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: Mladen Gogala <mladen_at_wangtrading.com>
Date: Tue, 23 Mar 2004 12:38:19 -0500
Message-ID: <20040323173819.GA1657@mladen.wangtrading.com>


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
-----------------------------------------------------------------
Received on Tue Mar 23 2004 - 12:22:08 CST

Original text of this message

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