Skip to content

What is Difference Between PCT_FREE and PCT_USED

  • by

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 listsWhen PCT_FREE < PCTFREE, take it from the listWhen PCT_USED < PCTUSED, put it on the list
Applied DMLUPDATEINSERT (new rows)
Measured Before DMLFree %Used %
PlanMin Space Must be Reserved before UPDATEMax Space Can be Consumed for free lists
Fail to Meet PlanFind other data blocks in free list to UPDATEFind other data blocks in free list to INSERT
May CauseMigrated RowsChained Rows
May Do ActionReorganize SegmentReorganize Segment
When to increase1. 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 increased1. 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 decreaseMost 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 decreased1. 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.