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:
> > 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

> 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.

Raja, it is good that you want to understand how to read AWR/Statspack reports. I highly recommend reading the blog entries at the link that I previously posted in this thread.

If you have access to Metalink, try a search for the term statspack With the above search, currently the seventh item returned is "Systemwide Tuning using STATSPACK Reports" and the tenth item in the list is "Oracle Performance Diagnostic Guide (OPDG)". Both of those are very good resources. Somewhere I posted a list of six or more resources for understanding Statspack reports, but I currently cannot locate that list.

In my previous post in this thread, I mentioned that I found a couple interesting wait events, which seem to indicate that an RMAN backup to tape was in process during the 31 second AWR report, which may be contributing to a performance problem. I spent a little time looking at the statistics section of the AWR report: physical read total bytes 24,088,335.07 per second physical write total bytes 15,066,329.64 per second

So a question for you to think about when you try to understand your AWR report: if the disk subsystem is reading on average about 24MB per second and writing on average about 15MB per second, what happens when the 11,529 (roughly 370 per second) single block 8KB reads occur during query execution? Do those 11,529 single block reads occur immediately, or must they queue behind other read and write requests as well as wait for the drive heads to relocate over the correct location on the physical disk?

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Thu Nov 06 2008 - 06:59:04 CST