Liron Amitzi Oracle ACE

Oded Raz Oracle ACE Director

Analyzing Oracle AWR reports – top 5 events

Posted by Liron Amitzi on Aug 17th, 2010 and filed under Main-Performance, Oracle, Oracle - Latest Articles, Tips & Tricks. You can follow any responses to this entry through the RSS 2.0. You can leave a response or trackback to this entry

This is the first post related to analyzing AWR reports. We will focus on the “top 5 events” section,understand its importance and see some of the main wait events.

From Oracle documentation:
“Wait events are statistics that are incremented by a server process or thread to indicate that it had to wait for an event to complete before being able to continue processing.”

For example, wait events can be related to I/O, locks, memory allocation, etc.

The top events tell us what the server processes wait for the most. Eliminating these wait events will definately reduce execution time, since server processes will not have to wait this time for other operations to complete.

There are many wait event in Oracle databases, this is a list of some important and common wait events:

To identify a performance problem we will check the time spent on each wait event and the average wait time. These numbers can be very different for different wait events, I/O wait events will probably have a low average, while waiting on locking related wait events can take a long time. Don’t forget to compare the wait time to the AWR report time period, waiting on I/O for an hour may be reasonable if the AWR report was generated on 6 hours period, but may indicate a problem on a 10 minutes report (the waiting time can exceed the total report time, since it is cumulative for all server processes).

The best way to find performance problems is to compare the wait events and the time spent on them to an AWR report from a time period in which the database worked fine. So try to save reports as a baseline for future reference.

In the next posts we’ll go deeper into analyzing the rest of the AWR report.

2 Responses for “Analyzing Oracle AWR reports – top 5 events”

  1. athar says:

    I need your help on Oracle DB performance issue.

  2. Vineesh says:

    Thanks…good one…will keep reading this post…pls continue updating this topic..

Leave a Reply

Log in / Advanced NewsPaper by Gabfire Themes