Oracle Real Application Clusters (RAC) Global Cache Tuning :

Introduction :

Everything covered so far – blocking, deadlocks, ADDM, SPA, AWR Compare Periods, SQL Monitor, 10053, Database Replay, ASH – assumes a single database instance. In a RAC environment, that assumption breaks. Multiple instances share the same database files, and a block that one instance needs may be held in another instance’s buffer cache. The mechanism that coordinates this is the Global Cache (GCS, Global Cache Service), and it introduces an entirely new class of wait events that do not exist on a single instance.

This is why RAC tuning is its own discipline. A query that runs in 2 seconds on a single instance can run in 20 seconds on RAC if it is constantly requesting blocks across the interconnect. The SQL is identical, the plan is identical, the statistics are identical – but the performance is not. The difference is the interconnect traffic and the global cache protocol.

The goal of this guide is to explain the theoretical model behind global cache contention and then walk through the diagnostic process step by step, with the queries a DBA actually runs in production.

Theoretical Foundation :

What the Global Cache Does :

In a RAC cluster, each instance has its own buffer cache. But all instances access the same data files. When Instance A wants to read a block, it may find that Instance B already has that block in its buffer cache, possibly in a modified (dirty) state. The Global Cache Service (GCS) coordinates this:

• If the block is clean and Instance A only needs to read it, GCS grants a shared (S) lock. Both instances can read the block simultaneously.

• If the block is dirty or Instance A needs to modify it, GCS must transfer the block from Instance B to Instance A and grant an exclusive (X) lock. Instance B’s copy is invalidated.

This transfer happens over the interconnect (private network). The latency of that transfer is measured in microseconds on modern hardware, but under heavy contention, it becomes the dominant cost of the query.

The Three Global Cache Wait Events :

Almost all global cache contention falls into three wait events :

The distinction between gc buffer busy acquire and gc buffer busy release is important. acquire means the contention is local – two sessions on the same instance want the same block. release means the contention is remote – the block is held by another instance and that instance is slow to release it.

The Global Cache Efficiency Metrics :

Oracle exposes two critical ratios in V$GCSHVMASTER and GV$GCSHVMASTER (or in the AWR global cache section):

A high hit ratio does not mean there is no problem. A system with a 95% global cache hit ratio can still suffer from gc buffer busy waits if the 5% of misses are concentrated on a hot block. The ratio is a starting point, not a diagnosis.

The Root Cause Pattern :

Global cache contention almost always traces back to one of three patterns:

1. Hot block – Many sessions across multiple instances modify the same block. Classic example: a sequence-like table with a single row that every session updates, or an index with a monotonically increasing key where the rightmost leaf block is constantly modified.

2. Read-mostly contention – Many sessions read the same block, and the block keeps getting invalidated because one instance occasionally modifies it. The cr transfer cost dominates.

3. Interconnect latency – The interconnect itself is slow or saturated. The block transfer time is high even when there is no logical contention. This shows up as high gc cr block transfer time with a low number of transfers.

The diagnostic process must distinguish these three, because the fix is different for each.

Step 1 – Confirm the Problem is Global Cache :

Query GV$SESSION for Global Cache Waits :

SELECT inst_id, event, COUNT(*) AS sessions_waiting, ROUND(AVG(seconds_in_wait), 2) AS avg_wait_sec, ROUND(MAX(seconds_in_wait), 2) AS max_wait_sec
FROM gv$session WHERE event LIKE 'gc %' GROUP BY inst_id, event ORDER BY sessions_waiting DESC;

What the Output Reveals :

If gc buffer busy acquire dominates on one instance, the contention is local to that instance. If gc buffer busy release dominates, the blocker is on another instance. If gc cr/current block transfer dominates, the interconnect is the problem.

Step 2 – Identify the Hot Block :

The next step is to find which object and which block is causing the contention.

Query GV$ACTIVE_SESSION_HISTORY for the Hot Object :

SELECT inst_id, current_obj#, current_file#, current_block#, COUNT(*) AS samples, MIN(event) AS wait_event FROM gv$active_session_history
WHERE event LIKE 'gc %' AND sample_time > SYSDATE - 30/1440 GROUP BY inst_id, current_obj#, current_file#, current_block# ORDER BY samples DESC
FETCH FIRST 20 ROWS ONLY;

Resolve the Object Name :

SELECT object_name, object_type, owner FROM dba_objects WHERE object_id = &current_obj#;

Theoretical Note :

The combination of current_file# and current_block# identifies the exact block. If 90% of the gc buffer busy samples point to the same block of the same table, that is the hot block. This is the most important diagnostic output in RAC tuning – it tells you exactly where the contention is concentrated.

Step 3 – Classify the Contention Pattern :

Check Whether the Hot Block is a Table or an Index :

Check the Access Pattern :

SELECT sql_id, COUNT(*) AS samples, MIN(event) AS top_event FROM gv$active_session_history WHERE current_obj# = &object_id AND event LIKE 'gc %'
AND sample_time > SYSDATE - 30/1440 GROUP BY sql_id ORDER BY samples DESC;

This tells you which SQL statements are hitting the hot block. If it is a single UPDATE statement, the fix is application-level. If it is many different statements, the fix is structural.

Step 4 – Measure Interconnect Latency :

Query GV$GCSHVMASTER for Transfer Statistics :

SELECT inst_id, name, value FROM gv$gcs_hvmaster WHERE name IN ('gc cr block received','gc current block received','gc cr block transfer time',
'gc current block transfer time');

Calculate Average Transfer Time :

The transfer time is in centi-seconds (hundredths of a second). Divide by the block count to get the average latency per transfer.

Rule of thumb :

If the transfer time is high but the transfer count is low, the interconnect is slow. If both are high, the interconnect is saturated – either add bandwidth or reduce the number of transfers.

Check the Interconnect Configuration :

SHOW PARAMETER cluster_interconnects;

In Oracle 19c and later, the recommended configuration is to use HAIP (Highly Available IP) or a dedicated private network with jumbo frames. If the interconnect is shared with the public network, that is a red flag.

Step 5 – Distinguish Local vs. Remote Contention :

Query GV$SESSION for Blocking Topology :

SELECT s.inst_id AS blocked_inst, s.sid AS blocked_sid, s.event AS blocked_event, s.blocking_session AS blocker_sid, b.inst_id AS blocker_inst,
b.event AS blocker_event FROM gv$session s LEFT JOIN gv$session b ON s.blocking_session = b.sid AND s.blocking_session_status = 'VALID'
WHERE s.event LIKE 'gc %' ORDER BY s.inst_id, s.sid;

Interpretation :

Step 6 – Apply the Correct Fix :

For Hot Row Contention (Table) :

Problem : Many sessions update the same row, causing constant gc current block transfer and gc buffer busy.

Fixes :

1. Application redesign – Do not update a single row from all sessions. Use partitioning or a distributed counter.

2. Reverse key index – If the contention is on a sequence-generated primary key index, use a reverse key index to spread inserts across leaf blocks.

3. Partitioning – Partition the table by a key that distributes updates, so different sessions hit different blocks.

4. GC_BUFFER_BUSY hints – Not a real fix, but a temporary workaround.

For Right-Hand Index Leaf Contention (Index) :

Problem : Monotonically increasing key (sequence) causes all inserts to hit the same rightmost leaf block.

Fixes :

1. Reverse key index – CREATE INDEX ... REVERSE;

2. Hash partitioning – Spread inserts across partitions.

3. Sequence caching – Increase CACHE on the sequence, or use NO-ORDER if ordering is not required.

For Interconnect Latency :

Fixes :

1. Verify that the private network is dedicated (not shared with public traffic).

2. Enable jumbo frames (MTU 9000) on the interconnect.

3. Check switch configuration for packet loss or retransmission.

4. In Oracle 19c, verify HAIP is configured correctly.

Step 7 – Monitor After the Fix :

Re-run the Diagnostic Queries :

After the fix, re-run the queries from Steps 1 and 2. The sample count for the hot block should drop significantly.

Check the Global Cache Hit Ratio :

SELECT inst_id, name, value FROM gv$sysstat WHERE name IN ('gc cr blocks received','gc current blocks received','gc blocks lost','gc claim blocks lost');

The gc blocks lost and gc claim blocks lost metrics should be zero in a healthy cluster. Any non-zero value indicates interconnect problems  packet loss or misconfiguration.

Summary :

Closing Note :

RAC global cache tuning is difficult because the problem is not visible from any single instance. V$SESSION on Instance 1 shows nothing if the blocker is on Instance 2. The GV$ views – global versions of the V$ views – are mandatory. Every query in this guide uses GV$, not V$. This is the single most common mistake when tuning RAC: forgetting that the problem is distributed.

The theoretical model is simple: blocks move between instances over the interconnect, and the cost of that movement is the new performance variable. The diagnostic work is identifying which block, which instance, and which SQL statement is driving the movement. Once you know that, the fix is usually structural – reverse key indexes, partitioning, or interconnect configuration.