4 SUM(CASE WHEN stat_name IN ('DB CPU', 'background cpu time')
5 THEN VALUE END) cpu_time
6 FROM gv$sys_time_model
7 GROUP BY inst_id )
8 SELECT instance_name,
9 ROUND(db_time/1000000,2) db_time_secs,
10 ROUND(db_time*100/SUM(db_time) over(),2) db_time_pct,
11 ROUND(cpu_time/1000000,2) cpu_time_secs,
12 ROUND(cpu_time*100/SUM(cpu_time) over(),2) cpu_time_pct
13 FROM sys_time
14 JOIN gv$instance USING (inst_id);
Instance DB Time Pct of CPU Time Pct of
Name (secs) DB Time (secs) CPU Time
-------- ------------- ------- ------------- --------
MELRAC3 3,705.30 24.48 1,119.99 17.03
MELRAC2 6,278.23 41.48 4,010.85 61.00
MELRAC1 5,150.96 34.03 1,444.06 21.96
In this example it is clear that MELRAC2 is being subjected to a disproportionate level of CPU load: if this is not addressed, increasing cluster workload will almost certainly lead to performance degradation as MELRAC2 becomes the bottleneck for the entire cluster.
The above query summarizes performance since the instances in the cluster were started. Of course, instances in a cluster can start and stop independent of the cluster as a whole, which might result in different totals even if the cluster is experiencing a balanced workload.
Quest Software’s Spotlight on RAC probably has the most advanced RAC balance monitoring. Spotlight on RAC displays cluster balance from a number of perspectives and performs a statistical analysis to determine if the imbalance is systematic or due to short term random fluctuations. Figure 4 shows example output from Spotlight on RAC.
Figure 4 RAC balance display in Quest's Spotlight on RAC
An imbalance in RAC load can be due to a single session – or just a few sessions – placing heavy load on specific instances. These imbalances might be unavoidable although parallelizing operations across the cluster might be an option. Other possible causes include:
Balancing load across the instances in the cluster is essential for optimal RAC performance. In particular, ensure that no single instance becomes overloaded. An overloaded instance can lead to high Global Cache waits, inconsistent service times and poor scalability.
Cluster balance and services
Services allow you to allocate workloads to specific instances within a cluster. Instances within the cluster can be associated with specific services and connection requests for those services will then be directed to those instances. Each service will have one or more preferred instances and optionally one or more available instances. Requests for a service will be directed to preferred instances initially; if none of the preferred instances are available then the request will be sent to an available instance.
After installation, each RAC database will have a few default services: one for each instance, one for the cluster as a whole, and special or internal services (SYS$BACKGROUND, SYS$USERS, the XDB service, etc).
Additional services can be configured and managed by the DBMS_SERVICES package, the srvctl co