Forums | Aggregator | Books | Industry News | Firefox Plugins | Social | Links

Comments

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 :)

Cheers

Tim...

hamdew said...


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

Tim... said...

Hi.

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.

Cheers

Tim...

Aseem said...

Great Stuff...!
Thanks

Robin Moffatt said...

A useful link is to Cary Millsap's paper, "Mastering Performance with Extended SQL Trace":
http://method-r.com/downloads/doc_details/72-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.

Tim... said...

Hi.

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.

Cheers

Tim...

Tim... said...

Hi.

Done a full answer to your question in the forum:

http://www.oracle-base.com/forums/viewtopic.php?f=1&t=12737&p=32890

Cheers

Tim...

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?

Tim... said...

Hi.

Can;t say I've noticed that, but I almost exclusively use DBMS_MONITOR these days.

Cheers

Tim...
Prepared Statement Error: Table './oraclebasecms/cms_page_comment_uuids' is marked as crashed and should be repaired