Tuning with Wait Stats in Oracle and SQL Server
Posted by decipherinfosys on October 21, 2007
Oracle and SQL Server provide a very powerful tuning methodology called the Wait Stats. Essentially, whenever a request is made from the RDBMS, if the response takes say n seconds to satisfy the request, the RDBMS internally tracks the different time buckets which led to those n seconds. This response time to a request is essentially a combination of the time taken to service the request + the wait time. The service time is the time that a given process spends on the CPU and the wait time is the amount of time a process waits for specific resources to be made available. The wait time is attributed to the different types of wait types. These include waits for I/O operations, locks, latches, background processes activities, network latencies etc.. Each time a process has to wait on something when making a call to the database, Oracle and SQL Server collect statistics about the type of wait. This wait event information helps us to quickly identify the bottlenecks and take the correct action. A classic day-to-day analogy would be a trip to the food store where the grocery clerk scans your food items. The response time to your request of checking out is dependent on how long you have to wait in line (and other wait events like looking up a food item based on a code or the clerk getting help from a senior associate etc.) and how fast the grocery clerk scans the items (this is the service time). One can then choose to either go to a self service counter to reduce the service time and/or move to another lane to reduce the wait in line time.
A process is either servicing the request on the processor or it is off the processor and is in a wait state and these two timings combine to form the total response time. Please do note that this is just the database response time discussion. A process (from an end users perspective) within an application goes through a lot more and so the end user request/response would include things like middle tier latencies, network latencies, memory, CPU issues etc.
The Oracle Wait Interface (OWI) was first introduced with version 7.0.12. In 10g Release 1, there were over 800 different wait events that can help you detect and tune the performance issues in the system. Microsoft exposed these wait events in SQL Server 2000 and in SQL Server 2005, over 200 wait events exist in SQL Server that can help the DBA/Developer to help tune the system more effectively.
One thing to keep in mind is that these counters are re-set when the instance is re-started (these can be re-set manually as well) so if you are looking for trend analysis or profiling your system, you would need to build an archiving solution that tracks these events and logs them. You can then do advance level reporting on it.
There is a lot of literature available on these from Oracle and Microsoft so we are going to provide you those links so that you can read up more on those and then in subsequent posts, we will look into specific key wait types.
We use this wait events and queues methodology extensively in our tuning engagements and have built alerts and reports based on these to do preventive maintenance and address bottlenecks in a timely fashion.
Sorry, the comment form is closed at this time.