Oracle RAC Performance Analysis: Cache Fusion, Interconnect Communication, and Transaction Contention Assessment in Oracle Database 19c.


                     
1. Introduction
Oracle Real Application Clusters (RAC) enables multiple database instances to access a
single database residing on shared storage. Each RAC instance maintains its own buffer
cache; however, data consistency across instances is maintained through Oracle RAC
Cache Fusion.
Cache Fusion is a disk-less cache coherency mechanism that transfers data blocks directly
between RAC instance memory structures through the private interconnect. This
eliminates unnecessary disk I/O and improves scalability while ensuring data consistency
across clustered database instances.

This project demonstrates a practical Cache Fusion analysis performed in a two-node
Oracle RAC 19c laboratory environment to understand block transfers, global cache
operations, wait events, and RAC interconnect behaviour.

The study also evaluates transaction contention behavior and RAC inter-instance
communication using AWR, ADDM, and dynamic performance views.

2. Objective

The objective of this activity is to analyze Oracle RAC Cache Fusion behavior and understand how Oracle coordinates block ownership between multiple RAC instances.

Specific objectives include:

 Understanding Cache Fusion architecture

 Identifying Global Cache Service operations

 Observing CR and Current block transfers

 Monitoring RAC-specific wait events

 Evaluating interconnect communication

 Generating RAC workload safely

 Analyzing inter-instance transaction contention

 Performing performance analysis using AWR and dynamic performance views

3. Scope

This assessment covers:

 Oracle RAC Cache Fusion

 Global Cache Service (GCS)

 Global Enqueue Service (GES)

 Current Block Transfers

 Consistent Read (CR) Block Transfers

 RAC Wait Events

 Interconnect Communication

 AWR-Based Performance Analysis

This activity does not include application performance tuning, operating system tuning, storage optimization, or network redesign.

4. Environment Details

Component                        Details

Database Version               Oracle Database 19c (19.3)

Database Name                  PROD

PDB                                   PRODS

Instances                            prod1, prod2

Cluster Type                      2-Node Oracle RAC

Node 1                               node1.localdomain

Node 2                               node2.localdomain

Operating System             Oracle Linux 7.9

Storage                              ASM

Test User                           U1

Test Table                         CACHEFUSION_TEST

Test Rows                        100,000

5. Prerequisites

Before starting the analysis:

 Oracle RAC cluster must be operational

 Both instances must be open

 ASM must be healthy

 SYSDBA access must be available

 AWR snapshots must be enabled

 Private interconnect must be operational

 Sufficient free space must exist in USERS tablespace

6. Use Cases

Cache Fusion analysis is commonly used for:

 RAC performance troubleshooting

 Interconnect latency investigation

 Global cache wait analysis

 Hot block identification

 RAC architecture assessment

 Scalability validation

 Capacity planning exercises

7. Methodology

The analysis follows five phases:

Phase 1 – Environment Validation

Phase 2 – Baseline Statistics Collection

Phase 3 – Controlled Cache Fusion Workload Generation

Phase 4 – RAC Wait Event Analysis

Phase 5 – AWR-Based Performance Assessment

8. Implementation Steps

Step 1 – Validate RAC Environment

Verify active RAC instances.


Verify database version.


Verify cluster database configuration.


Step 2 – Validate RAC Interconnect



Step 3 – Capture Baseline Statistics

Capture RAC wait events before workload generation.


Capture Global Cache statistics.


Step 4 – Create Test Table


Step 5 – Populate Test Data

Step 6 – Capture Baseline Cache Fusion Statistics


Step 7 – Create AWR Snapshot



Step 8 – Open Two Sessions

Session A (Instance prod1) Connect specifically to instance 1.


DO NOT COMMIT.

Keep transaction open.

Session B (Instance prod2)

Open a second terminal.


Step 9 – Observe Cache Fusion Wait Events

As sys:


Look for:

 gc current request

 gc cr request

 gc current block busy

 gc cr block busy

These are the key Cache Fusion events.

Step 10 – Check Active Sessions


Step 11 – Analyze Interconnect


Step 12 – Capture Post-Test Statistics

Run again:


Compare with baseline.

You should observe increases in Global Cache metrics.


Step 13 – Commit Transaction

Return to Session A:


Step 14 – Final AWR Snapshot


9. Findings And Analysis

The following observations were recorded during the assessment:

 Cache Fusion block transfers occurred between RAC instances.

 Global Cache wait events increased during workload execution.

 Current blocks were transferred through the RAC interconnect.

 Consistent Read blocks were generated when required.

 No disk-based block shipping was required for cache coherency.

 RAC instances maintained synchronized block ownership.

 gc current blocks received = 20,006 on prod2

 gc current blocks served = 20,006 on prod1

 gc current block 2-way waits = 9,005 on prod2

 gc current block 2-way waits = 1,241 on prod1

AWR and ADDM Analysis

AWR snapshots 119 and 120 were collected during the RAC workload execution window. ADDM analysis identified significant database activity associated with concurrent update operations performed against the CACHEFUSION_TEST table in the PRODS pluggable  database.

Key Observations

 Total Database Time: 651 seconds

 Average Active Sessions: 0.61

 Primary Wait Event: enq: TX - row lock contention

 Impact on Database Activity: 98.4%

 Affected Object: U1. CACHEFUSION_TEST

 Blocking Session: SID 268 on Instance PROD2

 Waiting Session: Instance PROD1

 RAC Interconnect Latency: Less than 1 millisecond


SQL Analysis

The following SQL statement generated the majority of database activity:

update CACHEFUSION_TEST

set remarks='NODE1'

where id between 1 and 50000;

ADDM reported that the SQL statement experienced row lock contention due to

concurrent updates on overlapping row ranges from different RAC instances.


RAC Analysis

The test successfully demonstrated Oracle RAC inter-instance transaction coordination.

Concurrent DML operations executed from separate RAC nodes resulted in blocking and

waiting sessions. Oracle RAC Global Enqueue Services (GES) managed lock ownership

and transaction consistency across both instances.


Performance Impact

The observed contention was application-driven rather than infrastructure-driven. Cluster

wait events were not significant, indicating healthy RAC interconnect communication and

efficient cache synchronization between instances.


Root Cause

The primary cause of performance degradation was concurrent updates on the same data

set from multiple RAC instances, leading to row-level lock contention.


Recommendations

 Avoid overlapping update ranges whenever possible.

 Commit transactions promptly to minimize lock duration.

 Implement application-level workload partitioning.

 Monitor GV$SESSION and GV$LOCK for blocking sessions.

 Use AWR and ADDM for proactive RAC performance analysis.

 Review application design to reduce transaction conflicts in RAC environments.

10. Risks and Mitigation

Risk:

 Excessive row locking

 Cluster performance degradation

 Misinterpretation of RAC waits

 Long-running transactions

Mitigation:

 Use limited test data and controlled transactions.

 Execute during maintenance or lab hours.

 Capture baseline statistics before testing.

 Commit or rollback transactions immediately after analysis completion.

11. Validation

✓ gc current block 2-way waits observed

✓ gc current blocks served observed

✓ gc current blocks received observed

✓ RAC interconnect latency < 1 ms

✓ AWR and ADDM reports generated successfully

✓ Inter-instance transaction blocking reproduced

Key RAC Statistics Observed

Metric                                         PROD1                    PROD2

GC Current Blocks Served        20,006                       4,363

GC Current Blocks Received    4,363                         20,006

GC Remote Grants                    2,803                         2,874

GC KB Sent                              122,069                     32,441

12. Lessons Learned

 Cache Fusion activity can occur without becoming the primary performance bottleneck.

 Application-level row locking can overshadow Cluster wait events.

 AWR and ADDM provide complementary RAC diagnostic capabilities.

 RAC interconnect health is critical for efficient Global Cache operations.

 Proper workload design is required to generate measurable Cache Fusion traffic.

13. Conclusion

The assessment successfully demonstrated Oracle RAC inter-instance block transfers, Cache Fusion communication, and transaction contention behavior. A controlled workload generated measurable Global Cache activity, including gc current block transfers and gc current block waits, while ADDM identified row lock contention as the primary performance bottleneck. The study validated proper RAC interconnect operation and demonstrated how Oracle RAC coordinates cache coherency and transaction consistency across clustered database instances.

14. Limitations

 Laboratory workloads do not fully represent production workloads.

 Virtual machine networking may introduce artificial latency.

 Two-node RAC environments provide limited scalability testing.

 Results depend on workload design and may vary across different application patterns and cluster configurations.

15. Appendix

Appendix A – AWR Report

File Name: awrrpt_rac_119_120.html

Purpose:

Contains detailed RAC workload statistics, wait events, load profile information, and performance metrics collected during the Cache Fusion and lock contention test.

Appendix B – ADDM Report

Analysis Period: 10-JUN-2026 07:55 PM to 10-JUN-2026 08:13 PM

Key Findings:

 Top SQL Statements

 Row Lock Contention

 Blocking Session Identification

 RAC Instance Activity Distribution

Appendix C – Supporting Evidence

Screenshots Included:

 GV$SESSION blocking session details

 RAC instance information

 CACHEFUSION_TEST workload execution

 Snapshot creation

 ADDM findings

 AWR report generation

16. Business Impact

The assessment demonstrates how Oracle RAC maintains transaction consistency and cache coherency across clustered database instances. Understanding Cache Fusion behavior and transaction contention patterns helps database administrators proactively identify scalability constraints, reduce application bottlenecks, and improve RAC workload distribution in enterprise environments.

No comments:

Post a Comment