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