Re: AWR Sample Report

On Nov 6, 3:37 am, raja <dextersu..._at_gmail.com> wrote:
> On Nov 5, 7:15 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
>
>
>
> > 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.- Hide quoted text -
>
> > - Show quoted text -
>
> Hi,
>
> Thanks for ur immediate reply.
>
> I want to learn how to interpret the AWR Report.
> I was not able to find any docs regarding this in google.
> Also, i wanted to have some suggestions regarding the AWR report that
> i have posted.
>
> Any docs or URL related to Interpretation of AWR/Statspack report,
> Please send them.
> Also, any suggestions regarding the AWR Report Output that i have
> given, Please provide them too.
>
> With Regards,
> Raja.

Hi Raja,

How big is the database? You full scanned almost 2,000,000 rows in 32 seconds? Granted, you could have a 2,000 row table that was scanned 1000 times in that period, but compared to 231,0000 gets by rowid (probably index) that seems really high for that period of time.

What does the SQL look like?

HTH, Steve Received on Thu Nov 06 2008 - 11:01:40 CST