Skip to content
Home » Oracle » What is Difference Between PCT_FREE and PCT_USED

What is Difference Between PCT_FREE and PCT_USED

For manual segment space management (MSSM), we have to define the two parameters in a table in order to meet our target space plan. Let see their definitions by Oracle:

  • PCTFREE specifies the percentage of space to be reserved in a block for future updates.
  • PCTUSED sets the percentage of free space that must exist in a currently used block for the database to put it on the free list. For example, if you set PCTUSED to 40 in a CREATE TABLE statement, then you cannot insert rows into a block in the segment until less than 40% of the block space is used.

Here I summarized the differences between the two under MSSM.

FeaturePCTFREEPCTUSED
ASSM Used
MSSM Used
Data Block Level
Percentage Value
Free lists When PCT_FREE < PCTFREE, take it from the list When PCT_USED < PCTUSED, put it on the list
Applied DML UPDATE INSERT (new rows)
Measured Before DML Free % Used %
Plan Min Space Must be Reserved before UPDATE Max Space Can be Consumed for free lists
Fail to Meet Plan Find other data blocks in free list to UPDATE Find other data blocks in free list to INSERT
May Cause Migrated Rows Chained Rows
May Do Action Reorganize Segment Reorganize Segment
When to increase 1. Average row length (size) will be potentially growing larger and larger for each UPDATE. 1. Average row length (size) is pretty small
2. The shortage of disk space is the primary concern
3. Big tables
Consequences when increased 1. Less migrated rows
2. Space is less compact and Less fragments
3. Better performance
1. More chained rows
2. Space is more compact and more fragments
3. Worse performance
When to decrease Most DML are INSERT on this table. You can even set 0 if the table is INSERT only. 1. Average row length (size) is very large in nature
2. More free space is available
3. Small tables
Consequences when decreased 1. More migrated rows
2. Space is more compact and more fragments
3. Worse performance
1. Less chained rows
2. Space is less compact and Less fragments
3. Better performance

Note 1: PCT_FREE and PCT_USED are the current values of each table selected from dba_tables. PCTFREE and PCTUSED are the parameters for a segment.

Note 2: This post entirely talked about MSSM, not for ASSM. In ASSM, PCTUSED and Free lists are ignored, only PCTFREE is used.

Leave a Reply

Your email address will not be published. Required fields are marked *