Single instance contention and RAC
Oracle makes a point of emphasizing that applications that don’t scale well on single instance Oracle will probably not scale well on RAC. While this is generally true, the switch from single instance to RAC tends to magnify some issues while alleviating others.
Performance issues that relate to contention for specific “hot” data blocks tend to be magnified in RAC, since these contentions now also take on a cross-instance overhead. For instance, buffer busy waits can occur for a buffer which is on another instance; because of the interconnect overhead, the average time spent waiting for a buffer busy wait to complete may increase. Specific types of contention thatincrease in a RAC database are:
Buffer busy waits. The sort of operations that cause buffer busy in single-instance will also cause buffer busy in RAC, but the wait will be amplified by the need to transfer the block across the cluster once it is free.Cache buffer chains latch contention in a single instance database will probably reduce in RAC, since latches are not acquired across instances. However, for every relatively short latch free wait, you will probably experience a much longer global cache wait when transferring the block concerned across instances.Sequence number generation. If there are SQ enqueue waits then these will likely spread and magnify across instances in a RAC environment.However, it’s also true that some single-instance contention issues can be reduced under RAC. RAC divides up the SGA and sessions across each instance in the cluster, effectively allowing some operations to experience an increase in concurrency. In particular, shared pool related latches and mutexes waits might reduce, since the activity will be distributed across the multiple shared pools in the cluster.
Contention for data and index blocks in a single instance database will probably magnify in a RAC environment. However, other contention points - library cache mutexes for instance - might be reduced.
Cluster overhead
Asides from anything else, we want to make sure that a RAC cluster is able to perform database activities without being impeded by cluster related overheads. In a healthy cluster, the time spent in cluster related activities is mainly determined by the average time to make a Global Cache request (Global Cache latency) multiplied by the number of Global Cache requests which must be made.
ClusterTime = AvgGCLatency x GCRequests
It therefore follows that reducing cluster overhead is mainly a process of minimizing the Global Cache latency and eliminating any unnecessary Global Cache requests. The importance of those optimizations will depend upon the relative time spent in cluster related activities.
We can see the overall contribution of cluster related waits in comparison to other high level time categories in the following query:
SQL> SELECT wait_class time_cat ,ROUND ( (time_secs), 2) time_secs,
2 ROUND ( (time_secs) * 100 / SUM (time_secs) OVER (), 2) pct
3 FROM (SELECT wait_class wait_class,
4 sum(time_waited_micro) / 1000000 time_secs
5 FROM gv$system_event
6 WHERE wait_class <> 'Idle'
7 AND time_waited > 0
8 GROUP BY wait_class
9 UNION
10 SELECT 'CPU',
11 ROUND ((SUM(VALUE) / 1000000), 2) time_secs
12 FROM gv$sys_time_model
13 WHERE stat_name IN ('background cpu time', 'DB CPU'))
14 ORDER BY time_secs DESC;
Time category TIME_SECS PCT
-