How PCTFREE and PCTUSED Work Together
You use two space management parameters, PCTFREE and PCTUSED, to control the use of free space within all the data blocks of a segment. You specify these parameters when creating or altering a table or cluster (which has its own data segment).
If you are using Automatic Segment Space Management the value of PCTUSED has no meaning because there is no free list. Instead the space management is handled by way of a bitmap. However, PCTFREE does still have meaning and should be set appropriately.
The PCTFREE parameter sets the minimum percentage of a data block to be reserved as free space for possible updates to rows that already exist in that block.
The PCTUSED parameter sets the minimum percentage of a block that can be used for row data plus overhead before new rows are added to the block.
As an example, if you issue a CREATE TABLE statement with PCTFREE set to 20, the Oracle server reserves 20% of each data block in the data segment of this table for updates to the existing rows in each block. The used space in the block can grow (1) until the row data and overhead total 80% of the total block size. Then the block is removed from the free list to prevent additional inserts (2).
DML Statements and PCTFREE
After you issue a DELETE or UPDATE statement, the Oracle server processes the statement and checks to see whether the space being used in the block is now less than PCTUSED. If it is, the block goes to the beginning of the freelist. When the transaction is committed, free space in the block becomes available for other transactions (3).
After a data block is filled to the PCTFREE limit again (4), the Oracle server again considers the block unavailable for the insertion of new rows until the percentage of that block falls below the PCTUSED parameter.
DML Statements, PCTFREE, and PCTUSED
Two types of statements can increase the free space of one or more data blocks:
DELETE statements
UPDATE statements which update existing values to values that use less space
Released space in a block may not be contiguous; for example, when a row in the middle of the block is deleted. The Oracle server coalesces the free space of a data block only when:
An INSERT or UPDATE statement attempts to use a block that contains enough free space to contain a new row piece
The free space is fragmented so that the row piece cannot be inserted in a contiguous section of the block
The Oracle server performs this compression only when required, because otherwise the performance of a database system would decrease due to the continuous compression of the free space in data blocks.
Setting PCTFREE for an Index
You can also specify the PCTFREE storage parameter when creating or altering an index. Setting PCTFREE for an index specifies how much of a block to fill when the index is created or during a block split. It does not keep space available for updates as is done with data blocks.
|
