If you've got an interest in Oracle performance tuning, you'll probably have seen references to something called event 10046. Event 10046 is an undocumented, but well known feature within the Oracle database that generates detailed information on how a statement is parsed, what the values of bind variables are, and what wait events occurred during a particular session. Activating Event 10046 gives us a more detailed trace that is provided by alter session set sql_trace=true and lets the DBA determine exactly why the database is performing in a certain way, by documenting how a statement is parsed and noting what wait events occured during the statement execution.
Wait events are the things that stop your SQL statement being processed quickly, and are typically caused by statements being hard parsed, data having to be retrieved or written to disks which are suffering from contention, and other such events which in theory can be minimised in a well designed and tuned system. You've probably come across the top five wait events section in STATSPACK which gives you an idea which parts of your system are causing bottlenecks, and activating extended trace using event 10046 allows us to analyze each individual wait event, in the context of the set of SQL statements executed in a particular session.
Activating event 10046 is actually quite straightforward for your current session, and takes the form of alter session set events '10046 trace name context forever, level num';
Where num is either 1,4,8 or 12, with 1 being the same as a normal set sql_trace=true, 4 including values of bind variables,12 including wait events, and 12 including both bind variables and wait events.
ByteTracer allows you to point to the raw trace file produced by setting this event at the session level and locate the TOP SQL and TOP events the session waited for in 2 clicks! |
