RE: trying to understand an awr report

Agreed that executions=0 generally means the statement is still in
progress at snap time. 
If the statement finishes shortly after the snap time then it may have
been flushed by the time the next snap occurs and all metrics will be
gone. I maintain a central repository of AWR metrics for many databases
and these are what I call "partial metrics" and I let users exclude them
from analysis using a radio-button as they skew results. 
Bear in mind also that if your statement uses PQ then elapsed time can
be indecipherable as it is a sum of elapsed time for x number of slave
sessions that become active/inactive over the excution lifetime of the
cursor.

________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Kerry Osborne
Sent: 10 May 2011 08:47
To: eglewis71@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: trying to understand an awr report


Generally that would be a statement that didn't complete by the time the
ending snapshot was taken. Like so: 

  Elapsed      CPU                  Elap per  % Total
  Time (s)   Time (s)  Executions   Exec (s)  DB Time    SQL Id
---------- ---------- ------------ ---------- ------- -------------
       257        163            0        N/A    82.8 gf5nnx0pyfqq2
Module: SQL*Plus
select a.col2, sum(a.col1) from kso.skew a, kso.skew b group by a.col2

That statement was still running when that ending snapshot was taken. 

Also notice that the elapsed time per exec is rounded to the nearest
tenth of a second. So if it says 0.0 that means that the statement
averaged less than 0.05 
seconds.

Kerry Osborne
Enkitec
blog: kerryosborne.oracle-guy.com






On May 9, 2011, at 7:21 PM, ed lewis wrote:


        
         
        Hi,
            I'm trying to understand some of the information in
        an AWR report.
         
            Specifically,
         
        - The "executions" column and "Elap per exec"  , in several
cases is empty,
          but shows "DB time",   "Elapsed TIme", and "CPU time". 
          Can anyone explain that ?
         
        - 1 transactions shows 5.509,111 executions but
          no "elap per exec". 
         
            These numbers are from 9 hours of snapshots.
         
        Solaris 10,   RDBMS, and RAC 10.2.0.4.
         
            Your comments are appreciated.
         
                thanks
         
                ed
         
          

        *       Resources reported for PL/SQL code includes the
resources used by all SQL statements called by the code. 
        *       % Total DB Time is the Elapsed Time of the SQL statement
divided into the Total Database Time multiplied by 100

Elapsed Time (s)         CPU Time (s)    Executions      Elap per Exec
(s)      % Total DB Time         SQL Id  SQL Module      SQL Text       
16,520   16,518  19,286  0.86    4.03    d4fdhcjs2vndp
<file:///C:/Documents%20and%20Settings/Edward%20Lewis/Local%20Settings/T
emp/awrrpt_2_54917_54938.html#d4fdhcjs2vndp>            SELECT
C603210194 FROM T2245 W...      
12,041   12,023                  2.94    3ycgb4yb5svuc
<file:///C:/Documents%20and%20Settings/Edward%20Lewis/Local%20Settings/T
emp/awrrpt_2_54917_54938.html#3ycgb4yb5svuc>             SELECT T954.C1,
C1000000161 FR...       
11,554   11,528                  2.82    00yn69r0dguk6
<file:///C:/Documents%20and%20Settings/Edward%20Lewis/Local%20Settings/T
emp/awrrpt_2_54917_54938.html#00yn69r0dguk6>             SELECT T954.C1,
C1000000161 FR...       
11,441   9,214   19,384  0.59    2.79    41cnaprwx32n6
<file:///C:/Documents%20and%20Settings/Edward%20Lewis/Local%20Settings/T
emp/awrrpt_2_54917_54938.html#41cnaprwx32n6>            SELECT
cdintegrationid FROM bm...      
11,356   11,337                  2.77    gvtgqv0gqx6nn
<file:///C:/Documents%20and%20Settings/Edward%20Lewis/Local%20Settings/T
emp/awrrpt_2_54917_54938.html#gvtgqv0gqx6nn>             SELECT T954.C1,
C1000000161 FR...       
7,877    7,875                   1.92    fyb6g4640yaag
<file:///C:/Documents%20and%20Settings/Edward%20Lewis/Local%20Settings/T
emp/awrrpt_2_54917_54938.html#fyb6g4640yaag>             SELECT T954.C1,
C1000000161 FR...       
7,465    1       5       1492.91         1.82    cmdx8dhbwnncy
<file:///C:/Documents%20and%20Settings/Edward%20Lewis/Local%20Settings/T
emp/awrrpt_2_54917_54938.html#cmdx8dhbwnncy>            DELETE FROM T129
WHERE C1 = :"...        
6,731    6,728                   1.64    0pks9t80ukx0s
<file:///C:/Documents%20and%20Settings/Edward%20Lewis/Local%20Settings/T
emp/awrrpt_2_54917_54938.html#0pks9t80ukx0s>             SELECT T936.C1
FROM T936 WHERE...      
5,947    2,982   1,308   4.55    1.45    4rkq5f7cx0aau
<file:///C:/Documents%20and%20Settings/Edward%20Lewis/Local%20Settings/T
emp/awrrpt_2_54917_54938.html#4rkq5f7cx0aau>             SELECT T1346.C1
FROM T1346 WHE...       
5,134    5,122   134     38.31   1.25    9g2vbj6d66ssr
<file:///C:/Documents%20and%20Settings/Edward%20Lewis/Local%20Settings/T
emp/awrrpt_2_54917_54938.html#9g2vbj6d66ssr>            SELECT T954.C1,
C1000000161, C...       
4,990    4,843   5,509,111       0.00    1.22    9dqj7wx3yc9yq
<file:///C:/Documents%20and%20Settings/Edward%20Lewis/Local%20Settings/T
emp/awrrpt_2_54917_54938.html#9dqj7wx3yc9yq>            SELECT fieldId,
vuiId, propSho...       
4,807    4,807   32      150.23  1.17    0xjy5qb0z6s1w
<file:///C:/Documents%20and%20Settings/Edward%20Lewis/Local%20Settings/T
emp/awrrpt_2_54917_54938.html#0xjy5qb0z6s1w>            SELECT T727.C1,
C10000001, C30...       



=============================================================================== 
Please access the attached hyperlink for an important electronic communications 
disclaimer: 
http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html 
===============================================================================

Other related posts: