Tuning Global Cache - Expert Oracle RAC Performance Diagnostics and Tuning - page 418

If this query was executed on instance 2 ( SSKY2 ), the complete operation is performed all over again (the execution
plan looks identical), including the physical I/O and the logical operations before getting the full results of 300 rows:

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- -------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 21 31.74 74.34 345361 345447 0 300
------- ------ -------- ---------- ---------- ---------- ---------- -------
total 23 31.76 74.36 345361 345447 0 300

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 89 (schema name)

Rows Row Source Operation
------- ---------------------------------------------------
300 HASH GROUP BY (cr=345447 pr=345361 pw=0 time=299 us cost=126413 size=4950 card=150)
21349787 HASH JOIN (cr=345447 pr=345361 pw=0 time=62985040 us cost=125703 size=680920944
card=20633968)
2134685 INDEX FAST FULL SCAN ORDERS_I2 (cr=11248 pr=11219 pw=0 time=490345 us cost=2743
size=22694870 card=2063170)(object id 86234)
21349787 INDEX FAST FULL SCAN IORDL (cr=334199 pr=334142 pw=0 time=42913972 us cost=87415
size=453947296 card=20633968)(object id 86202)

Analyzing the trace outputs from the two instances and the following listed wait events, we notice that there was
no cache synchronization of data. As discussed earlier, this is a new observation with Oracle Database 11g Release 2
RAC (unlike in Oracle 10g) when Oracle finds it more efficient that a local I/O operation would be more beneficial to
avoid transferring data across the interconnect.

Instance 1 (SSKY1) Instance 2 (SSKY2)

Event waited on Times Event waited on Times

---------------------------- Waited --------------------------- Waited

row cache lock 14 row cache lock 18

SQL*Net message to client 21 SQL*Net message to client 21

db file sequential read 6 db file sequential read 6

db file parallel read 1 db file parallel read 96

db file scattered read 2762 db file scattered read 2667

gc current grant busy 206

gc cr block 2-way 50 gc cr block 2-way 21

SQL*Net message from client 21 SQL*Net message from client 21

gc cr multi block request 112

gc cr block 3-way 22