Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: TKPROF question

RE: TKPROF question

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Tue, 23 Mar 2004 13:49:29 -0500
Message-ID: <A186CBDC8B1D61438BC50F1A77E91F73057C1BB4@xchgbrsm1.corp.espn.pvt>


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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Mladen Gogala Sent: Tuesday, March 23, 2004 12:38 PM
To: oracle-l_at_freelists.org
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_at_freelists.org put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://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_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Tue Mar 23 2004 - 13:22:18 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US