Re: tkprof output

also how can we check if one particular session is being traced (10046 ) or not . Basically I want to be sure that tracing is stopped for the session and its not fillling up disk space .

-ak

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, March 12, 2003 9:07 AM

> An action with dep=n+1 (n>=0) for cursor #k is the recursive child of the
> next dep=n action for cursor #k that immediately follows in the trace
data.
>
>
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
>
> Upcoming events:
> - Hotsos Clinic 101, Mar 25-27 Oxford
> - Hotsos Clinic 101, Apr 8-10 Chicago
>
>
> -----Original Message-----
> Sent: Wednesday, March 12, 2003 10:16 AM
> To: Multiple recipients of list ORACLE-L
>
> Thanks Jonathan,
> what is meaning of recursive depth ? I see calls to cdef$, seq$
> tables/views does it hint something . I though procedure is using some
> sequence and these are internal calls to generate seq numbers . Is that
rite
> ?
>
> -ak
>
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Tuesday, March 11, 2003 2:54 PM
>
>
> >
> > Any SQL within a pl/sql block is recursive SQL
> > (user recursive, rather than SYS recursive) so
> > this time could simply be the cost of running
> > your application code.
> >
> > Unfortunately the tkprof output doesn't quote
> > the recursive depth of the SQL - however if
> > you identify possible suspects, you can check
> > back in the raw trace file for lines like:
> > PARSING IN CURSOR #N
> > there will be a bit in the line like
> > dep=n
> > If n is not zero, then this is a 'recursive'
> > cursor.
> >
> > Regards
> >
> > Jonathan Lewis
> > http://www.jlcomp.demon.co.uk
> >
> > Now available One-day tutorials:
> > Cost Based Optimisation
> > Trouble-shooting and Tuning
> > Indexing Strategies
> >
> > (see http://www.jlcomp.demon.co.uk/tutorial.html )
> >
> > ____UK_______March 19th
> > ____UK_______April 8th
> > ____UK_______April 22nd
> >
> > ____USA_(FL)_May 2nd
> >
> >
> > Next dates for the 3-day seminar:
> > (see http://www.jlcomp.demon.co.uk/seminar.html )
> >
> > ____UK_(Manchester)_May
> > ____USA_(CA, TX)_August
> >
> >
> > The Co-operative Oracle Users' FAQ
> > http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> >
> >
> > ----- Original Message -----
> > To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> > Sent: 11 March 2003 19:29
> >
> >
> > > I am running tkprof on event 10046 output and I see at the end .
> > elapsed
> > > time of 18 secs in recursive calls thats what surprises me . Do you
> > know
> > > what I should look at next and what can be done to reduce these
> > timings
> > >
> > > Thanks,
> > > -ak
> > >
> > >
> > >
> > >
> > > OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
> > >
> > > call count cpu elapsed disk query current
> > > rows
> > > ------- ------ -------- ---------- ---------- ---------- ----------
> > ------
> > > ----
> > > Parse 26 0.08 0.10 3 26 0
> > > 0
> > > Execute 37 0.04 0.08 0 4 6
> > > 2
> > > Fetch 32 1.13 2.14 5079 5189 70
> > > 30
> > > ------- ------ -------- ---------- ---------- ---------- ----------
> > ------
> > > ----
> > > total 95 1.25 2.32 5082 5219 76
> > > 32
> > >
> > > Misses in library cache during parse: 23
> > >
> > >
> > > OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
> > >
> > > call count cpu elapsed disk query current
> > > rows
> > > ------- ------ -------- ---------- ---------- ---------- ----------
> > ------
> > > ----
> > > Parse 41 0.03 0.05 0 0 0
> > > 0
> > > Execute 541 4.40 12.30 1051 6442 63782
> > > 38712
> > > Fetch 550 3.23 6.31 3977 24298 340
> > > 419
> > > ------- ------ -------- ---------- ---------- ---------- ----------
> > ------
> > > ----
> > > total 1132 7.66 18.66 5028 30740 64122
> > > 39131
> > >
> > > Misses in library cache during parse: 9
> > >
> > > 164 user SQL statements in session.
> > > 41 internal SQL statements in session.
> > > 205 SQL statements in session.
> > > 0 statements EXPLAINed in this session.
> > >
> > **********************************************************************
> > ******
> > > ****
> > > Trace file: ora_28633_ak.trc
> > > Trace file compatibility: 8.00.04
> > > Sort options: default
> > >
> > > 1 session in tracefile.
> > > 164 user SQL statements in trace file.
> > > 41 internal SQL statements in trace file.
> > > 205 SQL statements in trace file.
> > > 167 unique SQL statements in trace file.
> > > 5369 lines in trace file.
> > >
> > >
> > >
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > --
> > > Author: AK
> > > INET: oramagic_at_hotmail.com
> > >
> > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > > San Diego, California -- Mailing list and web hosting
> > services
> > > --------------------------------------------------------------------
> > -
> > > To REMOVE yourself from this mailing list, send an E-Mail message
> > > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > (or the name of mailing list you want to be removed from). You may
> > > also send the HELP command for other information (like subscribing).
> > >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Jonathan Lewis
> > INET: jonathan_at_jlcomp.demon.co.uk
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> >
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: AK
> INET: oramagic_at_hotmail.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Cary Millsap
> INET: cary.millsap_at_hotsos.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: AK
  INET: oramagic_at_hotmail.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).