August 2006

The Specialist
Database News You Can Use
A monthly newsletter for Oracle users

August 2006
From Database Specialists
Expert onsite and remote DBA services
for your Oracle databases
Tel: 415-344-0500 • Toll-Free: 1-888-648-0500

Ask DB Specialists: Performance Impact of Chained Rows

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.

Choosing a Database Block Size

When you create an Oracle database, the db_block_size parameter setting determines the block size that will be used. The block size can range from 2 Kb to 32 Kb, but it cannot be changed after the database has been created. When is it appropriate to use a large block size? When would a small block size make more sense? Check out MetaLink document 46757.1 for a discussion of the pros and cons of the different block sizes. This month’s Ask DB Specialists column touched on this topic as well.

Beginning in Oracle 9i it is possible to add tablespaces with different block sizes to the database. This can be helpful for transporting tablespaces between databases created with different block sizes, and also for realizing in one database the various benefits that the different block sizes can offer. Using multiple block sizes in a database is not difficult, but some preparation work is required. A brief posting on Oracle-base walks through a simple example. You can read it at http://www.oracle-base.com/articles/9i/MultipleBlockSizes.php.

Oracle RAC Resources

In the past few years, Oracle’s Real Application Clusters (RAC) has been gaining in popularity. We support RAC in production, and we advise our customers to go into it with open eyes, fully weighing the pros and cons against other alternatives. While one may read that RAC is a high availability solution by itself, we have always maintained that RAC alone does not eliminate all single points of failure for your database. On occasion, we have witnessed this fact in our customers’ production RAC environments.

So, if you are using RAC or are considering going in that direction, you may be interested to learn about the Oracle RAC Special Interest Group. Associated with the Independent Oracle Users Group, the Oracle RAC SIG states that its purpose “is to advance RAC awareness and adoption while enhancing the experience of current RAC users by providing a forum for technical interchange.” This can be a starting point for more research for you.

You’ll find documents, forums, and web seminars on the Oracle RAC SIG website. You have to log in to post in the forums and attend web seminars, but you can access white papers and browse the forums without registering. Visit the Oracle RAC SIG at http://www.oracleracsig.org.

Code Tips from Oracle Magazine

Oracle Magazine invites readers to share their code tips. The magazine compiles and publishes additional tips from time to time. Here are a few of the topics covered so far in 2006 at http://www.oracle.com/technology/oramag/code/tips2006/index.html.

  • Date Range (This code gives date ranges for the start-end of the week, regardless of the NLS setting for the system and can be very handy especially for slightly complex dbms jobs scheduling.)
  • Tracing the Sessions(s) of a Given User (This code traces the sessions of a given user by using a database on-logon trigger. This can be helpful when you have to trace a very brief user session.)
  • Oracle 10g Fibonacci Sequence (This code uses the SQL model clause to determine the first 12 elements of a Fibonacci Sequence.)
  • Temporary Tablespace Usage (This code monitors the actual space used in a temporary tablespace and shows the temporary tablespace high water mark.)
  • Open Cursor Count (This query reports the session-wise open cursor count, which can be helpful for optimizing the open_cursors parameter setting.)

Leave a Reply

Your email address will not be published. Required fields are marked *