Introduction
Performance
tuning in an Oracle Real Application Clusters (RAC) environment requires a
broader perspective than simply adjusting RAC parameters. Although RAC
introduces specific mechanisms such as Cache Fusion, global cache coordination,
inter-instance block transfers, and distributed workload execution, the most
significant performance improvements often come from addressing the underlying
application and SQL workload.
Poor SQL
access paths, excessive logical I/O, inappropriate indexing, high commit rates,
inefficient transaction distribution, and unnecessary cross-instance data
access can have a greater impact on performance than RAC-specific configuration
changes. Therefore, RAC performance tuning should be considered as an extension
of conventional Oracle database and application tuning rather than a
replacement for it.
A modern RAC
tuning methodology should begin by understanding the workload, identifying the
actual bottleneck, measuring its impact, and then applying the smallest
appropriate change. Particular attention should be given to hot blocks, index
design, sequence behavior, workload locality, redo generation, Data Guard
transport, memory sizing, and service-based connection routing.
The
following sections provide a structured approach to the major performance
considerations in an Oracle RAC environment.
1. Start
with Application and SQL Tuning
The first
step in RAC performance tuning should always be to examine the application and
SQL workload.
Before
changing RAC configuration, review:
- SQL
execution plans
- Logical
and physical I/O
- Excessive
parsing
- Index
usage
- Transaction
size
- Commit
frequency
- DML
patterns
- Hot
objects and hot blocks
- Connection
and service distribution
- Cross-instance
block access
A poorly
designed SQL statement can generate significant database activity regardless of
whether it is running on a single-instance database or RAC.
Similarly,
an inappropriate index can improve one query while significantly increasing DML
overhead. In RAC, this cost can become larger because frequently modified index
blocks may need to move between instances through Cache Fusion.
Therefore,
RAC-specific tuning should be performed in addition to, rather than
instead of, conventional SQL and application tuning.
2. Review
Index Design and Selectivity
Indexes
should be evaluated according to both their query benefit and their DML cost.
Before
creating an additional index, consider:
Query
benefit vs. DML maintenance cost vs. RAC Cache Fusion impact vs. redo
generation vs. storage/I/O
Every
INSERT, UPDATE, or DELETE may require index maintenance. In an INSERT-intensive
system, unnecessary indexes can significantly increase:
- CPU consumption
- Logical I/O
- Physical I/O
- Redo generation
- Index block
modifications
- RAC Cache Fusion
traffic
This becomes
particularly important when multiple RAC instances frequently modify the same
index blocks.
Index design
should therefore consider not only SQL selectivity but also where and how
DML is generated across the RAC cluster.
3.
Identify and Address Right-Growing Indexes
One
important RAC-specific scenario occurs with monotonically increasing index
keys, such as sequential primary keys or timestamp-based keys.
When
multiple instances continuously insert new rows using increasing key values,
inserts can concentrate on the right-most leaf blocks.
These blocks
can become hot blocks, resulting in increased contention and potentially
more current-block transfers between RAC instances.
The
important recommendation is therefore not simply to use Reverse Key indexes
in RAC.
The
correct approach is:
Identify hot index blocks
↓
Analyze the SQL access pattern
↓
Determine whether range scans are
required
↓
Select the appropriate index/key
distribution strategy
For example:
|
Workload |
Possible
approach |
|
Sequential
PK, insert-heavy, equality lookup |
Reverse Key
Index |
|
Sequential
PK, high concurrency, range scans required |
Hash-partitioned
index or another partitioning strategy |
|
Very high
sequence-generation activity |
CACHE +
NOORDER; evaluate scalable sequences where appropriate |
|
DML
naturally distributed by tenant/customer/date |
Table/index
partitioning |
|
One instance
performs most inserts |
Preserve
workload affinity where practical |
|
Multiple
instances constantly modify the same blocks |
Review
service placement, application routing, partitioning, and index design |
The correct
choice must be based on actual access patterns.
A Reverse
Key index can improve distribution of inserts, but it also changes the physical
ordering of index keys and therefore can negatively affect range-scan access.
Consequently, it should not be treated as a universal RAC tuning solution.
4. Review
Sequence Configuration
Sequences
can also influence scalability in highly concurrent RAC workloads.
For
INSERT-intensive applications, appropriately sized sequence caches can reduce
sequence-related overhead.
Important
considerations include:
- CACHE size
- NOORDER versus ORDER
- Application transaction rate
- Number of RAC instances
- Whether global ordering is actually required
- Sequence gaps and application expectations
- Scalable sequence capabilities where appropriate
- using a reverse key index
- using a hash partitioned index
- using a cycling sequence to prefix sequence values
- using a scalable sequence
Applications
should also not assume that sequence values are gap-free.
The
important principle is:
Sequence
numbers should be treated as identifiers, not as a mechanism for generating a
perfectly ordered business timeline.
Modern
Oracle releases also provide scalable sequence capabilities that can be
considered for very high-concurrency workloads.
However,
sequence optimization and index optimization should be evaluated together
because the sequence's value distribution directly affects the corresponding
index's key distribution.
5.
Understand the Difference Between RAC Index Contention and Standby NOLOGGING
An important
distinction must be made between RAC index contention and Data Guard
Standby NOLOGGING.
These
mechanisms address different problems.
RAC index
and key-distribution optimization
These
techniques can help with:
- Hot
index blocks
- DML
scalability
- Cache
Fusion traffic
- Inter-instance
block transfers
- Concurrent
inserts
Data
Guard Standby NOLOGGING
Standby
NOLOGGING is primarily a bulk-load/Data Guard performance optimization.
For
supported high-volume direct-path operations, it can reduce the conventional
redo overhead associated with certain operations while allowing the physical
standby to obtain the affected blocks required for consistency.
It should
therefore be considered when dealing with:
- Large
data loads
- Bulk
data movement
- High-volume
direct-path operations
- Data
Guard redo/standby performance
It should not
be presented as a solution for RAC index hot blocks or Cache Fusion contention.
The
relationship can be summarized as follows:
|
Optimization |
Primary
objective |
|
Index
optimization |
RAC/DML/Cache
Fusion scalability |
|
Sequence and
key-distribution optimization |
RAC insert
scalability |
|
Standby
NOLOGGING |
High-volume
operations and Data Guard impact |
These topics
are related because they can all affect the cost of a large DML workload, but
they solve different problems.
6.
Evaluate Table and Index Compression
Compression
should be evaluated according to the characteristics of the workload.
Appropriate
compression can reduce:
- Storage
consumption
- Physical
I/O
- Buffer-cache
footprint
- Number
of blocks required to access data
This can be
particularly useful for I/O-bound workloads.
However,
compression is not a direct solution for RAC contention.
Compression
can introduce additional CPU overhead and therefore should be evaluated
carefully in high-DML OLTP environments.
The decision
should therefore be based on measured workload characteristics rather than
assuming that compression will automatically improve RAC performance.
7. Size
Redo Logs According to Workload
Redo logs
should be sized according to the actual workload rather than using a fixed
number or arbitrary switching interval.
The analysis
should consider:
- Redo
generation rate
- Peak
transaction volume
- Log-switch
frequency
- Checkpoint
behavior
- Storage
capacity
- Recovery
requirements
- Data
Guard transport requirements
Frequent log
switches during peak workload may indicate that redo logs are undersized.
The
objective is not simply to maximize redo-log size but to provide sufficient
capacity to avoid unnecessary log-switch and checkpoint pressure while
remaining consistent with recovery and operational requirements.
https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-the-redo-log.html
8. Use
ASSM for Application Tablespaces
Automatic
Segment Space Management (ASSM) should generally be used for application tablespaces unless
there is a specific reason to use another design.
ASSM
automates free-space management and eliminates the need to manually configure
legacy parameters such as:
- PCTUSED
- FREELISTS
- FREELIST
GROUPS
This is
particularly useful in RAC environments where multiple instances may
concurrently perform DML against the same objects.
As with
other RAC recommendations, however, ASSM should be considered part of the
overall database design rather than treated as a standalone performance
solution.
https://docs.oracle.com/en/database/oracle/oracle-database/26/dbiad/all_diagrams.html
9.
Establish an AWR and ASH Performance Baseline
AWR and ASH
should be central components of the RAC performance-management process.
The goal is
not simply to collect more historical data, but to establish a useful baseline
that allows deviations from normal behavior to be identified.
Important
metrics include:
- CPU utilization
- Physical and logical
I/O
- SQL response time
- Top SQL
- Wait events
- Global Cache
activity
- Inter-instance block
transfers
- Commit behavior
- Redo generation
- Memory pressure
- Workload
distribution
AWR
retention should be aligned with operational and troubleshooting requirements.
Before
increasing retention, evaluate:
- Additional
storage requirements
- Repository
growth
- Operational
requirements
- Actual
historical troubleshooting needs
A large
amount of historical data is not automatically useful unless it supports
meaningful analysis.
https://docs.oracle.com/en/database/oracle/oracle-database/26/tdppt/managing-baselines.html
10. Size
SGA and Buffer Cache Based on Measurements
RAC does not
require a fixed percentage increase in buffer cache simply because additional
instances are added.
Memory
sizing should instead be based on:
- Working-set
size
- Logical
I/O
- Physical
I/O
- PGA
requirements
- SQL
workload
- Concurrency
- Memory
pressure
- Cache
efficiency
Adding RAC
instances does not mean that SGA or buffer cache should automatically be
increased linearly.
For example,
moving from two to four RAC instances does not necessarily mean:
Required
memory = 2 × previous memory
The correct
sizing must be validated using the actual workload.
https://docs.oracle.com/en/database/oracle/oracle-database/26/admin/managing-memory.html
11. Tune
Data Guard When Synchronous Transport Is Used
Data Guard
can become an important component of RAC performance when synchronous redo
transport is used.
In a RAC
primary with synchronous Data Guard transport, redo transport can become part
of the transaction commit path.
Consequently,
the following can affect primary transaction latency:
- Network
latency
- Network
throughput
- Standby
redo-log I/O
- Standby
database performance
- Transport
configuration
- SYNC
/ ASYNC
- AFFIRM
/ NOAFFIRM
- NET_TIMEOUT
- Transport
Lag
- Apply
Lag
This means
RAC and Data Guard should not always be analyzed as completely independent
components.
For example:
Application
↓
RAC Instance
↓
Redo generation
↓
Synchronous
transport
↓
Standby
↓
Commit acknowledgment
If the
synchronous transport path becomes slow, transaction commit latency can be
affected.
Therefore,
network latency, standby storage performance, redo transport throughput, and
Data Guard configuration should be included in the RAC performance analysis.
12.
Monitor RAC Global Cache Activity
RAC-specific
waits should be analyzed carefully rather than treated as isolated symptoms.
High
global-cache activity, excessive block transfers, and frequent remote block
access may indicate that the workload is not well aligned with the RAC
architecture.
Investigation
should include:
- Hot
blocks
- Object
access patterns
- Index
design
- Sequence
behavior
- Transaction
distribution
- Application
connection routing
- Service
placement
- Partitioning
- Data
locality
The key
question should be:
Why are
multiple instances repeatedly accessing the same blocks?
Rather than
immediately changing RAC parameters, first determine whether the application
workload itself can be distributed more effectively.
https://docs.oracle.com/en/database/oracle/oracle-database/26/racad/monitoring-performance.html
13.
Design Services and Connection Routing for Workload Locality
RAC services
are an important mechanism for aligning application workload with RAC
instances.
Where
appropriate, services can be designed so that particular workloads
preferentially execute on specific instances.
This can
help reduce unnecessary cross-instance access and improve workload locality.
Connection
pools should also be reviewed to ensure that:
- Connections are distributed as
intended
- Services are correctly configured
- Application workloads reach the
appropriate instances
- Failover behavior is understood
- Application Continuity or
Transparent Application Continuity is correctly configured where used
The
objective is not necessarily to distribute every workload equally across every
RAC instance.
Instead, the
objective is to achieve an appropriate distribution of workload while
minimizing unnecessary inter-instance coordination.
14. Treat
Oracle-Managed Objects Carefully
Database
administrators should avoid blindly applying generic reorganization or tuning
recommendations to specialized Oracle-managed objects.
Some
components, such as Oracle-managed application features, OLAP-related
structures, or other specialized objects, may have dependencies that are not
immediately visible through normal table and index administration.
Before
reorganizing such objects:
- Identify
the owning Oracle component.
- Review
the documented management procedure.
- Understand
dependencies.
- Validate
the procedure in a non-production environment.
- Execute
only the supported maintenance operation.
Generic
table/index administration techniques should not automatically be applied to
specialized Oracle-managed structures.
15. Use
Evidence-Based RAC Tuning
A modern RAC
tuning methodology should be evidence-driven.
The process
can be summarized as:
RAC Performance
Problem
│
↓
Establish
Baseline
│
↓
Identify Actual
Bottleneck
│
┌───────────────┼────────────────┐
↓ ↓ ↓
SQL/I/O Cache Fusion Commit/Redo
│ │ │
↓ ↓ ↓
Tune
SQL/Index Hot Blocks Redo/DG
│ │ │
└───────────────┼────────────────┘
↓
Validate the
Change
│
↓
Measure Again
This
approach prevents RAC tuning from becoming a collection of historical parameter
recommendations.
https://docs.oracle.com/en/database/oracle/oracle-database/26/racad/troubleshooting-oracle-rac.html
Conclusion: A Modern RAC Tuning Principle
The most
important principle in Oracle RAC performance tuning is:
Do not
tune RAC by applying a collection of historical parameter values. Tune the
workload first, measure global cache and commit behavior, identify the actual
bottleneck, and then make RAC-specific changes based on evidence.
In practice,
RAC performance is often determined by the interaction between SQL design,
indexing, data distribution, sequence behavior, transaction patterns, workload
locality, Cache Fusion, redo generation, Data Guard transport, and application
connection routing.
For example,
an index problem may actually originate from sequential key generation;
excessive Cache Fusion traffic may actually originate from poor workload
locality; and commit latency in a RAC/Data Guard architecture may actually be
related to synchronous redo transport.
Therefore,
the most effective RAC tuning strategy is not to optimize each component
independently. Instead, the database, RAC architecture, Data Guard
configuration, and application workload should be analyzed as a single
performance system.
