Performance Tuning Considerations in Oracle RAC env

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.

https://docs.oracle.com/en/database/oracle/oracle-database/21/cncpt/indexes-and-index-organized-tables.html

 

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.

https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/designing-and-developing-for-performance.html

https://docs.oracle.com/en/database/oracle/oracle-database/21/cncpt/indexes-and-index-organized-tables.html

 

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.

https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/designing-and-developing-for-performance.html

 

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

For many RAC applications, NOORDER is preferable when globally ordered sequence values are not a business requirement because enforcing global ordering can require additional coordination between instances.

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.

https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/designing-and-developing-for-performance.html

 

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.

https://docs.oracle.com/en/database/oracle/oracle-database/19/sbydb/introduction-to-oracle-data-guard-concepts.html

 

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.

https://docs.oracle.com/en/database/oracle/oracle-database/26/haovw/tune-and-troubleshoot-oracle-data-guard1.html

 

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.

https://docs.oracle.com/en/database/oracle/oracle-database/26/rilin/load-balancing-of-connections-to-oracle-rac-databases.html

 

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:

1. Identify the owning Oracle component.
2. Review the documented management procedure.
3. Understand dependencies.
4. Validate the procedure in a non-production environment.
5. 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.