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

SQL Trace is a simple, straightforward approach to getting to the execution plan generated by Oracle for the
various SQL queries. The information provided by these trace files is only minimal. While SQL_TRACE is a good start
to getting to the basic execution behavior, it seldom provides any details on the actual reasons for the performance
characteristics or the reasons why the optimizer showed a specific behavior during query execution.
Oracle provides certain events that can be enabled at the system or session level to capture information for all
sessions or at the session level.
Event 10046
Enabling event 10046 at various levels provides varying details about the execution plans, performance
characteristics, and other related information.
Level 1
This is enabled at the system level using
ALTER SYSTEM SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 1';
or at the session level using
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 1';
Setting event 10046 at level 1 is equivalent to setting the SQL_TRACE parameter to TRUE in the init<SID>.ora file
or turning on trace at a session level. The output produced by this event is generated in the user dump destination
directory. The trace file can be interpreted in a more readable format using the TKPROF utility discussed earlier in the
“SQL Trace” section.
The following is the output generated by using event 10046 at level 1 on the SQL query used earlier:
call count cpu elapsed disk query current rows
------- ------ ------ ---------- -------- -------- ---------- -------
Parse 1 0.06 0.15 14 170 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 21 80.81 137.25 455687 455783 0 300
------- ------ ------ ---------- -------- -------- ---------- -------
total 23 80.88 137.41 455701 455953 0 300
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 99 (RAPTEST)
Rows Row Source Operation
-------- ---------------------------------------------------
300 HASH GROUP BY (cr=455783 pr=455687 pw=0 time=299 us cost=259483 size=4950 card=150)
44846573 HASH JOIN (cr=455783 pr=455687 pw=0 time=131297736 us cost=257957 size=1405564479
card=42592863)
4484793 INDEX FAST FULL SCAN ORDERS_I2 (cr=15100 pr=15080 pw=0 time=1345898 us cost=5458
size=47299967 card=4299997)(object id 201148)
44846573 INDEX FAST FULL SCAN IORDL (cr=440683 pr=440607 pw=0 time=81889496 us cost=178429
size=945961456 card=42998248)(object id 201090)