Re: AWR Sample Report

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.

While the Application Analysis information that you provided may be helpful, it is probably best to focus on a specific time interval in which you know that a performance problem is affecting *important business critical* activities. Is the backup requiring an extra 15 minutes to complete an *important business critical* activity (it might be), or is a repeated (user interactive) process which should take 10 seconds to execute that now takes takes 2 minutes to execute a greater *important business critical* activity. If you determine that it is a specify user activity which is more important, look at an AWR report for the time interval when the user activity is happening. If there is a specific user involved in the performance problem, switch to a 10046 trace at level 8 or 12 for just that user.

The excessive wait time for "log file sync" could possibly be minimized/reduced by allocating a greater percentage of the *battery back* RAID cache to write back caching, if that is an option in your environment. When a commit is executed, the session must wait on this event until Oracle receives confirmation from the operating system that the write to disk completed. With write back caching, the write confirmation is returned immediately, and the data in the cache is later flushed to disk.

When investigating the wait events, do not just find the definitions of those wait events. Dig deeper into the meaning of the wait events by seeing if those wait events are discussed by Tom Kyte, Cary Millsap, Jonathan Lewis, Richard Foote, Kevin Closson, etc.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Tue Nov 25 2008 - 07:58:25 CST