Re: TKPROF question
- From: Mladen Gogala <mladen@xxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 23 Mar 2004 15:01:06 -0500
Raj, one stupid suggestion: go to V$SYSTEM_EVENT before and after running the
process and
see how much time do you have there. Waiting for a single block for almost an
hour should
be clearly visible. The numbers don't make much sense. Other then that, as far
as I can
remember, you have RAC. You may have encountered some bug that prevents you
from getting
a shared lock on a block for a long, long time. If you want to read a block
that someone
else is updating, you do need to obtain a shared lock on that block and the
BSP needs to
reconstruct the block and send it to you. You may have a problem along those
lines.
On 03/23/2004 01:49:29 PM, "Jamadagni, Rajendra" wrote:
> Mladen,
>
> I dumped it and it is an index leaf block, but 3288 seconds of wait for a
> single block in a process that finishes in 1244 seconds?? I need a break ...
>
> Raj
> --------------------------------------------------------------------------------
> Rajendra dot Jamadagni at nospamespn dot com
> All Views expressed in this email are strictly personal.
> select standard_disclaimer from company_requirements;
> QOTD: Any clod can have facts, having an opinion is an art !
>
>
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Mladen Gogala
> Sent: Tuesday, March 23, 2004 12:38 PM
> To: oracle-l@xxxxxxxxxxxxx
> Cc: Oracle List (E-mail)
> Subject: Re: TKPROF question
>
>
> Dump it. Instructions how to dump blocks are on Julian Dyke's site. It
> should be clearly visible
> whch data blocks are data blocks and which are not.
>
> On 03/23/2004 11:16:39 AM, "Jamadagni, Rajendra" wrote:
> > Never mind ... found it now I have to find out why the he11 it waited that
> > long on one block .... and this block is from one index. now how do I find
> > what type of block is this? branch? data?
> >
> > Why would there be a cr wait of 3288.24 (seconds?/cent-seconds?) if so, it
> > doesn't match with the summary ...
> >
> > OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
> >
> > call count cpu elapsed disk query current
> > rows
> > ------- ------ -------- ---------- ---------- ---------- ----------
> > ----------
> > Parse 6 0.02 0.01 0 0 0
> > 0
> > Execute 7 0.02 1244.27 503009 11062603 0
> > 4
> > Fetch 2 0.00 0.00 0 3 0
> > 1
> > ------- ------ -------- ---------- ---------- ---------- ----------
> > ----------
> > total 15 0.04 1244.29 503009 11062606 0
> > 5
> >
> > Misses in library cache during parse: 5
> > Misses in library cache during execute: 1
> >
> > Elapsed times include waiting on following events:
> > Event waited on Times Max. Wait Total
> > Waited
> > ---------------------------------------- Waited ----------
> > ------------
> > SQL*Net message to client 8 0.00
> > 0.00
> > SQL*Net message from client 8 0.00
> > 0.00
> > library cache lock 27 0.00
> > 0.00
> > row cache lock 1 0.00
> > 0.00
> > log file sync 1 0.00
> > 0.00
> >
> >
> > OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
> >
> > call count cpu elapsed disk query current
> > rows
> > ------- ------ -------- ---------- ---------- ---------- ----------
> > ----------
> > Parse 36 0.04 0.06 0 85 0
> > 0
> > Execute 84 243.62 1244.11 502996 11062248 41821
> > 20188
> > Fetch 91 0.02 0.14 17 423 0
> > 67
> > ------- ------ -------- ---------- ---------- ---------- ----------
> > ----------
> > total 211 243.68 1244.32 503013 11062756 41821
> > 20255
> >
> > Misses in library cache during parse: 24
> >
> > Elapsed times include waiting on following events:
> > Event waited on Times Max. Wait Total
> > Waited
> > ---------------------------------------- Waited ----------
> > ------------
> > library cache lock 37 0.00
> > 0.00
> > row cache lock 34 0.00
> > 0.01
> > db file sequential read 492883 0.16
> > 975.74
> > global cache cr request 239402 3288.24
> > 3368.37
> > library cache pin 1 0.00
> > 0.00
> > direct path write 1267 0.00
> > 0.07
> > global cache s to x 155 0.00
> > 0.04
> > direct path read 1582 0.01
> > 0.72
> > db file scattered read 45 0.07
> > 0.38
> > global cache open s 3 0.00
> > 0.00
> > global cache open x 45 0.00
> > 0.01
> >
> > 13 user SQL statements in session.
> > 30 internal SQL statements in session.
> > 43 SQL statements in session.
> > ********************************************************************************
> >
> > I am confused ... any help/ponters is greatly appreciated.
> >
> > Raj
> > --------------------------------------------------------------------------------
> > Rajendra dot Jamadagni at nospamespn dot com
> > All Views expressed in this email are strictly personal.
> > select standard_disclaimer from company_requirements;
> > QOTD: Any clod can have facts, having an opinion is an art !
> >
> > > -----Original Message-----
> > > From: Jamadagni, Rajendra
> > > Sent: Tuesday, March 23, 2004 10:55 AM
> > > To: Oracle List (E-mail)
> > > Subject: TKPROF question
> > >
> > > <some big bad MERGE statement here>
> > >
> > > call count cpu elapsed disk query current
> > > rows
> > > ------- ------ -------- ---------- ---------- ---------- ----------
> > > ----------
> > > Parse 1 0.01 0.01 0 5 0
> > > 0
> > > Execute 6 213.15 949.43 393775 10256309 20885
> > > 10094
> > > Fetch 0 0.00 0.00 0 0 0
> > > 0
> > > ------- ------ -------- ---------- ---------- ---------- ----------
> > > ----------
> > > total 7 213.16 949.45 393775 10256314 20885
> > > 10094
> > >
> > > Misses in library cache during parse: 1
> > > Optimizer goal: CHOOSE
> > > Parsing user id: 47 (recursive depth: 1)
> > >
> > > Elapsed times include waiting on following events:
> > > Event waited on Times Max. Wait Total
> > > Waited
> > > ---------------------------------------- Waited ----------
> > > ------------
> > > global cache cr request 191074 3288.24
> > > 3352.21
> > > db file sequential read 392778 0.16
> > > 717.89
> > > global cache s to x 88 0.00
> > > 0.02
> > > direct path write 11 0.00
> > > 0.01
> > > direct path read 128 0.00
> > > 0.03
> > > db file scattered read 45 0.07
> > > 0.38
> > > global cache open s 3 0.00
> > > 0.00
> > > global cache open x 45 0.00
> > > 0.01
> > > ********************************************************************************
> > >
> > >
> > > Okay ... so this is 9204 db, and I am trying to correctly interpret the
> > > MAX.WAIT column value for 'global cache cr request' ... I assume that max
> > > wait means in all waits this was the max value for given wait event.
> > > right? If so, Did I really have wait of 3288.24 (seconds, centi seconds,
> > > milliseconds???)
> > >
> > > If so, I can't see that in the trace file, I checked all the 'ela='
> > > values for wait event g_c_cr_r .... where the heck this value 3288.24
> > > came from? And how do I find it? Truse me, the trace file is > 5G.
> > >
> > > Raj
> > > --------------------------------------------------------------------------------
> > > Rajendra dot Jamadagni at nospamespn dot com
> > > All Views expressed in this email are strictly personal.
> > > select standard_disclaimer from company_requirements;
> > > QOTD: Any clod can have facts, having an opinion is an art !
> > >
> >
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
