PCT USERD - The Percentage of Minimum space user for insertion of New Row data. The value determines when the block gets into the FREELISTS structure

PCTFREE - The Percentage of Space reserved for future updation of existing data


C.Ramesh

Consider 8K block size. The total bytes 8 x 1024 8196 bytes
Each block requires approximately 117 bytes for the header. Please note that the header size varies depending upon the block size.
The total available bytes for data ( 8196 – 117) 8079 bytes.

A table is created with PCTFREE 20 PCTUSED 50 .

PCTRFREE in bytes 1615
PCTUSED in bytes 4039

Now the data available for insert and update (8079 – (20 * 8079)/100 ) 6463 Bytes.

Now user can insert new rows into this block as long as the old rows’ total bytes + new row’s total byte is less than or equal to 6463 bytes. If the new row’s total byte cannot be put into this block then Oracle will get the next block from the free list and inserts into it.

When a row is updated and the row’s data is expanded then PCTFREE come into play. The updated row’s data is placed into PCTFREE’s area provided the updated row’s new data can be fit into PCTFREE area. If it is not fit into that area another new block will be obtained from the Freelist and the row will be migrated. But the original row info (pointer) is kept in the old block. For subsequent access to this row involves 2 read I/O. That is why row migration should be avoided because of excessive I/Os.

ROW DELETION:

The PCTUSED parameter value (in this example 50 ) is the threshold limit for the old block to be added in the FREELIST. To understand better let us assume that a block is of full data. Now the user starts deleting rows from the block. When a row is deleted Oracle does not put the block into the FREELIST because it requires many recursive calls to update the FREELIST. The PCTUSED (50) determines when the block should be added into FREELIST. When the total bytes in the block is less than or equal to 4039 bytes then the block will be added into FREELIST.

If a table has high inserts and high deletion then you should decrease the PCTUSED value in order to minimize the frequent update of FREELIST.

I think you got clear about pctused and pctfree