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
occur when, as a result of an UPDATE, the row cannot fit into any available data block.
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.
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.
If you seldom use UPDATE and the following conditions apply, then set PCTFREE to zero.
Your tables do not contain VARCHAR data type columns.
You always store full-sized default values in table columns.
You never update a table row.
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.