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.
| Feature | PCTFREE | PCTUSED |
|---|---|---|
| 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.