Knowledge

Wait Interface

Introduction

During the normal functioning of a database, an Oracle process will occasionally have to wait for something. These waits are recorded the RDBMS performance tables (V$Views) where they are categorized into wait events.

There are two types of wait events:

  • Idle waits simply mean that process has no work to do.
  • Non-idle waits indicate that a process is waiting for a resource that is temporarily unavailable.

In addition to all I/O related Wait Events all events describing the database internal locking and concurrent access behaviour like Enqueues and Latches are important to understand and analyze.

The oracle wait interface is a performance tracking methodology. It is designed for process bottlenecks, known as “wait events” or sometimes known as symptoms. Some of these bottlenecks can be I/O operations, locks, latches, background processes activities, network latencies and others. The wait interface has the ability to record and then present the bottlenecks in which a process comes into contact with from the beginning to the end.

It is able to keep track of the number of times that a process hits a bottleneck and also the amount of time that the process spends on the bottleneck. This is very useful as tracking the number of times can remove and reduce the impact of a system having any major bottlenecks in the future, which of course improves performance.

Just like any system, it can only be as effective and efficient as the weakest link. By identifying and tracking the weak links, you create an area of awareness for increased improvement and productivity. By removing, bypassing and reducing your bottlenecks, you will immediately improve your performance.

During any processing, your database prices might be trying to service a request on the central processing unit (CPU). For example the database process might be off the CPU awaiting a certain resource to become available, or it might be awaiting a new instruction on what action to take next. The important consideration here is that Oracle collects statistics about the wait, each time a process has to wait for something.

Wait event statistics are made available in several V$ views through the interface. On top of this, the wait event information allows users to easily identify the main bottlenecks that cause problems with a process, so that the users have the ability to find an appropriate solution for the problem.

It is now well known that having the capabilities to quickly identify the major bottlenecks that are the cause of a slow process, are what has revolutionized performance tuning and really enhanced the users ability to troubleshoot. Previously, it would take much longer to troubleshoot and solve the issues associated with slow process times.

Prior to the Oracle wait interface, the method of troubleshooting simply meant going through a preset list of checks. This involved running a set of queries that calculated certain ratios and checked for high resource-consuming SQL statements. The major downside of the checklist method was that there were often issues with every item on the checklist, and as the items were not time based, users did not know which issue would offer the best return for the effort exerted.

As such, the troubleshooting process and the problem solving process became very hit and miss, and very time consuming. This usually meant that users generally had to choose one issue based on their experience and best guess. As such, it was not a very accurate method for resolving any issues, and was not a long-term solution. It also meant that many hours were wasted and many unnecessary processes were run. However, with the wait interface, you can clock every bottleneck and quickly sort those found into the significant ones and the less urgent items.

Benefits of wait interface

Pointing the wait interface straight at the major bottleneck provides much more than just saving time troubleshooting. This is very useful as it helps the wait interface to look in the right places and not to waste time dealing with the wrong issues. For example, if you see that a process spent the majority of its time competing for latches and a small amount of time waiting for I/Os, then it makes sense to spend your time on the most important areas.

The great benefit of the wait interface is that you can now direct your time and resources in efforts that bring enhancements in the performance output. This leads to a smaller turnaround time in terms of problem solving, lower operational costs, as well as enhanced customer service.

The oracle wait interface brings with it a new way of monitoring, measuring, and tuning a database. On top of this, it gives users a new way to measuring and report on database performance. If you manage a database for clients, then you can now show them a much higher degree of information, and show them the bottlenecks or symptoms that the processes come across. It will also show them the time that is wasted and enable them to discuss the plan of action. It will also show users when there is a problem in the database.

Another great benefit of the wait interface is that it allows people to see the performance in a way that makes sense to everyone. This way, everyone will know what and where the major problems lie, which helps to instigate a much quicker recovery.

KnowledgeBanner
QueryAdvisor trial.
QueryAdvisor in 30 Seconds

QueryAdvisor quote.
QueryAdvisor support forum.
QueryAdvisor Twitter channel.
social icons Facebook Twitter Blogspot RSS