To determine which segments are resulting in the highest rates of Global Cache activity, we can exploit the GV$SEGMENT_STATISTICS view. The following query lists the segments that are associated the highest number of Global Cache blocks received:
SQL> WITH segment_misses AS
2 (SELECT owner || '.' || object_name segment_name,
3 SUM(VALUE) gc_blocks_received,
4 ROUND( SUM(VALUE)* 100
5 / SUM(SUM(VALUE)) OVER (), 2) pct
6 FROM gv$segment_statistics
7 WHERE statistic_name LIKE 'gc%received' AND VALUE > 0
8 GROUP BY owner || '.' || object_name)
9 SELECT segment_name,gc_blocks_received,pct
10 FROM segment_misses
11 WHERE pct > 1
12 ORDER BY pct DESC;
SEGMENT_NAME GC_BLOCKS_RECEIVED PCT
---------------------------------------- ------------------ ------
TRANSIM.GH_SALES_TOTALS 1,641,395 79.31
TRANSIM.G_CART_PK 104,014 5.03
TRANSIM.G_CART 86,185 4.16
SYS.ALERT_QT 39,476 1.91
SYS.SYS_IOT_TOP_8797 22,895 1.11
The “hot row” scenario – for instance an order number generated from a sequence table – is hard to correct without application changes. If many transactions frequently access a specific row, then that row is inevitably going to circulate at high rates throughout the cluster. If this overhead is unacceptable, then you will probably want to isolate the workloads concerned to specific instances in the cluster – perhaps by using services. Alternatively, you could avoid these single-row contention points by changing your application design (perhaps using an Oracle sequence generator rather than a sequence table).
For instance, lets say we are trying to reduce the interconnect load caused by the GH_SALES_TOTALS table from the example output above. We could create a service that is associated with a single instance of the cluster and then allocate all sessions that access GH_SALES_TOTALS to that service. The result would be that all SQLs that access GH_SALES_TOTALS would execute on a specific instance and the interconnect activity would be eliminated.
In the more general case where blocks or specific segments are associated with heavy Global Cache activity, we can attempt to reduce the amount of inter-instance traffic through one of the following techniques:
Isolating workloads to a particular instance or groups of instances. We can do this through services configuration as discussed earlier. If transactions and queries that access specific segments or blocks are isolated to a smaller number of instances, then the amount of Global Cache traffic will be reduced. However this does make balancing the cluster more difficult.Isolating sessions that are likely to work on the same data. This is similar to isolating workloads, but instead of isolating specific transaction types, we isolate sessions that are likely to work on the same sets of data. For instance, we might allocate sessions from the east coast to one set of instances, and west coast to another. Again, this raises concerns about balance: in the East coast/West coast scenarios we imbalance the cluster because of the differences in the number of users in each region and the time zones that correspond to peak processing. Nevertheless, this sort of partitioning will result in a reduction in Global Cache traffic and - if we are careful with our services configuration - we can still achieve an acceptable dis