Data Blocks  «Prev  Next»
Lesson 5Setting PCTFREE for optimal performance
ObjectiveSet PCTFREE to minimize row chaining and migration.

Setting PCTFREE for Optimal Performance

Oracle SQL Tuning using PCTFREE

As we discussed earlier, Oracle makes a direct tradeoff between efficient space usage and high performance. This is particularly true for the PCTFREE parameter, especially as it applies to chained rows.
Chained rows occur when, as a result of an UPDATE, the row cannot fit into any available data block.

The performance impact of migrated and chained rows

PCTFREE determines when to perform a freelist un-link, and so decides whether or not a row will migrate or chain.
If the data is migrated or chained, it is stored in different database blocks, and must be read from many database blocks to retrieve the row, resulting in many unnecessary I/Os. The SlideShow below looks at PCTFREE, row chaining, and row migration.

1) If PCTFREE is set low enough so that the rows cannot be stored on the same block (A), but on another block (B).
1) If PCTFREE is set low enough so that the rows cannot be stored on the same block (A), but on another block (B).

2) Oracle moves the entire row to the new block (B), and the rows are said to be migrated.
2) Oracle moves the entire row to the new block (B), and the rows are said to be migrated.

3) If PCTFREE is set too low, so that the row is too large to fit into any available block caption
3) If PCTFREE is set too low, so that the row is too large to fit into any available block caption

4) Oracle splits the row into multiple pieces and stores each in a separate block, and the rows are said to be chained.
4) Oracle splits the row into multiple pieces and stores each in a separate block, and the rows are said to be chained.

Migrating Chaining Rows

Using PCTFREE to minimize row chaining and migration

If PCTFREE is small, the data will fully occupy each database block. As a result, PCTFREE can have an important impact on performance if it is set too low. By planning for row expansion and reserving enough space on the data block, we ensure that minimal row chaining occurs. Because migrating and chaining are very harmful to performance levels, PCTFREE should be set so that they occur infrequently. Now that we understand the importance of the PCTFREE parameter as it applies to chained rows and migrated rows, let us look at setting PCTFREE.

Rules for setting PCTFREE

If you seldom use UPDATE and the following conditions apply, then set PCTFREE to zero.
  1. Your tables do not contain VARCHAR data type columns.
  2. You always store full-sized default values in table columns.
  3. You never update a table row.

If these criteria do not describe your tables, then you must set PCTFREE to allow for Oracle to reserve enough room for the rows to expand.
Oracle monitors the number of chained rows in a list of table statistics.

Detecting chained rows

You can check for chained rows with the ANALYZE command. The ANALYZE command can be used to either populate the chained_rows column of DBA_TABLES, or you can use the LIST CHAINED ROWS option to pipe the output into the CHAINED_ROW table as defined by the utlchain.sql utility. In summary, our goal as a DBA is to ensure that we keep enough space on the data block to ensure that minimal row chaining occurs. The next lesson looks at PCTUSED.