In Oracle we sometimes come across poor performance in our databases. Though there may be a number of reasons for performance degradation, we can prevent some issues by properly designing and/or diagnosing the database. Row Migration (RM) and Row Chaining (RC) are two potential problems that can be prevented. By suitably diagnosing RM/RC, we can improve database performance. The main considerations are:
- What is RM/RC?
- How to identify RM/RC?
- How to avoid RM/RC?
To begin, let’s start with the basic concept of an Oracle block.
Oracle Block
The Operating System Block size is the minimum unit of operation (read /write) by the OS and is a property of the OS file system. While creating an Oracle database we have to choose the ‘Data Base Block Size’ as a multiple of the Operating System Block size. The minimum unit of operation (read /write) by the Oracle database would be this ‘Oracle Block’, and not the OS block. Once set, the ‘Data Base Block Size’ cannot be changed during the life of the database (except in the case of Oracle 9i). To decide on a suitable block size for the database, we take into consideration factors like the size of the database and the concurrent number of transactions expected. Proper block sizing and its use are very important from the tuning point of view.
The Database Block (dbblock) has the following structure:
- Header – The header contains general information about the data; i.e. block address and type of segments (table, index, etc.). The header also contains information about the table and the actual row (address) that holds the data.
- Free Space – Space allocated for future update/insert operations. Generally affected by the values of PCTFREE and PCTUSED parameters.
- Data – Actual row data.
While creating/altering any table/index, Oracle used two storage parameters for space control.
- PCTFREE: The percentage of space reserved for future update of existing data.
- PCTUSED: The percentage of minimum space used for insertion of new row data. This value determines when the block gets back into Free List table.
An example would make the terms more clear. Suppose we create a table tab1 as:
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
SQL> insert into tab1 values(1, "Hello");
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).
This is where Row Migration comes into the picture.
Row Migration
Oracle 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 Chaining
What 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/RC
Oracle has provided the following three methods to create/view the statistics of tables/indexes:
- ANALYZE command
- Dynamic views
- Report.txt method
Execute this command to get the statistics of tab1 table-
SQL> ANALYZE TABLE tab1 LIST CHAINED ROWS;
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/RC
For 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:
- Analyze the table to get the row ID
- Copy those rows to a temporary table
- Delete the rows from the original table
- Insert the rows from step 2 back to the original table
Avoiding row chaining is very difficult since it is generally caused by insert operations using large data types (i.e lob, etc.).
A good precaution is to either use a large block size (which can’t be changed without creating a new database) or use a large extent size.
To summarize what we have discussed:
- Row migration (RM) is typically caused by UPDATE operations
- Row chaining (RC) is typically caused by INSERT operations
- SQL statements which are creating/querying these RM/RC data will degrade the performance due to more I/O work
- To diagnose this, use the ANALYZE command, query V$SYSSTAT view, or generate a report.txt file
- To remove RM, use a higher PCTFREE value
Gaurav Sharan Gupta
OCP-8I DBA
MBT-India
