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" <[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]> > 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: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
