Avoid Chaining in Oracle by Setting PCTFREE Correctly

When a row is created in a table, the data is written to a block and is given a ROWID. The ROWID identifies the data’s location on disk. If a row is updated, the changes are written to the same location on disk. The ROWID for the row does not change.

Row chaining can occur when there isn’t enough room in the data blocks to store a single row or the most recent changes made to a row. A chained row is one that exists in multiple blocks instead of a single block. Accessing multiple blocks for the same row can be costly in terms of performance.

To see if you have chaining problems, run the utlchain.sql script that Oracle provides to create the CHAINED_ROWS table. The utlchain.sql file is a file that comes with Oracle and is in the /rdbms/admin subdirectory of your ORACLE_HOME. You can also use Enterprise Manager or look for “Fetch By Continued Row” in STATSPACK or AWR Report to detect chained rows. You should check for chaining on a weekly basis and fix any problems immediately. To analyze the amount of chaining in a table (CUSTOMER in this example), run the following query:

 

 

Then, run the following query accessing the CHAINED_ROWS table to check the CUSTOMER table for chaining:

 

 

 

If no rows are returned, then you don’t have a chaining problem. If there is a chaining problem, then the query will return the HEAD_ROWID for all chained rows. You can also use SELECT “COUNT(*)” against the CHAINED_ROWS table to find the number of chained rows. In V$SYSSTAT, “table fetch continued row” is an indicator of chained rows as well.

To avoid row chaining, set PCTFREE (the amount of space reserved in a block for updates) correctly (don’t set this when using ASSM). This parameter is set when the table is created. The default value is set to 10 (10 percent free for updates), but this needs to be much higher in a table where there is a large frequency of update activity to rows in the table.

Incidentally, if you have a table where update activity will be very low to nonexistent, you can set the PCTFREE to a slightly lower value to assure more rows will fit into the block and, therefore, conserve space in your table.

TIP

Find chaining problems by accessing the CHAINED_ROWS table. Avoid chaining problems by correctly setting PCTFREE or choosing the correct size for your database.