Minimizing Global Cache traffic
As we saw earlier, Global Cache requests are integral to RAC and represent both the “cost” of the RAC architecture and the basis of its scalability. Avoiding a disk read by fetching a needed block from another instance prevents RAC databases from becoming IO bound. However, each Global Cache request adds overhead: it’s far better to find the data you want in the local buffer cache than to retrieve it from another instance.
Very high Global Cache request rates generally result in a poorly performing RAC cluster. The overhead of the Global Cache activity probably means that response time for the RAC database will be higher than for its single-instance equivalent and may suggest that scalability problems will emerge as more instances are added.
High Global Cache request rates are generally the result of application processing patterns:
“Hot” rows or blocks that are needed by every instance and/or every transaction. For instance a table-based sequence number generator may contain a single row which has the next order sequence number. This block will be required every time an order is created on any instance and will therefore be constantly shipped across the cluster. Other “hot” blocks. These are the same sorts of blocks that might have caused cache buffers chains latch contention in a single instance database. The cures for cache buffers chains latch contention (reducing rows per block or partitioning) may also be effective in the RAC environment.Full table scans that retrieve large numbers of blocks across the interconnect.To determine how often the database needs to make Global Cache requests, we can compare the number of blocks fetched across the interconnect with the total number of block accessed (e.g., the number of logical reads). The following query performs that calculation as well as determining the ratio of physical to logical reads (yes, the notorious Buffer Cache Hit Ratio):
SQL> WITH sysstats AS (
2 SELECT inst_id,
3 SUM(CASE WHEN name LIKE 'gc%received'
4 THEN VALUE END) gc_blocks_recieved,
5 SUM(CASE WHEN name = 'session logical reads'
6 THEN VALUE END) logical_reads,
7 SUM(CASE WHEN name = 'physical reads'
8 THEN VALUE END) physical_reads
9 FROM gv$sysstat
10 GROUP BY inst_id)
11 SELECT instance_name, logical_reads, gc_blocks_recieved, physical_reads,
12 ROUND(physical_reads*100/logical_reads,2) phys_to_logical_pct,
13 ROUND(gc_blocks_recieved*100/logical_reads,2) gc_to_logical_pct
14 FROM sysstats JOIN gv$instance
15 USING (inst_id);
Instance Logical GC Blocks Physical Phys/Logical GC/Logical
name Reads Received Reads Pct Pct
---------- ------------ ------------ ------------ ------------ ----------
MELRAC3 15,353,311 1,730,818 23,099 .15 11.27
MELRAC2 148,903,331 1,756,882 438,531 .29 1.18
MELRAC1 21,792,614 1,730,366 39,471 .18 7.94
Note how in the above example it’s the least busy instances (in terms of logical reads) that have the highest Global Cache/Logical request ratio: the less busy an instance is, the more likely that the blocks it needs are in the memory of another, more busy, instance.
As is typical in general with “hit” or “miss” ratios, one should be cautious about forming too many conclusions, since the ratio will vary widely depending on workloa