No Explain Plans from TKPROF

Hi,

I am just learning how to use traces and TKPROF. My problem is that it
is not giving me the explain plans (with row counts and timings) for my
SQL. Sometimes I will get a few explain plans but typically I get
none. When I do get explain plans sometime they will have timing
statistics as in "(cr=22631 r=17 w=0 time=212289 us)" and sometimes
they won't.

I am running my tests from a fresh session on sqlplus and I think I am
exiting gracefully. My Oracle versions are 9204 and 9206. I use the
following test script:

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'alan' ;
ALTER SESSION SET timed_statistics = true ;
ALTER SESSION SET max_dump_file_size = unlimited ;
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12' ;

<<< sql stuff goes here >>>

ALTER SESSION SET EVENTS '10046 trace name context off' ;

DISCONNECT ;

Any ideas ???

Thanks,

Alan
EXIT ;

You need to make sure that all cursors are closed before turning off
tracing. STAT lines, which contain the execution plan are only emitted
when the cursor is closed. If you are using sql*plus, exiting without
turning off tracing will cause the cursors to be closed. Truning off
tracing will not automatically close any of the cursors.

If you don't always see timing statistics, is it possible the query is
parallelized? If so, the information will not be written to the user
process.

Hi Daniel,

Thanks for the reply!!!

So are you suggesting that, after my SQL runs that I simply exit
sqlplus to close the cursors WITHOUT turning off tracing first?

Also - as a side question - are you supposed to get Explain Plans for
UPDATE statements? I have an update with a subquery and I never see
the plan.

Thanks in advance,

Alan

Alan,

Why not just gracefully close the sql*plus session? It will close all
cursors (even those that are associated with recursive sql) and tracing
will be turned off as there is nothing to trace if the session does not
exist anymore.

I just ran a test on 9206 on WinXP and I get execution plans for my
UPDATEs, with or without predicates or subqueries. You might be hitting
a bug. I know that earlier versions (9.2.0.3 and earlier including 8i)
had a bug where STAT lines were not emitted for some queries (I found
it in queries executed as part of logon triggers). You might be hitting
a bug.

Dan

Hi Dan,

I think I've figured it out (with your kind help) -

My original test script turned off tracing, disconnected and then did
an exit from sqlplus. When I took out the line that turned off tracing
everything appeared in the tkprof file as expected. As you suggested,
I must have been turning off tracing before all the cursors got closed.

Thanks again!!!!

Alan