You're invited to submit your Oracle-related questions to us at askdbspecialists08@dbspecialists.com. Please include your name and telephone number in your email.
This month’s question comes to us from Bill in New York: What are the performance impacts of having tables with chained or migrated rows? How can I fix them? How can I avoid them in the first place?
Hamid Minoui of the Database Specialists team responds: We say a row in a table is “chained” if the row is stored in two or more data blocks. This can happen because the row was too big to fit in one data block, or because an update of an existing row required additional space and there wasn’t sufficient space available in the data block. We say a row is “migrated” if, during the course of an update, Oracle moved the entire row to a different data block (with more space) instead of leaving part of the row in the original data block and chaining to a new data block. Index ROWIDs still point to the original location for the row, and a forward address there points to the new location.
Chained or migrated rows in database tables can potentially have a performance impact, depending on how frequently these rows are fetched during a typical workload. To fetch a migrated row, the data block referenced by the ROWID needs to be fetched first in order to obtain the new address of the row. Similarly for chained rows, the head of the chained row contains the address of the next part of the row, which might again contain an address for another part. In effect, multiple I/O operations are required when accessing a chained or migrated row instead of just one.
Migrated rows are special cases of chained rows and are treated the same way by Oracle when statistics are accumulated for them. The effect of reading more than one data block to fetch column data from one row is known as “table fetch continued row.” The value of this statistic in v$sysstat and v$sesstat is increased by one every time a row is fetched this way.
One way to measure the performance impact of row chaining is to determine the percentage of rows fetched either by ROWID or table scan that incurred a “table fetch continued row” since instance startup. The following query calculates this percentage as pct_chained_row:
SELECT C.value / (A.value + B.value) * 100.0 AS pct_chained_row
FROM v$sysstat A, v$sysstat B, v$sysstat C
WHERE a.name = 'table fetch by rowid'
AND b.name = 'table scan rows gotten'
AND c.name = 'table fetch continued row';
If pct_chained_row is consistently high, excessive row chaining may be degrading system performance and further research might be appropriate.
Note that migrated rows do not impact performance of full table scans; all data blocks up to the table’s high water mark are scanned and rows in blocks containing reference to migrated rows are ignored because all rows will eventually be read. Chained rows may still need to be fetched as a “continued row” even in a table scan in order to get the address of the rest of the row from the first part. However, in situations where the needed column values are stored in the first block of the chain, the fact that a row is chained will not have a performance impact on either a table scan or access by ROWID.
You can determine the number of chained or migrated rows in a table with the ANALYZE TABLE statement. This statement updates the chain_cnt column in user_tables appropriately. (Note that dbms_stats does not update chain_cnt.) To identify which rows are chained or migrated, use the ANALYZE TABLE statement with the LIST CHAINED ROWS clause. By default this populates a table called chained_rows (created by utlchain.sql) with information on the table’s chained rows. The column head_rowid shows the ROWID that starts the chain for each chained row.
It might not be practical to analyze all tables in the database to identify those with chained rows. Also, chained rows in tables that are rarely used may not degrade performance much. Therefore row chaining and migration do not always represent a problem. Consider the performance impact carefully before hunting down chained rows and trying to fix them.
There are multiple ways to fix chained rows in tables. The best approach in each case depends on factors such as data availability requirements, percentage of rows that are chained, and whether the table has a LONG or LONG RAW column. Some options are:
-
Move the table with ALTER TABLE MOVE
- Copy the chained rows to a temporary table and then delete and reinsert them
- Use the dbms_redefinition package to reorganize the table online
- Export the table, drop it, and re-import it
In most cases, the cause of chained rows is related to application design and there are times row chaining is unavoidable. Tables with many columns or long row length are prone to have chained rows, as are tables where rows are inserted with null values that are updated later. You can adjust the PCTFREE and PCTUSED storage parameters on individual tables according to their usage in order to avoid or minimize row chaining and migration. You can also partition tables and compact or compress the rows residing on partitions that are no longer being updated. For tables with long row lengths, you might want to consider relocating the tables to tablespaces with a larger block size.
By querying v$sysstat or v$sesstat it is easy to determine how frequently Oracle comes across migrated or chained rows. It is possible to identify precisely which rows in the database are chained or migrated, but this can be resource-intensive. Consider how much chaining and migration might be degrading system performance before trying to identify the chained rows. If you do decide it will be worth the effort to fix chained rows, be sure to address the root cause to the extent possible in order to prevent or slow the rate at which additional rows become chained or migrated in the future.
About The Specialist 