11g Wait Events Statistics - Wait Event Histograms

Wait Event Histogram

From 11g Oracle introduced Wait Event Histograms in the AWR report. This provides DBAs more granular information in the report for efficient diagonosis.This section splits the event counts into elapsed time buckets so that we can see if there are any suspicious or anomalous response time / frequency patterns. This is a very interesting and informational view in Oracle10g when it comes to analyzing wait time per wait event.

 It shows a histogram of the number of waits, total wait time and maximum wait for each event. The bucket sizes or the time intervals in this case, for the histograms are predefined. They cannot be changed. The bucket time intervals are from < 1 ms, < 2 ms, < 4 ms, < 8 ms, < 16 ms, and so on, increasing with the power of 2, till we get to >= 2 ms. The buckets will be populatedaccordingly, and only when TIMED_STATISTICS is set to TRUE. From a very short report it produces an extremely useful addition to the information about lost time.

For any event, the less number of buckets the better because the wait times they represent are shorter. The bucket boundaries are pre-set and you cannot change their values. Also, high WAIT_COUNT numbers should be in the buckets with lower WAIT_TIME_MILLI values. If you have it the other way around, then that's the event that needs further investigation.

Another good application of this view is to monitor the SQL*Net message from client event. If the WAIT_COUNT is high for the low-end buckets then it could mean that the application is communicating a lot with the client. This may cause excessive network traffic, which could be reduced with higher ARRAYSIZE or server side processing. On the other hand, if the WAIT_COUNT is high for the high-end buckets then it is more likely due to user lag time.

Below are the screen shots of the various sections of AWR representing Wait Events Statistics.

 It is always good if you see the " No data exists " in the 4 min to 1 hr segment and 4 sec to 2 min segment.

Top 5 Timed Events - log file switch (archiving needed)

log file switch (archiving needed) -- Waiting for a log switch because the log that the LGWR will be switching into has not been archived yet. The main cause of this error is due to the non availability of space in the archive log destination.

Database is literally hung in this situation, even thought DB is not physically down, all the operations will come to a standstill affecting the uptime and availability of the system.

The best approach if one runs out of space in the archive log destination is to configure alternative location for archive log destination to avoid outages due to space issues.

Oracle has got an option to add Alternate attribute added to the log_archive_destination. Alternate archiving destination will be used if archiving to the original archiving destination fails. Below link will provide more information about the alternate option.

http://download.oracle.com/docs/cd/B10500_01/server.920/a96653/log_arch_dest_param.htm#77201

11g Top 5 Timed Foregorund Events

From 11g, Oracle replaced “Top 5 Timed Events” as “Top 5 Timed Foreground Events”

Prior to Oracle 11g, wait event statistics included both foreground processes (for example, session based) and background processes (such as DBWR, SMON). It makes sense to break those different process types down into different sets of wait event statistics. He is now making clear in AWR with Foreground and Background events which will help us to focus more on foreground events.

Find below images of new names in 11g.

We will discuss more enhancements like Wait Event Histogram in next post.