Oracle RAC GC (Global Cache) waits

Oracle RAC GC (Global Cache) waits occur when multiple instances in a Real Application Clusters (RAC) environment contend for access to a shared cache. These waits can impact performance and scalability. Let's discuss common findings related to RAC GC waits and some potential solutions, including relevant commands.
 
Identify top wait events related to GC Buffer Busy waits:
SELECT event, total_waits, time_waited
FROM gv$system_event
WHERE event LIKE 'gc buffer busy%';

Monitor the most accessed objects:
SELECT owner, object_name, object_type, buffers, gets
FROM dba_objects
ORDER BY gets DESC; 

• Implement partitioning, caching mechanisms, or workload distribution strategies to reduce contention.

Unbalanced Global Cache Fusion:
Check the workload distribution across instances:
SELECT inst_id, count(*)
FROM gv$session
GROUP BY inst_id;

SELECT inst_id, name, network_type, bandwidth, latency FROM gv$cluster_interconnects;

• Ensure the interconnect network is functioning properly and has sufficient bandwidth.
• Redistribute workload across instances using connection load balancing or Oracle services.

Inadequate Buffer Cache Size:
Check buffer cache hit ratios:
SELECT name, value
FROM gv$sysstat
WHERE name IN ('db block gets', 'consistent gets', 'physical reads');

• Adjust the buffer cache size using the DB_CACHE_SIZE initialization parameter or Automatic Shared Memory Management (ASMM) / Automatic Memory Management (AMM) features.

High Library Cache Lock:
Identify top SQL statements causing contention:
SELECT sql_text, executions, child_number
FROM gv$sql
WHERE address IN (
  SELECT address
  FROM gv$session
  WHERE event LIKE 'library cache%');

• Optimize SQL statements using SQL plan management, indexing, query rewriting, or SQL profile hints.