Global Cache Waits, Hot Blocks, Sequence/Index Contention, and Scale-Out Workload Design

Learning outcomes

After adding a second RAC node, ServiceHub throughput gets worse. Both instances insert into the same right-growing primary-key index and update the same “open queue” rows. Sessions spend time transferring blocks between caches. More CPU was added, but the bottleneck became global cache contention. RAC performance work begins by locating the blocks/objects/SQL that move or wait—not by adding a third node.

1. A Cache Fusion request becomes a precise gc wait outcome

RAC global-cache waits belong to the Cluster wait class. A session initially requests a gc current block or gc cr block; Oracle later attributes the wait to a more precise outcome such as 2-way, 3-way, busy or congested.

gc current block busy/gc cr block busy mean the requested block could not be shipped immediately—for example it was pinned, busy, waiting on remote log flush, or queued behind concurrent access. gc buffer busy acquire/release indicates local access is waiting behind an outstanding global-cache operation on that buffer.

2. Start with cluster wait deltas, not lifetime totals

SELECT  inst_id,  event,  total_waits,  time_waited_micro,  CASE WHEN total_waits > 0       THEN ROUND(time_waited_micro/1000/total_waits,3)  END AS avg_msFROM gv$system_eventWHERE wait_class='Cluster'ORDER BY time_waited_micro DESC;

Take two timestamped snapshots around the slow interval. High counts with tiny service time can be harmless; fewer long waits on a critical request path can be serious. Compare CPU/run queue and interconnect health before blaming “Cache Fusion” generically.

3. Find the active block and SQL

SELECT  inst_id,  sid,  serial#,  sql_id,  event,  p1 AS file_no,  p2 AS block_no,  seconds_in_waitFROM gv$sessionWHERE event LIKE 'gc %'ORDER BY seconds_in_wait DESC;

SELECT *FROM (  SELECT    inst_id,    sql_id,    executions,    buffer_gets,    cluster_wait_time,    elapsed_time,    sql_text  FROM gv$sqlarea  WHERE cluster_wait_time > 0  ORDER BY cluster_wait_time DESC)FETCH FIRST 20 ROWS ONLY;

CLUSTER_WAIT_TIME helps prioritize SQL, but high cluster time may be a symptom of object hot spots or instance placement. Read the plan and object access pattern before rewriting SQL.

4. Identify globally shared/hot segments

SELECT *FROM (  SELECT    inst_id,    owner,    object_name,    subobject_name,    object_type,    statistic_name,    value  FROM gv$segment_statistics  WHERE statistic_name IN (    'gc current blocks received',    'gc cr blocks received',    'gc buffer busy'  )  ORDER BY value DESC)FETCH FIRST 30 ROWS ONLY;

If the same few index/table blocks dominate across instances, investigate hot-row/update concentration, right-growing indexes, freelist/ITL/block concurrency, service placement and batch partitioning.

5. Sequential keys can create a right-hand index hot spot

When monotonically increasing primary keys are indexed, new keys target the right-most B-tree leaf range. Concurrent inserts from several RAC instances can repeatedly request/modify the same leaf blocks, causing inter-instance ownership movement.

A reverse-key index reverses key bytes so adjacent numeric keys land across different leaf areas; it can reduce right-edge contention, but ordinary range scans over the original key ordering lose their natural B-tree range-order benefit. A hash-partitioned index or scalable sequence can be better when range access still matters.

6. Sequence configuration can amplify or reduce coordination

Oracle recommends CACHE for sequences in RAC. CACHE NOORDER has the least sequence-generation coordination cost for ordinary surrogate keys and is the default style. ORDER guarantees request ordering and adds global ordering work; 26ai includes ordered-sequence optimizations, but ordering is still a semantic requirement you should request only when needed.

Scalable sequences add an instance/session-derived offset to spread high-concurrency generated keys and significantly reduce both sequence and index-block contention. They intentionally produce noncompact/nonchronological key values.

CREATE SEQUENCE servicehub_event_seq  START WITH 1  INCREMENT BY 1  CACHE 1000  NOORDER;

1000 is a demonstration value, not a universal RAC cache size. Sequence gaps are normal and can increase after instance failure/restart; never use sequence gaplessness as a financial/legal numbering guarantee without a dedicated design.

7. Free design lab: observe the right-growing index and an alternative

BEGIN  EXECUTE IMMEDIATE 'DROP TABLE servicehub_rac_insert PURGE';EXCEPTION WHEN OTHERS THEN  IF SQLCODE != -942 THEN RAISE; END IF;END;/BEGIN  EXECUTE IMMEDIATE 'DROP SEQUENCE servicehub_event_seq';EXCEPTION WHEN OTHERS THEN  IF SQLCODE != -2289 THEN RAISE; END IF;END;/CREATE SEQUENCE servicehub_event_seq  CACHE 1000  NOORDER;CREATE TABLE servicehub_rac_insert (  event_id NUMBER NOT NULL,  status_code VARCHAR2(12) NOT NULL,  payload VARCHAR2(100),  CONSTRAINT sh18_event_pk PRIMARY KEY(event_id));INSERT INTO servicehub_rac_insertSELECT servicehub_event_seq.NEXTVAL,       'OPEN',       RPAD('x',50,'x')FROM dualCONNECT BY LEVEL <= 20000;COMMIT;

SELECT index_name,index_type,statusFROM user_indexesWHERE table_name='SERVICEHUB_RAC_INSERT';ALTER INDEX sh18_event_pk REBUILD REVERSE;SELECT index_name,index_type,statusFROM user_indexesWHERE index_name='SH18_EVENT_PK';

The lab shows a real Oracle index design alternative, but on Free there is only one instance, so it cannot demonstrate global-cache block pinging. Compare point lookups and any required range queries before adopting reverse key.

DROP TABLE servicehub_rac_insert PURGE;DROP SEQUENCE servicehub_event_seq;

8. Service/data affinity can be more powerful than changing the index

If ServiceHub regions/tenants naturally own disjoint hot working sets, services can route those workloads preferentially to instances so the same blocks remain local more often. Affinity must follow real workload/data boundaries; arbitrary pinning can create imbalance or reduce failover capacity.

9. Deliberately wrong: add another RAC node to fix hot blocks

If two instances already fight over the same small set of blocks, adding a third requester can increase ownership movement and queuing. Capacity scale-out works best when the workload can be distributed; contention scale-out requires changing the contention mechanism.

10. Production judgment

Use cluster wait deltas, SQL/object attribution and interconnect/CPU evidence before changing schema or placement. Prefer ordinary SQL/index tuning first when a statement touches too many blocks. Use service affinity and specialized key/index designs only when the workload shape supports them and after validating range-query/operational tradeoffs.

No hidden/underscore RAC parameters are recommended. ADDM/AWR may help on licensed deployments, but this lesson's core views do not require them. Lesson 4 shifts from steady-state contention to state changes: a RAC node fails or is deliberately drained for maintenance and the cluster must reconfigure without turning a planned operation into an outage.

Authoritative references

• Monitoring Performance — gc waits, SQL/object diagnostics and contention
• Classes of Wait Events — Cluster wait class
• CREATE SEQUENCE — CACHE/ORDER/NOORDER semantics
• Managing Sequences — scalable sequence behavior
• Indexes and Index-Organized Tables — reverse-key index and RAC hot-leaf rationale