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:
- buffer busy waits
- free buffer waits
- db file scattered read
- db file sequential read
- enqueue waits
- log buffer space
- log file sync
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.