iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

DBAs - Fundamentals

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 | Lesson 19 | Lesson 20 | Lesson 21 | Lesson 22 | Lesson 23 | Lesson 24 | Lesson 25 | Lesson 26 |

 

Lesson 18

“If there were in the world today any large number of people who desired their own happiness more than they desired the unhappiness of others, we could have paradise in a few years.” Bertrand Russell (1872 - 1970)

 

Read first then play the video:

   DBA-VIDEO -Detecting ROW Migration and Chaining

   

Detecting ROW Migration and Chaining

 

Introduction

As a DBA, you are responsible for detecting row migration and chaining. The more migration and chaining you have, cause more performance problems for application software. You should identify them and if there are many of them, organize the table. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

 

Creating a table

Inserting lots of records

Generating lots of Migration and Chaining

Analyzing a table

Using the USER_TABLES view

Displaying table statistics

The NUM_ROWS column

The BLOCKS column

The CHAIN_CNT column

Moving or relocating a table

Grant a system privilege to a user

Checking an index table

Using the USER_INDEXES view

Rebuilding or relocating an index table

Analyzing an index table

Using the INDEX_STATS view

Checking the DELETED ROWS RATIO value

The lf_rows column

The del_lf_rows column

Dropping a table

Dropping an index table

Dropping a tablespace

Revoking a system privilege from a user

Commands:

CREATE TABLE CONSTRAINT PRIMARY KEY

ANALYZE TABLE COMPUTE STATISTICS

CREATE TABLESPACE DATAFILE

GRANT CREATE TABLESPACE TO

GRANT DROP TABLESPACE TO

ALTER TABLE MOVE TABLESPACE

ALTER INDEX REBUILD TABLESPACE

ANALYZE VALIDATE STRUCTURE

DROP TABLE

DROP TABLESPACE

REVOKE CREATE TABLESPACE FROM

 

 

Hands-on

In this exercise you will learn how to analyze a table to detect row migration and fix the problem. Note that a “Chained Record” happens when a user updates a record and the new value can not fit in the existing allocated location. So, Oracle stores the value in a space that allocated for them (PCT) and add a pointer to the row so that it knows where the rest of the record is. This process calls row Chaining. Now, if there was no space in the PCT area, then Oracle will take the whole record and migrate it in a different block that has space. This process calls row Migration.

 

Now, connect to SQLPlus as the iself user.
SQL> CONNECT iself/schooling


Create a dummy table
First, let's create a table and name it TEST_MIGRATE.
SQL> CREATE TABLE test_migrate
              (col1 NUMBER,
              col2 VARCHAR2(1000),
              CONSTRAINT pk_test_migrate PRIMARY KEY (col1)
              )
/


Populate a table with a good data

Write a procedure to insert a few records. This procedure will insert 1000 records into the TEST_MIGRATE table.
SQL> BEGIN
SQL>
              FOR i IN 1..1000 LOOP
SQL>
                            INSERT INTO test_migrate VALUES (i,'A');
SQL>
                            COMMIT;
SQL>
              END LOOP;
SQL> END;
SQL> /


View a table statistics
Query the TEST_MIGRATE table information using the USER_TABLES view.
SQL> SELECT table_name, num_rows, blocks, chain_cnt
              FROM user_tables
              WHERE table_name = 'TEST_MIGRATE'
/
Check the num_rows, blocks, and chain_cnt columns. Notice that they are null.


Analyze a table
Analyze the TEST_MIGRATE table with the compute statistics option.
SQL> ANALYZE TABLE test_migrate COMPUTE STATISTICS
/


Now, query the USER_TABLES directory view again.
SQL> SELECT table_name, num_rows, blocks, chain_cnt
              FROM user_tables
              WHERE table_name = 'TEST_MIGRATE'
/
Check the NUM_ROWS, BLOCKS, and CHAIN_CNT columns again. The analyze statement computed statistical information for the TEST_MIGRATE table. Notice that we have no problems in the row migration or chaining since the CHAIN_CNT column is zero.

 

Populate a table with lots of space problem

Now, let's write a procedure to create 1000 of row migrations and chaining.
SQL> DECLARE
SQL>
              v_data CHAR(1000) := 'This is very very long text ...';
SQL> BEGIN
SQL>
              UPDATE test_migrate
SQL>
              SET col2 = v_data;
SQL>
              COMMIT;
SQL> END;
SQL> /
Can you tell why this procedure creates so many row chaining or migration? Notice that the V_DATA's datatype is CHAR and was sized to 1000 characters long. The migration will occur since the record cannot be fitted in the block.


Analyze the table

Analyze the TEST_MIGRATE table with the compute statistics option again.
SQL> ANALYZE TABLE test_migrate COMPUTE STATISTICS
/


View table statistics
Query the TEST_MIGRATE table information again.
SQL> SELECT table_name, num_rows, blocks, chain_cnt
              FROM user_tables
              WHERE table_name = 'TEST_MIGRATE'
/
Check the NUM_ROWS, BLOCKS, and CHAIN_CNT columns. Notice that you have row chaining and migration problems. The problem needs to be fixed.


Clear all Problems
Now, let's create a tablespace and move or relocate the table to an alternative destination.
SQL> CREATE TABLESPACE data2move DATAFILE SIZE 100K
/
Notice that the ISELF user does not have sufficient privileges to create or drop tablespaces.

Connect as the SYSTEM/MANAGER user and grant the create and drop privileges to the ISELF user and then connect to SQLPlus as the ISELF user.
SQL> CONNECT system/manager AS SYSDBA
SQL> GRANT CREATE TABLESPACE TO iself
/
SQL> GRANT DROP TABLESPACE TO iself
/
SQL> CONNECT iself/schooling

Now, lets create a tablespace again.
SQL> CREATE TABLESPACE data2move DATAFILE SIZE 100K
/
Here, the tablespace was created.

Check to see where the table is located.
SQL> SELECT table_name, tablespace_name
              FROM user_tables
              WHERE table_name = 'TEST_MIGRATE'
/

Relocate or move the table from ISELF_DATA to the DATA2MOVE tablespace.
SQL> ALTER TABLE test_migrate MOVE TABLESPACE data2move
/

Check to see where the table is moved.
SQL> SELECT table_name, tablespace_name
              FROM user_tables
              WHERE table_name = 'TEST_MIGRATE'
/
Note that the location was changed.

Check the TEST_MIGRATE index table using the USER_INDEXES view.
SQL> SELECT index_name, tablespace_name
              FROM user_indexes
              WHERE table_name = 'TEST_MIGRATE'
/

Create the INDEX2MOVE tablespace and relocate the index table to it.
SQL> CREATE TABLESPACE index2move DATAFILE SIZE 100K
/
The tablespace will be created.

Rebuild or relocate the index table.
SQL> ALTER INDEX pk_test_migrate

              REBUILD TABLESPACE index2move
/
Notice that the REBUILD option not only can recreate an index tables, but can also be used to move or relocate the index tables.

Analyze the TEST_MIGRATE table with the compute statistics option again.
SQL> ANALYZE TABLE test_migrate COMPUTE STATISTICS
/

Query the USER_TABLES view to display the TEST_MIGRATE table information again.
SQL> SELECT table_name, num_rows, blocks, chain_cnt
              FROM user_tables
              WHERE table_name = 'TEST_MIGRATE'
/
Check the NUM_ROWS, BLOCKS, and CHAIN_CNT columns. Notice that with the move clause not only you can relocate the table, but also remove all row migrations and chaining.

 

When should I rebuild

Delete most of the records. Let us remove almost all the records except the record number 1, 500, and 999, and keep only three records in the table.
SQL> DELETE FROM test_migrate
              WHERE col1 NOT IN (1,500,999)
/

Now, analyze the TEST_MIGRATE index table with the validate structure option.
SQL> ANALYZE INDEX pk_test_migrate VALIDATE STRUCTURE
/


Query the index table statistics, INDEX_STATS view, to display the name, number of leaf nodes, deleted leaf nodes, and deleted rows ratio to see if the empty space exceeds 30%.
SQL> SELECT name, lf_rows, del_lf_rows,

                           del_lf_rows/lf_rows "Over 30%"
              FROM index_stats
              WHERE name = 'PK_TEST_MIGRATE'
/
The INDEX_STATS dictionary view shows that the number of index entries in leaf nodes in the lf_rows column compared to the number of deleted entries in the del_lf_fows column. If the number of deleted entries is over 30 percent, you should rebuild the index.

Now, drop the TEST_MIGRATE table, along with the DATA2MOVE and INDEX2MOVe tablespaces.
SQL> DROP TABLE test_migrate
/
SQL> DROP TABLESPACE data2move
              INCLUDING CONTENTS
              CASCADE CONSTRAINTS
/
SQL> DROP TABLESPACE index2move
              INCLUDING CONTENTS
              CASCADE CONSTRAINTS
/

Also, connect to SQLPlus as the SYSTEM/MANAGER user and revoke the CREATE TABLESPACE and DROP TABLESPACE from the ISELF user.
SQL> CONNECT system/manager AS SYSDBA
SQL> REVOKE CREATE TABLESPACE FROM iself
/
SQL> REVOKE DROP TABLESPACE FROM iself
/

 

“The foolish man seeks happiness in the distance, the wise grows it under his feet.” James Oppenheim

 

Questions:

Q: What is a ROW Migration?

Q: What is a Chained record?

Q: What are the differences between a row migration and chained record?

Q: How do you detect a row migration and chaining?

Q: How do you analyze a table?

Q: Describe the following views.

USER_TABLES view

USER_INDEXES view

INDEX_STATS view

Q: What do the following columns contain in the USER_TABLES view?

NUM_ROWS column

BLOCKS column

CHAIN_CNT column

Q: How do you move a table?

Q: How do you grant a system privilege to a user?

Q: How do you reorganize an index table?

Q: What does the DELETED ROWS RATIO value show?

Q: What are the lf_rows and del_lf_rows columns in the INDEX_STAT table?

Q: How do you revoke a system privilege from a user?

Q: How do you create a table constraint?

Q: What do the following SQL statements do?

SQL> CREATE TABLE test_migrate
              (col1 NUMBER,
              col2 VARCHAR2(1000),
              CONSTRAINT pk_test_migrate PRIMARY KEY (col1)
              )
/


SQL> SELECT table_name, num_rows, blocks, chain_cnt
              FROM user_tables
              WHERE table_name = 'TEST_MIGRATE'
/

SQL> ANALYZE TABLE test_migrate COMPUTE STATISTICS
/

SQL> ALTER TABLE test_migrate MOVE TABLESPACE data2move
/

SQL> ALTER INDEX pk_test_migrate

              REBUILD TABLESPACE index2move
/

SQL> SELECT name, lf_rows, del_lf_rows,

                         del_lf_rows/lf_rows "Over 30%"
              FROM index_stats
              WHERE name = 'PK_TEST_MIGRATE'
/

SQL> DROP TABLESPACE index2move
              INCLUDING CONTENTS
              CASCADE CONSTRAINTS
/