SQL Tuning - Expert Oracle RAC Performance Diagnostics and Tuning - page 206

TIMED_OS_STATISTICS
The TIMED_STATISTICS parameter discussed earlier will enable gathering of statistics at the database and instance
level. The TIMED_OS_STATISTICS parameter will collect O/S-level statistics and is normally driven by the value of the
STATISTICS_LEVEL parameter. When the STATISTICS_LEVEL parameter is set to ALL , TIMED_OS_STATISTICS is set to a
value of 5, meaning that the servers should collect O/S statistics every 5 seconds.
The 10046 trace from the query following indicates the time metrics that were possible because the TIMED_
STATISTICS parameter was enabled:
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.01 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 21 33.04 96.12 346671 347172 0 300
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 23 33.06 96.14 346671 347172 0 300
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 89 (OBIPRD)
Rows Row Source Operation
------- ---------------------------------------------------
300 HASH GROUP BY (cr=347172 pr=346671 pw=0 time=299 us cost=126413 size=4950 card=150)
21541174 HASH JOIN (cr=347172 pr=346671 pw=0 time=100269208 us cost=125703 size=680920944
card=20633968)
2153881 INDEX FAST FULL SCAN ORDERS_I2 (cr=12056 pr=11635 pw=0 time=629729 us cost=2743
size=22694870 card=2063170)(object id 86234)
21541174 INDEX FAST FULL SCAN IORDL (cr=335116 pr=335036 pw=0 time=79083112 us cost=87415
size=453947296 card=20633968)(object id 86202)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ----------
ges message buffer allocation 466 0.00 0.00
library cache lock 2 0.00 0.00
row cache lock 14 0.00 0.00