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

In a four-node cluster (Figure 13-12 ), when the query is executed in instance 1 ( SSKY1 ), as illustrated in the
10046 9 trace output, the query performs in index fast full scan of the two database tables ORDERS and ORDER_LINE . This
operation reads 345,361 blocks of data from disk (physical I/O) and performs another 345,447 logical I/O operations.
The final result set is 300 rows that are then sent back to the user:
SELECT ol_w_id,
ol_d_id,
ol_number,
SUM(ol_amount),
SUM(ol_quantity)
FROM order_line ol,
orders ord
WHERE ol.ol_o_id = ord.o_id
AND ol.ol_w_id = ord.o_w_id
AND ol.ol_d_id = ord.o_d_id
GROUP BY ol_number,
ol_w_id,
ol_d_id
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- -------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 21 18.42 77.41 345361 345447 0 300
------- ------ -------- ---------- ---------- ---------- ---------- -------
total 23 18.43 77.42 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=149 us cost=126413 size=4950 card=150)
21349787 HASH JOIN (cr=345447 pr=345361 pw=0 time=59061480 us cost=125703 size=680920944
card=20633968)
2134685 INDEX FAST FULL SCAN ORDERS_I2 (cr=11248 pr=11219 pw=0 time=258291 us cost=2743
size=22694870 card=2063170)(object id 86234)
21349787 INDEX FAST FULL SCAN IORDL (cr=334199 pr=334142 pw=0 time=47799580 us cost=87415
size=453947296 card=20633968)(object id 86202)
9 10046 trace can be enabled using alter session set events "10046 trace name context forever, level 12" and can be
disabled using alter session set events "10046 trace name context off . " The trace output will be generated in the
location defined by the parameter user_dump_dest .