Lesson 6	Setting PCTUSED for optimal performance
Objective	Set PCTUSED for high performance.

Setting PCTUSED for optimal performance

Tune using PCTUSED for Efficient space usage and High Performance

PCTUSED also presents a tradeoff between efficient space usage and high performance. After a data block becomes full, as determined by PCTFREE, Oracle does not consider the block for the insertion of new rows until the percentage of the block being used falls below the parameter PCTUSED. PCTUSED tells Oracle when it is acceptable to insert rows and perform a freelist re-link operation. If PCTUSED is set to a higher value, Oracle will constantly move database blocks onto the freelist as rows are deleted. PCTFREE and PCTUSED work together to optimize the use of space in the data blocks of the extents within a data segment. For tuning purposes, the settings of each of these storage parameters depend upon whether we value faster performance or better use of free space.

Tuning for Efficient Space reuse

If we desire efficient space re-use, we can set PCTUSED to a high number, making a data block available to receive a rows as soon as there is space for the row. Unfortunately, the new block may only have room for a few rows before the data block again becomes full and Oracle has to un-link the block from the freelist and fetch another free block. The slide show below describes how PCTUSED is used to tune for efficient space re-use.

Setting PCTUSED optimal performance in Oracle

1) Rows are deleted and the block goes onto the freelist

2) An insert task grabs the block and is only able to add 2 rows before the block again becomes full

3) Oracle must get another free block from the freelist. The result is a more efficient use of space.

Setting PCUsed Optimal Performance

Tuning for performance

If we value performance over space re-use, we should set PCTUSED to a low value. Using this method, we wait until the Oracle data block is nearly empty before re-linking it onto the freelist. Now, only empty blocks go onto the freelists and they will be able to receive numerous rows before they must be re-linked.

Visit the following link to read about how PCTUSED is used to tune for efficient performance and view a series of images describing how to set pctused performance.
Setting pctused Performance

In summary

We have learned that the data block is filled to the PCTFREE limit, and also that, until the percentage of that block falls below PCTUSED, the block is unavailable for insert of new rows. In setting the PCTUSED parameter, you must choose between efficient use of storage and faster database performance. The next lesson looks at monitoring and tuning Oracle indexes.

Setting PCtused - Exercise

Before moving on to the next lesson, click the Exercise link below to test your understanding of setting table storage parameters.
Setting PCtused - Exercise