Guidelines for setting PCTUSED

1. If your table	2. And your favor	3. Then set PCTUSED	4. In this example
has more DELETE activity	High performance (and less efficient storage)1) only empty blocks are added to the freelists 2) blocks are able to receive many rows before they must be re-linked	At a low value; set PCTUsed to 60, to keep the block at least 60% full	Oracle will 1) Prevent immediate re-use of blocks as rows are deleted. 2) Occasionally perform a freelist re-link operation to move blocks onto the FREELIST, keeping the database blocks less full 3) Allow blocks to receive new rows if they are less than 60% full
has less DELETE activity	Efficient use of storage [and lower performance] 1) full blocks are added to the freelists and they can only receive few rows before they must be re-linked	At a high value: set to 80 to keep the block at least 80% full	Oracle will 1) Allow immediate re-use of blocks as rows are deleted, 2) Constantly perform a freelist re-link operation to move blocks onto the FREELIST, keeping the database blocks more full, 3) Allow blocks to receive new rows only if they are less than 80% full

The settings for PCTUSED can have an impact on the performance of an Oracle database. Many Oracle DBAs do not 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. Let us examine at how the PCTUSED operator works and how it affects the operation of re-links onto the table freelist.
A data block becomes available for re-use when a block's free space 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 Input/Output operations than they would if they were inserting into empty blocks. 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.

One needs 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.

Ad Advanced Oracle SQL Tuning