Tuning the Database - Expert Oracle RAC Performance Diagnostics and Tuning - page 287

Step 6
What kind of waits did the query encounter that caused such high wait times? This information can be obtained either
from GV$SESSION or GV$SESSION_WAIT views for any currently active sessions. However, to get the wait times for the
operation over a period of time, you check against the GV$ACTIVE_SESSION_HISTORY view.

Inst Event Count Total Time
Waited
---- ------------------------------------ ---------- ----------------
4 gc cr block 2-way 6705 130709999
4 gc buffer busy acquire 977 95153965
5 gc current grant busy 1553 94741345
5 gc cr grant 2-way 3878 77598705
4 gc current grant busy 5388 73536511
5 gc buffer busy acquire 897 65681455
5 gc cr block 2-way 5466 63163740
5 gc current block 3-way 4551 59585985
5 gc current block 2-way 5655 49691644
4 gc current block busy 1433 43599583
4 gc buffer busy release 588 38576658
4 gc cr grant 2-way 5098 33905067
5 gc current grant 2-way 959 59979554
4 gc current grant 2-way 999 55886144
5 gc cr multi block request 655 19934496
5 gc current block busy 355 11648503
5 gc cr disk read 1474 11501349
4 gc cr multi block request 651 9410566
4 gc current multi block request 103 9097616
5 gc cr block busy 730 9095815
4 gc cr failure 97 7045456
4 gc cr block congested 55 5563650
5 gc current multi block request 34 3456179
5 gc buffer busy release 54 3005641
5 gc cr block congested 13 5835114

Snapshots of the number of times the waits have been encountered and the total time waited by the events, for
these data files indicate high inter-instance block activity. Concurrency related wait events (from the output of Step 5)
are normally related to enqueues.

Step 7
Now that this information is obtained, the next step is to determine how to reduce this cluster-related overhead. There
are several methods by which this could be addressed:

• Using database features such as partitioning. Is the current table/object already partitioned? If not,

can it be partitioned and access to the various sets of data distributed across instances in the cluster
since the idea of using a clustered environment is to distribute workload and use all the available
resources? However, not always partitioning can be implemented without doing a complete profile
of the application and understanding the access patterns of the queries. The execution plans and
access criteria should match the partition keys for the optimizer to access the data efficiently.

• Yet another option available could be to isolate the operations of this object to a separate

database service name and assign the service to only one instance in the cluster.