RE: TKPROF question

  • From: Muqthar Ahmed <Muqthar.Ahmed@xxxxxxxxxxxxxxxxx>
  • To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 23 Mar 2004 15:44:17 -0500

Hi,

Can you give me the URL for the website(Instructions how to dump blocks are on 
Julian Dyke's site).

Thanks
Muqthar

-----Original Message-----
From: Mladen Gogala [mailto:mladen@xxxxxxxxxxxxxxx] 
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
-----------------------------------------------------------------
                *       *       *       *       *       *       *       *       
*

The information contained in this E-mail message is privileged, confidential, 
and may be protected from disclosure; please be aware that any other use, 
printing,copying, 
disclosure or dissemination of this communication may be subject to legal 
restriction or sanction. If you think that you have received this E-mail 
message in error, please 
reply to the sender and delete it from your computer. Thank you. 
----------------------------------------------------------------
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: