Re: Why no row source plans in tkprof output? -- More info please read...

From the sound of it, you're tracing a user's session from another session. As long as that session is still running, the cursors do not necessarily have to be closed just because the transaction is completed (either by rollback or commit, doesn't matter). The only way to be really sure that all the stat lines (row source operations) get dumped in the tracefile is to end the session before disabling the trace.

Also, tkprof isn't perfect. That's why some people write their own profilers, or look at raw trace files when in doubt.

If the STAT lines aren't there for the SQL you're interested in, the cursor just hasn't been closed yet (and in fact, in a well designed application, it may never be closed until the application shuts down.)

Stefan

On 4/24/07, Binh Pham <binhpham15_at_hotmail.com> wrote:
>
> I do see stats written for some of the SQL's in the raw trace file,
> however, there is no row source plans for any of the SQL's in the tkprof
> output file. There are many SQL's in this trace file and most of them if
> not all were completed. The trace was turned off when the user transaction
> completed.
>
> Can you explain that?
>
> Also, the dump file size is unlimited.
>
> Thanks for any additional input.
>
> ------------------------------
> From: *"Stefan Knecht" <knecht.stefan_at_gmail.com>*
> Reply-To: *knecht.stefan_at_gmail.com*
> To: *binhpham15_at_hotmail.com*
> CC: *oracle-l <oracle-l_at_freelists.org>*
> Subject: *Re: Why no row source plans in tkprof output?*
> Date: *Tue, 24 Apr 2007 08:19:00 +0200*
>
> The STAT lines (row source operations) only get dumped in the tracefile
> when the cursor gets closed. If, for example, you're tracing a running
> session for a short period of time, you may not get any plans in the
> tracefile (and hence in tkprof).
>
>
> If you're tracing your own session, as alexander suggested, quitting your
> session cleanly will help.
>
> Stefan
>
> On 4/24/07, Binh Pham <
> binhpham15_at_hotmail.com> wrote:
> >
> > Sometimes I have these "row source" plans (not the regular explain
> > plans created by the explain=user/password parm) in my tkprof output,
> > sometimes I do not see them. I'm on
> > 9.2.0.6 (HPUX).
> >
> > Thanks for any explanation.
> >
> > ------------------------------
> > Download Messenger. Join the i'm Initiative. Help make a difference
> > today.
> > <http://g.msn.com/8HMAENUS/2740??PS=47575>
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
>
>
> --
> =========================
>
> Stefan P Knecht
> Consultant
> Infrastructure Managed Services
>
> Trivadis AG
> Europa-Strasse 5
> CH-8152 Glattbrugg
>
> Phone +41-44-808 70 20
>
> Fax +41-808 70 12
> Mobile +41-79-571 36 27
> stefan.knecht_at_trivadis.com
> http://www.trivadis.com
>
> =========================
>
>
> ------------------------------
> Mortgage refinance is Hot. *Terms. Get a 5.375%* fix rate. Check savings<http://g.msn.com/8HMAENUS/2746??PS=47575>

-- 
=========================

Stefan P Knecht
Consultant
Infrastructure Managed Services

Trivadis AG
Europa-Strasse 5
CH-8152 Glattbrugg

Phone +41-44-808 70 20
Fax +41-808 70 12
Mobile +41-79-571 36 27
stefan.knecht_at_trivadis.com
http://www.trivadis.com

=========================

--
http://www.freelists.org/webpage/oracle-l