Optimize Distributed Workload - Expert Oracle RAC Performance Diagnostics and Tuning - page 527

The preceding query revealed several SQL_IDs used commonly across many modules and actions. Further
investigation into wait events and row level locking issues associated to the various SQL operations indicated potential
issues with the SQL statement with SQL_ID 'dhp8kqqkag9tq' .
SELECT inst_id INT,
event,
p1,
p2,
wait_class
FROM gv$active_session_history
WHERE sql_id = 'dhp8kqqkag9tq'
AND event IS NOT NULL
ORDER BY p2,
p1;
INT EVENT P1 P2 WAIT_CLASS
---- ------------------------------ -------- -------- -------------
1 latch: cache buffers chains 5.42E+10 155 Concurrency
2 latch: cache buffers chains 5.42E+10 155 Concurrency
latch: cache buffers chains 5.42E+10 155 Concurrency
latch: cache buffers chains 5.46E+10 155 Concurrency
latch: row cache objects 5.43E+10 279 Concurrency
latch: row cache objects 5.43E+10 279 Concurrency
gc cr block 2-way 150 6324 Cluster
gc buffer busy acquire 149 7164 Cluster
gc cr block 3-way 149 7164 Cluster
gc buffer busy acquire 149 7164 Cluster
gc buffer busy acquire 140 10096 Cluster
gc buffer busy acquire 151 11419 Cluster
gc buffer busy acquire 138 12617 Cluster
gc current block 3-way 143 18008 Cluster
gc buffer busy acquire 160 18808 Cluster
gc cr block 2-way 159 20536 Cluster
Step 7
The cluster-related statistics following also indicate that the query was executed across multiple instances in the cluster:
Script: MVRACPDnTap_sqlstats.sql
SELECT inst_id INT,
sql_id,
application_wait_time awt,
concurrency_wait_time conwt,
cluster_wait_time clwt,
user_io_wait_time uiwt
FROM gv$sqlstats
WHERE cluster_wait_time > 10000
ORDER BY inst_id,
user_io_wait_time desc;