Oracle storage parameters in tables along with techniques for tuning and optimizing heavy loads

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

1. Fresh block → available for inserts.
2. Insert rows until 6400 bytes used → block “full,” no new rows allowed.
3. Updates grow rows → free reserved space (1600 bytes) is used.
4. Deletes reduce space below 3200 (PCTUSED) → block re-enters freelist.
5. 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