In this post I will present a script called WaitCheck which you can use to monitor wait events in an Oracle database. You can find and download it from my GitHub Page here: https://github.com/bbalazs83/WaitCheck The script was written in python and uses the cx_Oracle Python module which you can download here: https://pypi.python.org/pypi/cx_Oracle/5.2.1
The following two historical views are used to search for the wait event: DBA_HIST_SYSTEM_EVENT and DBA_HIST_SYS_TIME_MODEL. As these views belong to AWR you will need the Diagnostic pack license to use the script.
Running the check
$ ./WaitCheck.py -e "cursor: pin S wait on X" Connected to Instance orcl Version: 12.1.0.2.0 Wait time for cursor: pin S wait on X: 0.0 sec Elapsed Time between the last 2 snapshots: 3373.0 sec DB Time between the last 2 snapshots: 35.856 sec WaitCheck - OK! cursor: pin S wait on X wait percentage: 0.0
Specifying the threshold levels manually:
$ ./WaitCheck.py -e "hard parse elapsed time" -w 10 -c 20 Connected to Instance orcl Version: 12.1.0.2.0 Wait time for hard parse elapsed time: 2.13 sec Elapsed Time between the last 2 snapshots: 3373.0 sec DB Time between the last 2 snapshots: 49.812 sec WaitCheck - OK! hard parse elapsed time wait percentage: 4.27
Return codes
$ ./WaitCheck.py -e "hard parse elapsed time" -w 20 -c 40 Connected to Instance orcl Version: 12.1.0.2.0 Wait time for hard parse elapsed time: 2102.13 sec Elapsed Time between the last 2 snapshots: 3373.0 sec DB Time between the last 2 snapshots: 2850.58 sec WaitCheck - CRITICAL! hard parse elapsed time wait percentage: 73.74 $ echo $? 2
| Status | Return Code |
|---|---|
| OK | 0 |
| WARNING | 1 |
| CRITICAL | 2 |
| UNKNOWN | 3 |
Why monitor Oracle wait events
Monitoring wait events is a proactive way of finding performance bottlenecks in an Oracle instance. Basically you are able to spot a performance issue before it turns into a troublemaker for the whole instance. It is also a great way to be alerted if there is any unexpected change in the load of the instance which might be caused by an application bug.
Which wait events are worth monitoring
It depends on the performance issue and the instance load profile but you can use the following queries to get a list of wait events you can monitor with WaitCheck:
select distinct EVENT_NAME from DBA_HIST_SYSTEM_EVENT; select distinct STAT_NAME from DBA_HIST_SYS_TIME_MODEL;
Some of the well known wait events are explained here:
cursor: pin S wait on X
This wait event is related to mutex contention (mostly in the library cache) and generally it should not be higher than 2-5% (of course there are exceptions). In case you notice this wait event to be too high it might be related to a high number of hard parses or to an Oracle bug affecting the library cache.
enq: TM – contention
Table lock problems during DML which might be related to unindexed foreign key constraints.
buffer busy waits
This wait event might become a problem in IO-bound instances when there are blocks which are being read into the buffer cache by one session and at the same time another session is waiting for that block to be able to read it.
library cache: mutex X
This wait event can refer to library cache lock (mutex) contention. If the value is constantly high it might relate to an undersized shared pool. In case the application accessing the instance is mainly using PL/SQL you might consider pinning some of the code to reduce contention problems.
hard parse elapsed time
When an SQL statement is hard parsed it means that Oracle has to load (or reload) it into the shared pool. As this operation might need significant time it is something that should be avoided in general. One of the most common causes of hard parsing is using literals instead of bind variables.