In AWR, the ‘Wait Classes by Total Wait Time’ table comes under the ‘Report Summary’ section. Refer to the below figure:
To analyse this section, firstly check for the ‘Wait Class’ column. If User I/O, System I/O and Others have moderate ‘Waits’ value then it is fine. But if ‘Concurrency’ under the wait class has a high value then it could be a serious problem. Next to look at is Total Wait Time (sec) which shows how many times DB was waiting in this class and then Wait Avg (ms). If the Total Wait Time(sec) are high but the Wait Avg(ms) is low then you can ignore this. If both are high or the Wait Avg(ms) is high then this has to be further investigated.
Note that there could be significant waits that are not listed here, so check the Foreground Wait Events (Wait Event Statistics) section for any other time-consuming wait events.
For the largest waits look at the Wait Event Histogram to identify the distribution of waits.
Within Oracle, the duration of a large number of operations (e.g. Writing to disk or the control file) is metered. These are known as wait events because each of these operations requires the system to wait for the event to complete. Thus, the execution of some database operation (e.g. a SQL query) will have several wait events associated with it. We can try to determine which wait events are causing problems by looking at the wait classes and the wait event reports generated from AWR.
The ‘Wait Classes’ provide the wait statistics for various sub-systems like System I/O or User I/O etc. which is the summation of wait times. These statistics allow one to quickly determine which subsystem is likely to suspect performance problems (e.g. the network, cluster or concurrency).
In the above screenshot, the DB CPU wait class has the largest number of waits (total of 1151 seconds). Wait events are normal occurrences, but if a particular sub-system is having a problem performing (e.g. the disk sub-system) this fact will appear in the form of one or more wait events with an excessive duration. Then, the wait event report provides some insight into the detailed wait events. Note that this section is sorted by Total Wait Time (seconds).
You may be interested:
- AWR – Buffer Hit Ratio – Analysis
- AWR – Execute to Parse Ratio – Analysis
- AWR – Instance Efficiency Percentages
- AWR Report Analysis Guide