| Lesson 6 | Setting PCTUSED for optimal performance |
| Objective | Set PCTUSED for high performance. |
Setting PCTUSED for Optimal Performance
PCTUSED controls when Oracle returns a data block to the freelist after rows have been deleted or updated out of it. Once a block has been filled to the PCTFREE threshold and removed from the freelist, Oracle will not consider that block for new INSERT operations again until the percentage of used space falls below the PCTUSED value. At that point Oracle performs a freelist re-link, making the block available to receive new rows.
PCTFREE and PCTUSED work as a pair governing the full freelist lifecycle of every data block in a segment:
- PCTFREE controls the freelist un-link: a block leaves the freelist when INSERT activity
fills it to the
(100 - PCTFREE)percent threshold. - PCTUSED controls the freelist re-link: a block rejoins the freelist when DELETE or UPDATE activity reduces used space below the PCTUSED threshold.
The default value for all Oracle tables is PCTUSED = 40, meaning a block must fall below 40 percent full before Oracle re-links it onto the freelist. For tuning purposes, the choice of PCTUSED value determines whether the database favors faster INSERT throughput or more efficient reuse of allocated block space.
Note: PCTUSED applies only in manual segment space management environments. In ASSM
tablespaces (SEGMENT SPACE MANAGEMENT AUTO), Oracle tracks free space via bitmap and PCTUSED has
no effect. PCTFREE remains active in both environments; PCTUSED does not.
The PCTUSED Performance Tradeoff
The central tradeoff is between efficient use of allocated storage and the I/O cost of INSERT operations. Setting PCTUSED to a high value keeps blocks fuller on average but forces INSERT sessions to cycle through freelist operations more frequently. Setting PCTUSED to a low value reduces freelist overhead per INSERT session at the cost of leaving partially-filled blocks off the freelist for longer.
PCTUSED Block Reuse Cycle
Setting PCTUSED High: Efficient Space Reuse
A high PCTUSED value — for example, 80 — means a block becomes eligible for re-use as soon as used space drops below 80 percent. Blocks return to the freelist quickly after DELETEs, keeping storage utilization high. However, the block may only have room for a few rows before it again reaches the PCTFREE threshold and must be un-linked. INSERT tasks acquire blocks from the freelist frequently, each time receiving only a small number of row slots before the block cycles off the freelist again.
In the most extreme case, a re-linked block with PCTUSED set very high may only have room for a single row before triggering another freelist un-link. Each INSERT generates a freelist operation, which is counterproductive in high-concurrency INSERT workloads.
Setting PCTUSED Low: Higher INSERT Performance
When INSERT throughput is the priority, set PCTUSED to a low value. Oracle waits until a block is nearly empty before re-linking it onto the freelist. Blocks that rejoin the freelist in this mode can accept many rows before reaching the PCTFREE threshold again, reducing the number of freelist operations per INSERT session. The cost is that partially-filled blocks below the PCTUSED threshold sit off the freelist, consuming allocated space that cannot receive new rows until the threshold is crossed.
To read more about tuning PCTUSED for INSERT performance and view supplemental examples, see the link below.
Setting PCTUSED PerformanceBuffer Busy Waits and PCTUSED
Buffer busy waits occur when multiple sessions attempt to access the same buffer in the buffer cache concurrently. In the context of PCTUSED and freelist management, buffer busy waits on segment header blocks indicate contention on the freelist chain itself — multiple INSERT sessions competing to acquire a free block pointer from the same master freelist.
Query V$WAITSTAT for wait statistics broken down by buffer class. Common buffer classes that
show buffer busy waits include data block, segment header[1],
undo header, and undo block. The segment header class is the most relevant for PCTUSED and freelist
contention.
Check the following V$SESSION_WAIT parameter columns to identify the contended resource:
- P1: File ID
- P2: Block ID
- P3: Class ID
Diagnosing Buffer Busy Waits
To identify which session is waiting, query V$SESSION for the value of
ROW_WAIT_OBJ# when the event is a buffer busy wait:
SELECT row_wait_obj#
FROM V$SESSION
WHERE EVENT = 'buffer busy waits';To identify the object and object type from the returned value, query DBA_OBJECTS:
SELECT owner, object_name, subobject_name, object_type
FROM DBA_OBJECTS
WHERE data_object_id = &row_wait_obj;Actions to Reduce Buffer Busy Waits
The preferred resolution is to migrate from manual space management to Automatic Segment Space Management
(ASSM). ASSM replaces the freelist chain with a multi-level bitmap, eliminating freelist contention entirely
and removing the need to specify PCTUSED, FREELISTS, and FREELIST GROUPS. Create locally managed tablespaces
with SEGMENT SPACE MANAGEMENT AUTO to enable ASSM.
If ASSM is not available — for example, because the tablespace uses dictionary space management — use manual
freelist tuning. A freelist is a linked list of data blocks in which free space has not yet reached PCTFREE or
used space has fallen below PCTUSED. The number of process freelists is set by the FREELISTS
parameter at CREATE TABLE or CREATE INDEX time. The default is one freelist; the maximum depends on the data
block size.
To find the current freelist count for a specific segment:
SELECT segment_name, freelists
FROM dba_segments
WHERE segment_name = 'YOUR_TABLE_NAME'
AND segment_type = 'TABLE';If increasing the number of freelists does not resolve contention, add freelist groups using the
FREELIST GROUPS parameter. Even on a single instance, freelist groups partition the freelist chain
and can reduce segment header contention. On Oracle RAC, assign each instance its own freelist group to ensure
INSERT sessions on different nodes do not compete for the same freelist entries.
Guidelines for Setting PCTUSED
PCTUSED determines the amount of row space available in a newly re-linked data block. A re-link only occurs when a DELETE or UPDATE statement has reduced used space in the block below the PCTUSED threshold. The setting therefore directly controls how much free space is available to INSERT operations at the moment a block rejoins the freelist.
The default PCTUSED = 40 means a block must fall below 40 percent full before Oracle re-links it. This is a balanced default suitable for most workloads. Raise PCTUSED only when disk space is constrained and efficient block reuse is required at the cost of INSERT performance.
| If your table | And you favor | Then set PCTUSED | In this example Oracle will |
|---|---|---|---|
| has more DELETE activity | High performance (less efficient storage)
• Only empty blocks are added to the freelist • Blocks receive many rows before they must be re-linked |
At a low value
Set PCTUSED = 20 to keep blocks less than 20% full before re-linking |
• Prevent immediate reuse of blocks after DELETE
• Occasionally perform a freelist re-link, keeping blocks less full • Allow blocks to receive new rows only when less than 20% full |
| has less DELETE activity | Efficient use of storage (lower performance)
• Full blocks are added to the freelist • Blocks can only receive a few rows before they must be re-linked |
At a high value
Set PCTUSED = 80 to keep blocks at least 80% full |
• Allow immediate reuse of blocks after DELETE
• Constantly perform freelist re-link, keeping blocks fuller • Allow blocks to receive new rows only if less than 80% full |
PCTUSED and Average Row Length
Average row length must be considered when setting both PCTFREE and PCTUSED. PCTFREE should leave enough room on each block for row expansion on UPDATE. PCTUSED should ensure that a newly re-linked block has enough room to accept a meaningful number of rows before cycling off the freelist again.
With PCTUSED = 60, blocks with less than 60 percent data are on the freelist. Once a block's used space drops below 60 percent, it returns to the freelist. INSERT tasks receive relatively limited row capacity before the block fills again, increasing I/O frequency per INSERT session compared to a lower PCTUSED setting.
A practical rule: the usable space between PCTUSED and PCTFREE should accommodate at least five to ten average-length rows. For example, with an 8KB block, average row length of 400 bytes, PCTFREE = 20 and PCTUSED = 40, the available window is approximately 40 percent of 8,000 bytes — 3,200 bytes — which holds about eight rows per freelist cycle. That is a reasonable balance for most OLTP workloads.
In Summary
A data block is filled to the PCTFREE limit on INSERT, then held off the freelist until used space drops below PCTUSED on DELETE. The gap between PCTFREE and PCTUSED determines how much space must be reclaimed before a block re-enters the freelist. A wide gap means fewer freelist operations per INSERT session and better INSERT throughput. A narrow gap means more frequent reuse of block space and higher storage efficiency at the cost of more freelist I/O.
In ASSM tablespaces, bitmap-based free space tracking replaces the entire freelist mechanism and PCTUSED has no effect. PCTFREE remains the only block-level space parameter relevant in ASSM environments.
Before moving on, test your understanding of setting table storage parameters using the exercise link below.
Setting PCTUSED — ExerciseSetting PCtused - Exercise
Setting PCtused - Exercise