|SQL trace, 10046, trcsess and tkprof in Oracle - An article that combines all previous SQL Trace, event 10046 and tkprof information, along with information on trcsess and DBMS_MONITOR from Oracle 10g onward.|
PRAVIN BALDWA said...Just a minor error.
It should be DBMS_SUPPORT.START_TRACE_IN_SESSION on second line. please check
Tim... said...You're correct. I've corrected it now. A dodgy cut and page job :)
T Smith said...Nice ! Very helpful, thank-you.
Peter said...Great post! Thank you.
eko said...I do sql trace event 10046 level 8 or 12. But the results did not same, execution plan does not appear
The execution plan is generated by TKPROF, it is not in the trace file.
To see the execution plan you must have the "explain=username/password" parameter defined in your TKPROF statement. This must be set to the relevant user/password who can run the explain plan statement.
Aseem said...Great Stuff...!
Robin Moffatt said...A useful link is to Cary Millsap's paper, "Mastering Performance with Extended SQL Trace":
Rakesh said...Hi Tim,
For a 10046,level 12 trace of a vendor provided insert statement i didnt see commit after the insert statement. Will commits are traced in 10046 trace events. I cant paste the output coz of limited space.
The reason I ask people to ask questions in the forum is because there is no space limit and you can format your text. :)
To answer your question. Yes. Commit is included in the trace.
Done a full answer to your question in the forum:
Ferga Taheny said...Looking at an application here, it generated over 2000 trace files. So I'm stiching them together with trcsess and I get
Exception in thread "main" java.lang.OutOfMemoryError
To fix this edit the trcsess script and change the java call to:
java -Xms128M -Xmx2048M
tws said...FYI: SQL Developer apparently tries to load a trace file entirely in memory, so large trc files will error out with a java heap memory error
Lauri said...Very good and useful summary!!
I got not trace file generated using dbms_system when working with Oracle11gR2.
Does someone get that result also?
Can;t say I've noticed that, but I almost exclusively use DBMS_MONITOR these days.