DBA Fundamentals

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:

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 (PCTFREE) 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 PCTFREE 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 oracle user.
SQL> CONNECT oracle/learning
 

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)
)
/
 

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.

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 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 ORACLE 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 ORACLE user and then connect to SQLPlus as the ORACLE user.
SQL> CONNECT system/manager AS SYSDBA
SQL> GRANT CREATE TABLESPACE TO oracle
/
SQL> GRANT DROP TABLESPACE TO oracle
/
SQL> CONNECT oracle/learning

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 ORACLE_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.

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 ORACLE user.
SQL> CONNECT system/manager AS SYSDBA
SQL> REVOKE CREATE TABLESPACE FROM oracle
/
SQL> REVOKE DROP TABLESPACE FROM oracle
/
 

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
/