Creating Efficient RAC Data Objects: Reducing Cache Fusion Overhead and Improving Scalability

Creating Efficient RAC Data Objects: Reducing Cache Fusion Overhead and Improving Scalability

Oracle Real Application Clusters (RAC) provides high availability and horizontal scalability by allowing multiple database instances to access the same database simultaneously. While Oracle RAC's Cache Fusion architecture dramatically reduces disk I/O by transferring blocks directly between instance buffer caches, poorly designed database objects can still create excessive inter-instance traffic and become a significant scalability bottleneck.

One of the most overlooked aspects of RAC performance tuning is the design of database objects themselves. Efficient RAC object design minimizes block contention, reduces global cache transfers, and allows workloads to scale across nodes with minimal synchronization overhead.

Why Object Design Matters in RAC

In a single-instance database, multiple sessions accessing the same block generally remain local to the buffer cache. In RAC, however, if multiple instances require ownership of the same block, Oracle must coordinate access through the Global Cache Service (GCS) and Global Enqueue Service (GES), transferring blocks across the interconnect using Cache Fusion.

Although Cache Fusion is highly optimized, unnecessary block transfers can still increase:

• Global Cache (GC) waits
• Interconnect traffic
• Transaction latency
• CPU consumption
• Application response times

The goal of RAC-aware object design is simple:

Ø  Reduce the probability that multiple RAC instances need the same block at the same time.

1. Reduce Rows Per Block (RPB)

One classic RAC optimization is reducing the number of rows stored in a data block.

When a block contains many rows, multiple RAC instances are more likely to require data from that same block. If different nodes frequently update rows within the same block, Oracle must continuously transfer ownership of that block between instances.

Several techniques can reduce Rows Per Block (RPB):

Increase PCTFREE

A higher PCTFREE reserves additional space in each block, resulting in fewer rows per block.

SQL> CREATE TABLE orders (

    order_id NUMBER,

    customer_id NUMBER,

    order_date DATE

)

PCTFREE 30;

 

Benefits:

• Fewer rows per block
• Reduced probability of block sharing
• Lower cache fusion activity

Trade-off:

• Increased storage consumption

Use Smaller Block Sizes

For highly contended objects, smaller block sizes can reduce the number of rows stored per block and help isolate workload access patterns.

Ø  This technique should be used selectively after testing because it can increase total I/O requirements.

Deliberately Increase Row Size

Historically, some RAC implementations used filler columns to increase row length and reduce block density.

SQL> ALTER TABLE orders ADD rac_padding CHAR(50);

While generally not recommended in modern systems due to storage inefficiency, the concept highlights an important RAC principle:

Reducing block sharing is often more valuable than maximizing storage efficiency.

2. Minimize Index Contention

Indexes are often the largest source of RAC block transfers.

Data blocks may be distributed across the cluster, but index leaf blocks can become hotspots when multiple instances insert or update keys concurrently.

The original RAC design guidance highlights index maintenance as a major source of inter-instance block transfers.

Use Only Necessary Indexes

Every additional index increase:

• DML overhead
• Redo generation
• Cache Fusion traffic

Before creating an index, verify that it provides measurable query benefit.

3. Consider Reverse Key Indexes

Sequential keys generated by sequences often create "right-hand index growth."

All inserts target the same rightmost leaf block, creating a RAC hotspot.

Oracle provides Reverse Key Indexes specifically to address this issue.

CREATE INDEX orders_pk_idx

ON orders(order_id)

REVERSE;

A reverse key index reverses the bytes of the index key, spreading inserts across many leaf blocks instead of concentrating them on the right-hand side of the B-tree. This significantly reduces leaf block contention in RAC environments.

When to Use Reverse Key Indexes

Good candidates:

• Sequence-generated primary keys
• High-volume OLTP insert workloads
• Multi-node RAC systems

Avoid when:

• Frequent range scans are required

Because reverse key indexes scatter values, range scans become ineffective.

4. Use Locally Managed Tablespaces

Dictionary-managed tablespaces require centralized extent management.

In RAC, extent allocation and deallocation may require inter-instance coordination, increasing contention.

Locally Managed Tablespaces (LMTs) eliminate much of this overhead.

SQL> CREATE TABLESPACE oltp_data DATAFILE '+DATA' SIZE 50G EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

Benefits include:

• Reduced data dictionary contention
• Lower extent allocation overhead
• Better RAC scalability

Oracle has recommended LMTs as a best practice for many releases because they significantly reduce coordination costs.

5. Automate Space Management

Automatic Segment Space Management (ASSM) should be preferred over manual freelist management in most modern RAC deployments.

ASSM automatically distributes inserts across blocks, reducing freelist contention.

CREATE TABLESPACE oltp_data DATAFILE '+DATA' SIZE 50G SEGMENT SPACE MANAGEMENT AUTO;

Benefits:

• Reduced block contention
• Simplified administration
• Better scalability for concurrent DML

6. Pre-Allocate Extents for Heavy Insert Objects

For tables receiving heavy concurrent inserts from multiple RAC nodes, dynamic extent allocation can become a bottleneck.

Pre-allocating extents reduces runtime space management operations.

SQL> ALTER TABLE sales ALLOCATE EXTENT (  SIZE 100M );

Historically, Oracle RAC environments also allowed extent affinity strategies that helped isolate allocation activity to specific instances.

7. Use Sequences Correctly

Sequence design is one of the most important RAC object considerations.

Ø  Avoid ORDERed Sequences

ORDERed sequences force global coordination among instances.

SQL> CREATE SEQUENCE sales_seq NOCACHE ORDER;

This configuration is usually disastrous for RAC scalability.

Prefer Cached Sequences

Oracle recommends cached sequences because they reduce synchronization overhead.

SQL> CREATE SEQUENCE sales_seq CACHE 1000 NOORDER;

Benefits:

• Fewer data dictionary accesses
• Reduced row cache lock waits
• Better scalability under load

Oracle RAC documentation consistently recommends minimizing sequence synchronization and leveraging caching whenever possible.

 

Modern Alternative: Scalable Sequences

For Oracle 18c and newer, scalable sequences can often replace older RAC techniques such as reverse key indexes, providing improved scalability for sequence-generated keys.

8. Optimize Clustering Factor

The clustering factor measures how closely table rows align with index ordering.

A poor clustering factor causes:

• More logical I/O
• More block visits
• Greater likelihood of inter-instance block transfers

Ø  In RAC systems, clustering factor optimization becomes even more important because each additional block access can potentially involve Cache Fusion.

Strategies include:

• Reviewing column order in composite indexes
• Reorganizing tables where appropriate
• Evaluating access patterns before index creation

9. Separate Objects by Access Pattern

Tablespaces should be organized according to workload characteristics.

Recommended separation:

Frequently Modified Objects

• High DML tables
• OLTP indexes

Mostly Read-Only Objects

• Reference tables
• Lookup tables

Reporting Objects

• Data warehouse structures
• Historical data

This organization improves Oracle's Dynamic Resource Mastering, allowing cache resources to remain local to the instances that access them most frequently.

Practical RAC Design Principles

When designing database objects for RAC, focus on these priorities:

1.        Reduce block sharing between instances.

2.        Minimize index hot blocks.

3.        Use cached, non-ordered sequences.

4.        Favor locally managed tablespaces.

5.        Use ASSM for segment management.

6.        Pre-allocate space for heavy insert workloads.

7.        Evaluate reverse key indexes for sequential keys.

8.        Design tablespaces around workload affinity.

9.        Monitor Global Cache waits and tune the objects generating them.

Conclusion

Efficient RAC object design is fundamentally about reducing Cache Fusion traffic. Oracle RAC can automatically manage block transfers and dynamically allocate resources, but application and object design still determine how much inter-instance communication occurs.

The most successful RAC implementations are not necessarily those with the fastest hardware; they are the environments where data structures are designed to keep workload locality high and shared block access low.

By reducing rows per block, eliminating index hotspots, using cached sequences, leveraging locally managed tablespaces, and carefully organizing objects according to access patterns, DBAs can significantly improve RAC scalability while reducing global cache overhead.

In RAC, storage efficiency is often secondary to scalability efficiency. A few extra gigabytes of space are usually far cheaper than thousands of unnecessary Cache Fusion block transfers per second.

 

********************
Alireza Kamrani

Oracle ACE Pro / DATA Tech Lead