Oracle-L: Re: tkprof output

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 11 Mar 2003 14:54:31 -0800
Message-ID: <F001.0056704C.20030311145431@fatcity.com>

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

Received on Tue Mar 11 2003 - 16:54:31 CST