Re: AWR Sample Report

On Nov 5, 4:54 am, raja <dextersu..._at_gmail.com> wrote:
> Hi,
>
> I have a small part ( output ) of AWR Report. Can anyone help me on
> this, to interpret the AWR Output...
>
>                 Snap Id Snap Time          Sessions Cursors/Session
> Begin Snap:     8193    17-Oct-08 16:00:54 90           62.7
> End Snap:       8194    17-Oct-08 16:01:26 89           63.4
> Elapsed:   0.52 (mins)
> DB Time:   1.63 (mins)
>

(Snip)
> Load Profile
>
>                  Per Second Per Transaction
> Redo size:      42,200.64   120,268.00
> Logical reads:  2,312.58    6,590.64
> Block changes:  133.69      381.00
> Physical reads: 359.82      1,025.45Physical writes:540.97      1,541.73
(Snip)
> Top 5 Timed Events
>
> Event                   Waits   Time(s) Avg Wait(ms) % Total Call Time Wait Class
> db file sequential read 11,529  32      3               32.6            User I/O
> CPU time                        23      24.0
> Log archive I/O         411     17      42              17.5            System I/O
> db file parallel write  674     12      18              12.6            System I/O
> Backup: sbtwrite2       491     8       15              7.7             Administrative
>
> Wait Events
>
> Event                                   Waits   %Time -outs Total Wait Time (s) Avg wait (ms)
> Waits /txn
> db file sequential read                 11,529  0.00            32              3               1,048.09
> Log archive I/O                         411     0.00            17              42              37.36
> db file parallel write                  674     0.00            12              18              61.27
> Backup: sbtwrite2                       491     0.00            8               15              44.64
> log file sequential read                342     0.00            2               5               31.09
> RMAN backup & recovery I/O          46      0.00            0               4               4.18
> control file parallel write             30      0.00            0               5               2.73
> log file parallel write                 22      0.00            0               6               2.00
> control file sequential read            19,914  0.00            0               0               1,810.36
> log file sync                           3       0.00            0               11              0.27
> Backup: sbtbackup                       1       0.00            0               22              0.09
(Snip)
> Operating System Statistics
>
> Statistic               Total
> NUM_LCPUS               0
> NUM_VCPUS               0
> AVG_BUSY_TIME           808
> AVG_IDLE_TIME           2,298
> AVG_IOWAIT_TIME         556
> AVG_SYS_TIME            154
> AVG_USER_TIME           652
> BUSY_TIME               11,330
> IDLE_TIME               32,202
> IOWAIT_TIME             7,812
> SYS_TIME                2,178
> USER_TIME               9,152
> LOAD                    0
> OS_CPU_WAIT_TIME        12,600
> RSRC_MGR_CPU_WAIT_TIME  0
> PHYSICAL_MEMORY_BYTES   82,678,116,352
> NUM_CPUS                14
> NUM_CPU_CORES           7
(Snip)
> Please explain me the AWR Report.
>
> Thanks in Advance.
>
> With Regards,
> Raja.

Raja, do you feel that the database instance is slow, or is this just a general request of how to read an AWR/Statspack report? If you are trying to learn how to read a statspack report, there are several examples on the Internet, for example this series: http://jonathanlewis.wordpress.com/category/statspack/

The duration of your AWR report is about 31 seconds, which leads me to believe that you have isolated the problem to a specific time, or to a specific process. If the problem has been isolated to a specific process, I suggest using a 10046 trace at level 8 or 12 for that process, rather than an extremely short duration AWR report.

There are a couple interesting items in the section of the AWR report which you posted:
Waits:
Backup: sbtwrite2 (related to an RMAN backup to tape, see Metalink Note:311068.1)
RMAN backup & recovery I/O (see Metalink Note:311068.1) db file sequential read, at 32 seconds

So, in this 31 second AWR report, we may have identified a case where an RMAN backup is slowing some other process. I did not spend much time looking at the other statistics, as I do not know the intention of your post.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Wed Nov 05 2008 - 08:15:17 CST