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