Oracle storage parameters in tables along with techniques for tuning and optimizing heavy loads
Paying attention to storage level settings in tables and indexes is one of the important requirements in reducing the workload on tables in Oracle, and understanding these parameters and their precise adjustment and combining it with other techniques can help us greatly in tuning the database. In this post, I have tried to explain to you the key concepts and necessary techniques and conditions that are required to adjust these criteria.
Introduction:
PCTFREE, PCTUSED, and High Water Mark in Oracle
In Oracle Database, efficient space management inside data blocks is controlled by storage parameters such as PCTFREE and PCTUSED, which directly affect how rows are inserted, updated, and reused.
These parameters, in turn, influence the High Water Mark (HWM) of a table or segment, which represents the boundary between used and unused space.
1. PCTFREE (Percent Free)
Defines the minimum percentage of a block reserved as free space for future row expansions (updates that increase row size).
Ensures that updates do not cause excessive row migration or chaining.
Example: If PCTFREE = 20, then 20% of each block is reserved for row growth, and inserts stop once only 20% space remains free.
Restriction on the PCTFREE Clause
When altering an index, you can specify this parameter only in the modify_index_default_attrs clause and the split_index_partition clause.
PCTFREE has the same function in the statements that create and alter tables, partitions, clusters, indexes, materialized views, materialized view logs, and zone maps. The combination of PCTFREE and PCTUSED determines whether new rows will be inserted into existing data blocks or into new blocks.
2. PCTUSED (Percent Used)
Defines the threshold below which a block becomes available again for new inserts.
Once a block is filled up to the PCTFREE limit, it will not accept new rows until existing rows are deleted or shrink enough that the used space drops below PCTUSED.
Example: If PCTUSED = 40, a block will re-enter the freelist once less than 40% of its space is used.
Restrictions on the PCTUSED Clause
The PCTUSED parameter is subject to the following restrictions:
- You cannot specify this parameter for an index or for the index segment of an index-organized table.
- This parameter is not useful and is ignored for objects with automatic segment-space management.
How PCTFREE and PCTUSED Work Together
In a newly allocated data block, the space available for inserts is the block size minus the sum of the block overhead and free space (PCTFREE). Updates to existing data can use any available space in the block. Therefore, updates can reduce the available space of a block to less than PCTFREE.
After a data block is filled to the limit determined by PCTFREE, Oracle Database considers the block unavailable for the insertion of new rows until the percentage of that block falls beneath the parameter PCTUSED. Until this value is achieved, Oracle Database uses the free space of the data block only for updates to rows already contained in the data block. A block becomes a candidate for row insertion when its used space falls below PCTUSED.
3.INITRANS
Specify the initial number of concurrent transaction entries allocated within each data block allocated to the database object. This value can range from 1 to 255 and defaults to 1, with the following exceptions:
- The default INITRANS value for a cluster is 2 or the default INITRANS value of the tablespace in which the cluster resides, whichever is greater.
- The default value for an index is 2.
In general, you should not change the INITRANSvalue from its default.
Each transaction that updates a block requires a transaction entry in the block. This parameter ensures that a minimum number of concurrent transactions can update the block and helps avoid the overhead of dynamically allocating a transaction entry.
The INITRANS parameter serves the same purpose in the statements that create and alter tables, partitions, clusters, indexes, materialized views, and materialized view logs.
4. Relation to the High Water Mark (HWM)
The HWM marks the boundary between blocks that have been formatted for use and those never touched by data.
Inserts can push the HWM upward as new blocks are used.
PCTFREE and PCTUSED control space reuse below the HWM, while the HWM itself keeps growing upward when existing blocks are full.
Deletes do not lower the HWM, meaning once it is raised, it stays there until segment shrink, table move, or reorganization is performed.
Feature | PCTFREE | PCTUSED | Relation to HWM |
Purpose | Reserves space for row expansion | Controls when blocks can accept new rows | Defines boundary between used and never-used blocks |
Trigger | Affects inserts stopping earlier | Affects when deleted blocks rejoin freelist | Raised only by inserts (not lowered by deletes) |
Focus | Row growth management | Space reuse management | Segment space boundary |
Impact | Prevents row migration | Improves block reuse efficiency | Controls full table scan cost & storage usage |
Therefore,
PCTFREE = reservation for row growth.
PCTFREE protects update space.
PCTUSED = threshold for reusing blocks.
PCTUSED enables block reuse.
HWM = the highest point ever used in a segment, independent of free space inside blocks.
Pctfree + Pctused must be equal 100 ??!!
This is one of the common misunderstandings about PCTFREE and PCTUSED in Oracle.
They don’t balance to 100, but must not overlap beyond 100.
1. Formula for PCTFREE
PCTFREE determines the percentage of a block reserved for future updates (row expansion).
Usable space for inserts in a block =
Block size - Overhead - (Block size * (PCTFREE/100))
So, if PCTFREE = 20 in an 8 KB block (assuming ~100 bytes overhead):
8192 - 100 - (8192 * 0.20) =~6429 , bytes available for inserts
2. Formula for PCTUSED
PCTUSED defines the lower limit of used space before a block is considered reusable.
Reusable threshold =
Block size * (PCTUSED /100)
If PCTUSED = 40 in an 8 KB block:
8192 * 0.40 = 3276.8 bytes
Once used space in a block falls below ~3277 bytes, it is added back to the freelist for inserts.
Must be: PCTFREE + PCTUSED = 100 ??!!
No. They are independent values, and their sum does not have to equal 100.
In fact, Oracle requires that:
PCTFREE + PCTUSED <= 100
Otherwise, there would be no usable range for inserts/deletes.
Example:
PCTFREE = 20, PCTUSED = 40 → Valid (20 + 40 = 60 ≤ 100)
PCTFREE = 60, PCTUSED = 50 → Invalid (110 > 100)
Example Setup:
Block size = 8 KB (8192 bytes)
Block overhead = ~192 bytes (metadata inside block)
Usable space = 8192 - 192 = 8000 bytes
Storage parameters:
PCTFREE = 20 → Reserve 20% for updates = 8000 × 0.20 = 1600 bytes
PCTUSED = 40 → Threshold = 8000 × 0.40 = 3200 bytes
Step 1: Initial Inserts
Oracle allows inserts until used space reaches 8000 - 1600 = 6400 bytes.
After this point, the block is considered “full” (even though 1600 bytes remain free).
So if each row = 800 bytes:
Maximum inserts = 6400 ÷ 800 = 8 rows.
At this point, free space = 1600 bytes reserved (PCTFREE).
The block is removed from freelist → no more new rows inserted here.
Step 2: Updates (Row Expansion)
Suppose one row expands from 800 → 1200 bytes:
Space used = 6400 + 400 = 6800 bytes.
Still fine, because reserved PCTFREE (1600) covered row growth.
This prevents row migration.
Step 3: Deletes
Now, delete 6 rows (4800 bytes freed).
New used space = 6800 - 4800 = 2000 bytes.
Threshold for reuse = PCTUSED = 3200 bytes.
Since 2000 < 3200, block goes back into freelist → eligible for new inserts.
Step 4: Reuse of Block
When inserts happen again, they can reuse this block until used space grows back to 6400 bytes(PCTFREE limit).
Visual Timeline
- Fresh block → available for inserts.
- Insert rows until 6400 bytes used → block “full,” no new rows allowed.
- Updates grow rows → free reserved space (1600 bytes) is used.
- Deletes reduce space below 3200 (PCTUSED) → block re-enters freelist.
- New inserts reuse block until limit reached again.
This cycle shows how:
PCTFREE controls when inserts stop (reserve for updates).
PCTUSED controls when inserts can start again (after deletes).
They work together to avoid wasted space and row migration.
What is mean pctfree = 0 oe equal 100
When we set these values?
1. What PCTFREE Does
PCTFREE = % of space in each block reserved for future row expansion (updates that increase row size).
Oracle stops inserting rows when only that % of free space is left.
2. Extreme Cases
Case A: PCTFREE = 0
Means no space is reserved for updates.
Oracle fills the block completely with rows until there is only block overhead left.
Good for:
Static / insert-only tables (no updates).
Data warehouse fact tables, append-only logs.
Risk:
If rows are later updated and expand, row migration occurs (row moves to another block, leaving a pointer behind).
This can hurt query performance.
CREATE TABLE sales_data
(
sale_id NUMBER,
customer_id NUMBER,
amount NUMBER,
sale_date DATE
)
PCTFREE 0;
Since this is append-only (no updates), Oracle can fully pack rows per block = maximum storage efficiency.
Case B: PCTFREE = 100
Means 100% of block is reserved for updates.
Oracle will insert at most one row per block, then stop using that block for further inserts.
This is almost never practical, except for very special cases (like experimental testing).
Effectively wastes space, but guarantees maximum space for row expansion.
CREATE TABLE archive_data
(
doc_id NUMBER,
doc_text CLOB
)
PCTFREE 100;
This would result in one row per block — highly inefficient in most scenarios.
3. Best Practice for Tables with No Updates
Use PCTFREE = 0 → maximize rows per block, minimize wasted space.
Example workloads:
Audit tables (insert-only).
Data warehouse staging/fact tables (bulk-loaded, no updates).
History/archive tables (immutable).
Summary:
PCTFREE = 0 → best for insert-only/static tables, maximum efficiency.
PCTFREE = 100 → wastes space (one row per block), only useful in rare cases.
For normal OLTP tables with updates, choose something in between (e.g., 10–20%).
On ASSM and MSSM env:
As a DBA, when do we need to adjust PCFREE or PCTUSED?
1. Manual Segment Space Management (MSSM)
In MSSM, PCTFREE and PCTUSED directly control block availability:
PCTFREE reserves free space in each block for row updates.
PCTUSED determines when a block goes back to the freelist.
DBAs sometimes tuned these values for OLTP vs. DSS workloads.
But selecting MSSM, normally is not recommended unless you faced some special issues.
2. Automatic Segment Space Management (ASSM)
In ASSM (default for locally managed tablespaces), Oracle does not use PCTUSED.
Instead, Oracle internally manages block availability with bitmaps that track free space categories (0–25%, 25–50%, 50–75%, 75–100%).
This removes freelist management overhead.
PCTFREE still matters:
It is still honored, because Oracle must know how much space to leave in each block for row expansion.
Example: If PCTFREE = 20, Oracle will stop inserting into a block when 20% free space remains, even in ASSM.
3. So, when should you change them in ASSM?
Change PCTFREE if:
Your rows are likely to expand after inserts (updates to VARCHAR2, CLOB, etc.) → increase PCTFREE.
Your rows are mostly fixed-size, or inserts dominate with no growth → decrease PCTFREE (more rows per block).
Do not change PCTUSED in ASSM:
It’s ignored by ASSM.
Oracle decides block reuse automatically via bitmaps.
Key takeaway:
In ASSM, you only tune PCTFREE (for row growth).
PCTUSED is irrelevant because ASSM manages space reuse internally.
As a DBA, can adjusting of PCTFREE , lead to minimize Blocks contention in Heavy Inserts?
1. What PCTFREE really does
PCTFREE controls reserved space per block for future row expansion, not a direct “rows per block” setting.
But increasing PCTFREE reduces the usable space per block → fewer rows fit per block → more blocks are used overall → contention on individual blocks may reduce.
So yes:
Higher PCTFREE = fewer rows per block = inserts spread across more blocks.
Lower PCTFREE = more rows per block = fewer blocks used, but higher risk of buffer busy waits or ITL contention.
2. When this helps:
In high-concurrency OLTP inserts where many sessions insert into the same table, contention often happens because:
Too many sessions are inserting into the same hot block (especially at the segment’s “end”).
ITL (Interested Transaction List) slots inside blocks are exhausted.
Raising PCTFREE forces Oracle to move on to new blocks earlier, spreading inserts and lowering hot-block contention.
3. Alternatives (often better than just raising PCTFREE):
Increase INITRANS on the table/index → ensures more ITL slots for concurrent inserts in each block.
Use ASSM (Automatic Segment Space Management) → already spreads inserts more evenly across blocks via bitmaps, reducing freelist/block contention.
Partitioning (hash/range) → spreads inserts across multiple segments.
Reverse key indexes (for sequences) → avoids “hot index leaf block” contention.
Sequence caching (CACHE + NOORDER or Scalable Sequences) → reduces contention on sequence numbers.
4. Tradeoff of high PCTFREE
You’re wasting block space → more blocks needed → larger table size.
Can increase buffer cache usage and I/O footprint.
It’s a workaround, not a root fix, for contention.
Conclusion :
Yes, you can raise PCTFREE to reduce the number of rows per block and spread inserts across more blocks, which can help minimize block contention.
But usually, the better approach is to tune INITRANS, ASSM, partitioning, or sequence/index design, because PCTFREE is mainly meant for row expansion, not concurrency control.
Written by:
Alireza Kamrani
No comments:
Post a Comment