Re: tkprof output

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