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.
No comments:
Post a Comment