Tools and Utilities - Expert Oracle RAC Performance Diagnostics and Tuning - page 181

Level 8
Many times the query performs badly due to several reasons other than the way in which the query is written itself.
For example, slow performance could be due to the way the table has been stored on disk, poor distribution of data on
the disk, too many tables sharing the same data files, data heavily scattered or the query performing full table scans,
waiting for resources. SQL could be efficient but due to contention for resources could increase response time. Under
such circumstances, it is best to understand what type of resources the system is waiting on. This information can be
obtained using level 8.
Similar to level 1 and level 4, level 8 can be enabled at the system or session levels through the following
statements:
ALTER SYSTEM SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';
The following is the additional information generated by event 10046 at level 8:
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------- Waited ---------- ------------
ges message buffer allocation 25636 0.00 0.08
library cache lock 2 0.00 0.00
row cache lock 18 0.00 0.00
library cache pin 1 0.00 0.00
SQL*Net message to client 21 0.00 0.00
Disk file operations I/O 1 0.00 0.00
gc cr grant 2-way 2 0.00 0.00
KSV master wait 2 0.00 0.00
ASM file metadata operation 2 0.00 0.00
db file sequential read 4 0.01 0.02
gc cr multi block request 33403 0.00 1.73
db file scattered read 3796 0.12 56.25
asynch descriptor resize 2 0.00 0.00
db file parallel read 1 0.00 0.00
latch free 1 0.00 0.00
SQL*Net message from client 21 0.24 0.25
The above output lists the various wait events encountered during the query execution. Now from the above it is
certain that there are several waits that could have caused a performance slowdown for the query and query itself
may not be the problem. For example, the highest value in the Total Waited column is for a gc cr multi-block request .
Level 12
Level 12 provides a combined output of all the above levels. At this level, the execution plans, bind variables, bind
values (generated with level 4), and the wait events (generated with level 8) are generated in the trace files. Most often,
enabling event 10046 at level 12 will provide a comprehensive method of looking at the problem at hand. However, the
output generated at this level can be large and may require considerable amount of disk space. Enabling trace at this
level should be done only after ensuring sufficient space is available.
Similar to the other levels, level 12 can be enabled at the system or session levels through the following
statements:
ALTER SYSTEM SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';