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
