RE: trying to understand an awr report
From: Teehan, Mark <mark.teehan_at_credit-suisse.com>
Date: Tue, 10 May 2011 09:59:16 +0800
Message-ID: <E3CCEA4C11521E43A343441089E9E555059A9C8D_at_ESNG17P32003B.csfb.cs-group.com>
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.
Please access the attached hyperlink for an important electronic communications disclaimer: http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
Date: Tue, 10 May 2011 09:59:16 +0800
Message-ID: <E3CCEA4C11521E43A343441089E9E555059A9C8D_at_ESNG17P32003B.csfb.cs-group.com>
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Kerry Osborne
Sent: 10 May 2011 08:47
To: eglewis71_at_gmail.com
Cc: oracle-l_at_freelists.org
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 % TotalTime (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 statementdivided 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 d4fdhcjs2vndpC10000001, C30...
<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,
Please access the attached hyperlink for an important electronic communications disclaimer: http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
-- http://www.freelists.org/webpage/oracle-lReceived on Mon May 09 2011 - 20:59:16 CDT