• PCTFREE—This storage parameter determines when a block can be unlinked from the free list. You must reserve enough room on each data block for existing rows to expand without chaining onto other blocks. The purpose of PCTFREE is to tell Oracle when to remove a block from the object's free list. Since the Oracle default is PCTFREE=10, blocks remain on the free list while they are less than 90 percent full. Once an insert makes the block grow beyond 90 percent full, it is removed from the free list, leaving 10 percent of the block for row expansion. Furthermore, the data block will remain off the free list even after the space drops below 90 percent. Only after subsequent deletes cause the space to fall below the PCTUSED threshold of 40 percent will Oracle put the block back onto the free list.
• PCTUSED—This storage parameter determines when a block can relink onto the table free list after DELETE operations. Setting a low value for PCTUSED will result in high performance. A higher value of PCTFREE will result in efficient space reuse but will slow performance. As rows are deleted from a table, the database blocks become eligible to accept new rows. This happens when the amount of space in a database block falls below PCTUSED, and a free list relink operation is triggered. For example, with PCTUSED=60, all database blocks that have less than 60 percent will be on the free list, as well as other blocks that dropped below PCTUSED and have not yet grown to PCTFREE. Once a block deletes a row and becomes less than 60 percent full, the block goes back on the free list. As rows are deleted, data blocks become available when a block's free space drops below the value of PCTUSED for the table, and Oracle relinks the data block onto the free list chain. As the table has rows inserted into it, it will grow until the space on the block exceeds the threshold PCTFREE, at which time the block is unlinked from the free list.
• FREELISTS—Oracle allows table and indexes to be defined with multiple free lists. All tables and index free lists should be set to the high-water mark of concurrent INSERT or UPDATE activity. Too low a value for free lists will cause poor Oracle performance.

• Slow INSERTs—INSERT operations run slowly and have excessive I/O. This happens when blocks on the free list have room for only a few rows before Oracle is forced to grab another free block.
• Slow SELECTs—SELECT statements have excessive I/O because of migrated/chained rows. This occurs when rows "chain" and fragment onto several data blocks, causing additional I/O to fetch the blocks.
• Slow UPDATEs—UPDATE statements run very slowly with double the amount of I/O. This happens when updates expand a VARCHAR or BLOB column and Oracle is forced to chain the row contents onto additional data blocks.
• Slow DELETEs—Large DELETE statements run slowly and cause segment header contention. This happens when rows are deleted and the database must relink the data block onto the free list for the table.

Setting PCTFREE and PCTUSED

As any experienced DBA understands, the settings for PCTUSED can have a dramatic impact on the performance of an Oracle database. But many new Oracle DBAs fail to realize that PCTUSED is only used to relink full data onto the table free list. A relink occurs only when a DELETE or UPDATE statement has reduced the free space in the data block. The setting for PCTUSED will determine the amount of row space in this newly relinked data block.

The default setting for all Oracle tables is PCTUSED=40. This setting means that a block must become less than 40 percent full before being relinked on the table free list.

Let's take a closer look at how the PCTUSED operator works and how it affects the operation of relinks onto the table free list. As I said, a data block becomes available for reuse when its free space drops below the value of PCTUSED for the table, which triggers a free list relink operation.

There is a trade-off between the setting for PCTUSED and database performance on INSERT operations. In general, the higher the setting for PCTUSED, the less free space will be on reused data blocks at INSERT time. Hence, INSERT tasks will need to do more frequent I/Os than they would if they were inserting into empty blocks. In short, the value for PCTUSED should be set above 40 only when the database is short on disk space and it must make efficient reuse of data block space.

It should now be very clear that the average row length needs to be considered when customizing the values for PCTFREE and PCTUSED. You want to set PCTFREE such that room is left on each block for row expansion, and you want to set PCTUSED so that newly linked blocks have enough room to accept rows.

Herein lies the trade-off between effective space usage and performance. If you set PCTUSED to a high value, say 80, then a block will quickly become available to accept new rows, but it will not have room for a lot of rows before it becomes logically full again. In the most extreme case, a relinked free block may have only enough space for single rows before causing another I/O operation.

Remember this: The lower the value for PCTUSED, the less I/O your system will have at INSERT time and the faster your system will run. The downside, of course, is that a block will be nearly empty before it becomes eligible to accept new rows.

Because row length is a major factor in intelligently setting PCTUSED, a script can be written that allows the DBA to specifically control how many rows will fit onto a reused data block before it unlinks from the free list. We've created such a script, pctused.sql, which appears in this sidebar. It allows you to adjust the setting for PCTFREE and PCTUSED as a function of the number of rows you want to store between I/Os, and it generates the table alteration syntax. It also allows you to define the blocksize and the number of rows you want to leave room for after the block relinks onto the free list.

Note that this script provides only general guidelines; you will want to leave the default PCTUSED=40 unless your system is low on disk space or unless the average row length is very large.

The output file from pctused.lst will contain the ALTER TABLE syntax to reset PCTUSED to an appropriate value.

Now let's take a close look at free lists and see how a free list shortage can cause performance slowdowns.

Free list management for Oracle objects
In systems where multiple tasks are concurrently inserting or deleting records from an Oracle database, it is not uncommon to see "buffer busy waits" within the database engine. A buffer busy wait is a condition where Oracle is waiting to access the segment header block for the table. As you may know, Oracle reserves the first block in a table (the segment header) to keep control information, including the header link for a list of free blocks for the table. When multiple tasks are attempting to simultaneously insert information into an Oracle table, tasks will have to wait their turn to get access to the segment header.

In sum, any time buffer busy waits occur, the Oracle database administrator must try to find those tables or indexes that are experiencing the segment header contention and increase the freelists or freelist_groups parameters. The freelist_groups parameter allows an Oracle table to have several segment headers, so that multiple tasks can insert into the table. The setting for the FREELISTS parameter should be set equal to the high-water mark of the number of concurrent inserts for the target table.

• Always set PCTUSED to allow enough room to accept a new row. We never want to have free blocks that do not have enough room to accept a row. If we do, this will cause a slowdown since Oracle will attempt to read five "dead" free blocks before extending the table to get an empty block.
• The presence of migrated/chained rows in a table means that PCTFREE is too low or that DB_BLOCK_SIZE is too small. In most cases within Oracle, RAW and LONG RAW columns make huge rows that exceed the maximum blocksize for Oracle, making migrated/chained rows unavoidable.
• If a table has simultaneous INSERT SQL processes, it needs to have simultaneous DELETE processes. Running a single purge job will place all of the free blocks on only one free list, and none of the other free lists will contain any free blocks from the purge.
• The FREELISTS parameter should be set to the high-water mark of updates to a table. For example, if the customer table has up to 20 end users performing INSERTs at any time, then the customer table should have FREELISTS=20.
• FREELIST GROUPS should be set to the number of Oracle Parallel Server instances that access the table.  For partitioned objects and cases of segment header contention, freelist_groups my be set for non-RAC systems.