Setting PCTUSED for Optimal Performance in Oracle

The settings for PCTUSED can have a dramatic impact on the performance of an Oracle database. Many new Oracle DBAs fail to realize that PCTUSED is only used to re-link a full data onto the table freelist. A re-link only occurs 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 re-linked data block.
The default settings for all Oracle tables are PCTUSED=40. The PCTUSED=40 setting means that a block must become less than 40 percent full before being re-linked on the table free list.

How PCTUSED affects the operation of re-links onto the table freelist. As I said, a data block becomes available for re-use when the free space of a block drops below the value of PCTUSED for the table. This happens when the amount of space in a database block falls below PCT_USED, and a free list re-link operation is triggered. For example, with PCTUSED=60, all database blocks that have less than 60 percent data will be on the free list, as well as other blocks that dropped below PCT_USED and have not yet grown to PCT_FREE. Once a block deletes a row and becomes less than 60 percent full, the block goes back on the free list.
There is a direct tradeoff 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 re-used 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 only be set above 40 when the database is short on disk space, and it must make efficient re-use of data block space.
It should now be very clear that you need to consider the average row length 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 tradeoff 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 re-linked free block may only have enough space for a single row before causing another I/O.
Remember the rule: 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 re-used data block before it unlinks from the freelist.