Re: Trace file question

From: David Fitzjarrell <oratune_at_aol.com>
Date: Mon, 05 Feb 2001 21:42:52 GMT
Message-ID: <95n6oq$jf0$1@nnrp1.deja.com>

In article <frCf6.9303$Zp3.776812_at_e3500-chi1.usenetserver.com>, zifnab_at_NOSPAM.reddragon.org (Scott Gamble) wrote:
> Have a third party package that our developers are getting ready to
> upgrade, and as part of that procedure, we require them to allow us
to do
> sql traces prior to it going live in production.
>
> Since we upgraded to 8.0.6.X last October from time to time I have
seen
> trace files that come back with 0 recurrsive calls.
>
> This application in particular has a horrible problem with a lack of
bind
> variables and poor coding and thus parses all of its statements
repeatedly.
>
> Below is a portion of the trace that I did this morning...
>
> I always thought that a 'library cache miss' would require parsing,
so how
> are we still getting 0 recursive calls?
>
> My first thought was that I must of screwed up, but going back and
looking
> at the raw trace file there are absolutely no recursive calls in it
either.
>
>

***
> *****
>
> OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
>
> call count cpu elapsed disk query
current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------

> -----
> Parse 7971 16.51 15.55 0 0
0
> 0
> Execute 7971 11.66 12.06 0 179007
38940
> 24066
> Fetch 4531 42.45 43.81 3106 1095688
2736
> 247781
> ------- ------ -------- ---------- ---------- ---------- ----------

> -----
> total 20473 70.62 71.42 3106 1274695
41676
> 271847
>
> Misses in library cache during parse: 3446
>
> OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
>
> call count cpu elapsed disk query
current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------

> -----
> Parse 0 0.00 0.00 0 0
0
> 0
> Execute 0 0.00 0.00 0 0
0
> 0
> Fetch 0 0.00 0.00 0 0
0
> 0
> ------- ------ -------- ---------- ---------- ---------- ----------

> -----
> total 0 0.00 0.00 0 0
0
> 0
>
>

Sometimes, in order to execute a SQL statement issued by a user, Oracle must issue additional statements. Such statements are called recursive calls or recursive SQL statements. For example, if you insert a row into a table that does not have enough space to hold that row, then Oracle makes recursive calls to allocate the space dynamically. Recursive calls are also generated when data dictionary information is not available in the data dictionary cache and must be retrieved from disk; library cache misses will not generate recursive calls.

Recursive SQL statistics are not included for SQL-level operations. However, recursive SQL statistics are included for operations done below the SQL level, such as triggers.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com
http://www.deja.com/

Received on Mon Feb 05 2001 - 15:42:52 CST