Oracle 8i Row Chaining and Migration
September 25, 2002Gaurav Sharan Gupta
Create table tab1 (col1 number, col2 varchar2(100)) Storage ( PCTFREE 20 PCTUSED 50);This indicates that Oracle will create one table, tab1, having 2 columns, col1 and col2, using PCTFREE 20 and PCTUSED 50.
Now we want to insert the first record into the table
Oracle will first search for a free block in the "free list" (this is the table where oracle maintains a list of all free available blocks) and then the data is inserted into that block.
Note: The availability of the block in the "free list" is determined by the PCTFREE value. Initially, an empty block will be listed in the free list table, and it will continue to remain there until the free space reaches the PCTFREE value. When the free space reaches the PCTFREE value, the block is removed from the free list, and it is re-listed in the free list table when the volume of data in the block comes below the PCTUSED value. Oracle uses the free list to increase the performance. So for every insert operation, Oracle needs to search for the free blocks only from the 'free list' table instead of searching all blocks.
Let's consider the first dB block.
Now the first record we inserted occupies 10 units (let's consider the block size as 100 units, neglecting the header size for the sake of simplicity).
Ten units of the block are occupied by the first row we inserted. Since this is less than the available free space (80), the block would still be available for the next insertion.
We now insert seven more rows (10 units each), which will utilize 70 more units of the block.
After seven more rows insertion (total occupied space: 80 units), you would notice that it has occupied the available free space (80). So now this block will be removed from the free list. And if we want to insert a new row, Oracle will use the next block that is available in the free list table.
Suppose we want to update our first record, which will result in an increase in the row size by another 15 units. It will take the 15 unit space required from the 20 units of PCTFREE. If we want to update the second row requiring an additional 15 units of space, we would be unable to find the space in this block (we are left with just 5 more units in our block).
Row MigrationOracle will try to shift the entire row from the current block to another block having 25 (10+15) units of free space. However, it will not remove all the relevant entries for that row from the old block. It will store the new block row ID into the old block.
Now, if I want to view that record, Oracle will internally first check the old block and then from there it will get the new row ID and display the row data from the new block. With this extra amount of I/O operation required, you likely have guessed correctly that it would degrade the performance.
Now the first question that you might ask is what is the use of maintaining the old row ID if the entire row data has been migrated from the old block to the new one? This is because of Oracle's internal mechanism -- for the entire lifespan of a row data, its row ID will never change. That's why Oracle has to maintain two row IDs -- one is because of Oracle's internal mechanism and one is for the current location of the data.
Row ChainingWhat we have discussed to this point is the case where we have data in the block and new insertion is not possible into that block, which leads Oracle to go ahead and use a new block.
So what happens when a row is so large that it cannot fit into one free block? In this case, Oracle will span the data into a number of blocks so that it can hold all of the data. The existence of such data results in "Row Chaining".
Row Chaining is the storage of data in a chain of blocks. This primarily occurs in the lob, clob, blob or big varchar2 data types.
How to Find RM/RCOracle has provided the following three methods to create/view the statistics of tables/indexes:
It will populate the CHAINED_ROWS table. (The CHAINED_ROWS table should have been created by first executing the $ORACLE_HOME/rdbms/utlchain.sql script.)
Query this table for head_rowid column to get the rowid of the migrated/chained row.
By executing utbstat and utlestat (scripts provide by Oracle that can be found in $ORACLE_HOME/rdbms) for a period of time, we can create a report.txt file. Check the statistics of "table fetch continued row" in report.txt.
Check the value of "table fetch continued row" in V$SYSSTAT.
These steps can uncover the existence of row chaining/migration; now we need to find a cure for it.
How to Avoid/Eliminate RM/RCFor avoiding row migration, we can use a higher PCTFREE value since migration is typically caused by update operations. However, there is a tradeoff as the space allocated to PCTFREE is not used for normal insert operations and can end up wasted.
A temporary solution (since it will only take care of the existing migrated rows and not the future ones) is to delete the migrated row from the table and perform the insert again. To do this follow these steps:
To summarize what we have discussed: