Re: trying to understand an awr report

From: Kerry Osborne <kerry.osborne_at_enkitec.com>
Date: Mon, 9 May 2011 19:46:38 -0500
Message-Id: <3E2C69A2-4541-4104-9CDC-F226A6402F18_at_enkitec.com>

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 SELECT C603210194 FROM T2245 W...
> 12,041 12,023 2.94 3ycgb4yb5svuc SELECT T954.C1, C1000000161 FR...
> 11,554 11,528 2.82 00yn69r0dguk6 SELECT T954.C1, C1000000161 FR...
> 11,441 9,214 19,384 0.59 2.79 41cnaprwx32n6 SELECT cdintegrationid FROM bm...
> 11,356 11,337 2.77 gvtgqv0gqx6nn SELECT T954.C1, C1000000161 FR...
> 7,877 7,875 1.92 fyb6g4640yaag SELECT T954.C1, C1000000161 FR...
> 7,465 1 5 1492.91 1.82 cmdx8dhbwnncy DELETE FROM T129 WHERE C1 = :"...
> 6,731 6,728 1.64 0pks9t80ukx0s SELECT T936.C1 FROM T936 WHERE...
> 5,947 2,982 1,308 4.55 1.45 4rkq5f7cx0aau SELECT T1346.C1 FROM T1346 WHE...
> 5,134 5,122 134 38.31 1.25 9g2vbj6d66ssr SELECT T954.C1, C1000000161, C...
> 4,990 4,843 5,509,111 0.00 1.22 9dqj7wx3yc9yq SELECT fieldId, vuiId, propSho...
> 4,807 4,807 32 150.23 1.17 0xjy5qb0z6s1w SELECT T727.C1, C10000001, C30...

--
http://www.freelists.org/webpage/oracle-l

Received on Mon May 09 2011 - 19:46:38 CDT