Oracle-L: Re: tkprof output

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 12 Mar 2003 08:54:48 -0800
Message-ID: <F001.00567AE0.20030312085448@fatcity.com>

The examples you have given are SYS-recursive,

The call to cdef$ is Oracle looking for some information about constraints (one possibility is that you keep breaking a PK or UK constraint and Oracle has to keep looking up the name of the constraint because it doesn't cache constraint names).

The call to seq$ usually appears because you are using a sequence with a very small, or no, CACHE set - so as you get the next value from the sequence, oracle has to bump the sequence high-water value and write it to disc.

In general, the recursive depth simply tells you how far down the stack of calls your cursor is. For example (which may be wrong in detail, but right in gist) if you execute an anonymous pl/sql which runs an SQL statement that uses a sequence.nextval that happens to bump the sequence high-water, you would (probably see):

anonymous pl/sql            dep = 0
SQL statement                dep = 1

update seq$ statement dep = 3

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: 12 March 2003 16:15

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

-- 
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 Wed Mar 12 2003 - 10:54:48 CST