OptimizingOracleRAC(四)

As a rule of thumb, we might expect that cluster-related waits comprise less than 10% of total database time. Waits above 20% certainly warrant investigation.

Cluster waits times greater than 10-20% of total database time probably warrant investigation.

Although cluster waits will usually be comprised mainly of straightforward Global Cache request waits, it’s not uncommon for more “sinister” Global Cache waits to emerge: lost blocks, congestion, Global Cache buffer busy waits. Drilling down into the low level wait events will often reveal these conditions. The following query breaks out the cluster wait times:

SQL> WITH system_event AS
2 (SELECT CASE
3 WHEN wait_class = 'Cluster' THEN event
4 ELSE wait_class
5 END wait_type, e.*
6 FROM gv$system_event e)
7 SELECT wait_type, ROUND(total_waits/1000,2) waits_1000 ,
8 ROUND(time_waited_micro/1000000/3600,2) time_waited_hours,
9 ROUND(time_waited_micro/1000/total_waits,2) avg_wait_ms ,
10 ROUND(time_waited_micro*100
11 /SUM(time_waited_micro) OVER(),2) pct_time
12 FROM (SELECT wait_type, SUM(total_waits) total_waits,
13 SUM(time_waited_micro) time_waited_micro
14 FROM system_event e
15 GROUP BY wait_type
16 UNION
17 SELECT 'CPU', NULL, SUM(VALUE)
18 FROM gv$sys_time_model
19 WHERE stat_name IN ('background cpu time', 'DB CPU'))
20 WHERE wait_type <> 'Idle'
21 ORDER BY time_waited_micro DESC;

Waits Time Avg Wait Pct of
Wait Type \1000 Hours Ms Time
----------------------------------- ----------- ---------- --------- ------
CPU 6.15 43.62
Other 38,291 1.76 .17 12.50
Application 32 1.41 157.35 10.00
User I/O 822 .97 4.25 6.88
System I/O 995 .96 3.46 6.78
gc current multi block request 9,709 .87 .32 6.15
gc cr multi block request 16,210 .48 .11 3.37
Commit 300 .44 5.31 3.13
gc current block 2-way 5,046 .37 .26 2.59
gc current block 3-way 2,294 .28 .43 1.97
gc cr block busy 984 .16 .58 1.11

Cluster waits summary

Here are descriptions for some of the more important Global Cache wait events:

gc cr/current block 2-way: These are waits for Global Cache block requests involving only 2 instances. As outlined at the beginning of this article, these occur when the block master instance is able to forward a block directly to the requesting instance

gc cr/current block 3-way: These waits occur when the block master does not have the block concerned, and forwards the request to a third instance.

gc cr/current multi block request: A wait that occurs when requesting multiple blocks in a single request. This is typically associated with full table or index scans.

gc cr/current grant 2-way: The block master informs the requesting instance that the requested block is not available from another instance. The requesting instance will then perform a disk IO to retrieve the block.

gc cr/current block busy: The requesting instance must wait for the instance that holds the block to complete some other operation before the block can be forwarded. This can happen in the same circumstances as for single instance buffer busy or because the requesting instance must flush redo records to the redo log before shipping a consistent copy.

gc cr/current block congested: This wait can be reported when CPU or memory pressure prevents the LMS process from keeping up with requests. Prior to Oracle 10.2, you co