How to get explain plan output with rows in trace files
- alter session set sql_trace=true
- Run the sql(s)
- disconnect
Note step 3, disconnect, not turn trace off.
Read more here:
Hi Tom, sometimes when I run the TKPROF (ALTER SESSION SET SQL_TRACE=TRUE), the explain plan don’t appear in the output file. Why this can happen?
Thanks.
and we said…
The explain plan should never appear in the tkprof. In order to get the explain plan, you would have to use explain=u/p but you don’t want to do that.
http://tkyte.blogspot.com/2007/04/when-explanation-doesn-sound-quite.htmlYou want the row source operation, and that will be in the tkprof when you CLOSE the cursor (before ending trace)
eg: the most assured way - turn on trace, do stuff and DISCONNECT. That way we know the cursor is closed (not cached by pro*c, plsql, jdbc statement caching, whatever) and the row source operation will be there.Reviews
April 30, 2007 - 12pm US/Eastern
Reviewer: A reader
You mean that I should do this… ??
SQL> ALTER SESSION SET SQL_TRACE = TRUE ;
< .. do stuff .. >
SQL> DISCONNECT
Ask Tom “tkprof and explain plan”
Did you enjoy this post? Why not leave a comment below and continue the conversation, or subscribe to my feed and get articles like this delivered automatically to your feed reader.
Comments
No comments yet.
Leave a comment