Re: AWR Sample Report
Date: Tue, 25 Nov 2008 06:18:40 -0800 (PST)
Message-ID: <8a1b6759-9d76-4638-ad39-74adf489a622@c1g2000yqg.googlegroups.com>
On Nov 25, 8:03 am, raja <dextersu..._at_gmail.com> wrote:
> Hi,
>
> I have also another information which would help to solve the problem
> along with the AWR Report.
> From the advisor tables, i got the following information ( these were
> the findings made by oracle ) :
>
> Application Analysis :
> -----------------------
> Wait event "Backup: sbtwrite2" in wait class "Administrative"
> Wait event "Data file init write" in wait class "User I/O"
> Wait event "enq: CF - contention" in wait class "Other"
> Wait event "enq: JI - contention" in wait class "Other"
> Wait event "enq: TC - contention" in wait class "Other"
> Wait event "inactive session" in wait class "Other"
> Wait event "SQL*Net more data from dblink" in wait class "Network"
> Wait event "wait for a undo record" in wait class "Other"
> Waits on event "log file sync" while performing COMMIT and ROLLBACK
> operations
>
> I will check over the above wait events also and get back to you.
>
> Waits on event "log file sync" while performing COMMIT and ROLLBACK
> operations - i think we should not use more commits in the
> application, to solve this problem.
>
> I hope most of the above mentioned wait events were due to the Backup
> activity that has been taken place ( This was also present in the AWR
> Report).
> I have consolidated the above wait events ( data was taken for 2
> months )
> So the problem with the database should be mostly with the backup
> activity only.
> Correct ?
>
> With Regards,
> Raja.
Raja,
It sounds like you are interested in knowing how to read an AWR report, without any real problem currently at hand. That's OK, because that is the best time to learn (when you are not on fire).
I would suggest you do some time series analysis of your AWR to get a feel for what types of "stuff" you see and when you see them. You can then begin to model your application and know when something is in fact, "wrong".
Try something like what is below to get you started. You can graph the output in Excel to see what the trend is in your database, in this case of single block reads. You can then apply this type of query to other events in dba_hist_system_event, as well as other AWR views, such as dba_hist_systat...
select to_char(begin_interval_time,'YYYY_MM_DD HH24:MI'),
dhse.instance_number,
time_waited_micro - lag(time_waited_micro, 1, 0) over (order by
dhse.instance_number, dhse.snap_id) as time_waited,
total_waits - lag(total_waits, 1, 0) over (order by
dhse.instance_number, dhse.snap_id) as total_waits
from dba_hist_snapshot dhs,
dba_hist_system_event dhse
where dhs.snap_id = dhse.snap_id
and dhs.instance_number = dhse.instance_number
and event_name = 'db file sequential read'
order by 1,2;
If you see db file sequential read is always at the top of the list every reporting period, then an AWR that shows this was the top event is probably not a real problem.
If you run an analysis against other items in AWR and one bubbles to the top between 2AM and 4AM every Wednesday morning that you never see otherwise, that may be something to investigate more in the AWR report during the periods of the bubble. I find that to be absolutely indispensable when trying to uncover "un-reported" performance problems.
HTH, Steve Received on Tue Nov 25 2008 - 08:18:40 CST