Wait Events In Oracle

Every Oracle process at given given moment is involved in either a request or waiting for event to occur.
And when we have a Term "busy" means that the process needs CPU.

Take the case of a dedicated server process might be performing some arithmetic operation while executing a PL/SQL block. This process would be said to be busy in execution. But the LGWR process, might be waiting for the O.S to indicate that the redo log buffer has successfully been written to disk. In this case the LGWR process is said to be waiting. Oracle kernel developers have defined a list of every possible event that an Oracle process could be waiting for.

In Oracle 8.0 there were 158 such wait events. In Oracle 8i Release 3 there are 217. At any moment, every Oracle process that is not busy is waiting for one of these events to occur. Suppose a dedicated server process is waiting for an application to submit a SQL statement for execution. This wait event is called "SQL*Net message from client." Another dedicated server process might be waiting for a row-level lock on the a table to be freed so that an UPDATE statement can continue. That wait event is called "enqueue."

Wait event interface Is all about tracking the wait events by the DBA's. This can be known by querying v$ views, in depth information is provided for what events processes are waiting. we might encounter a case to learn that a dedicated server process was waiting 10 milliseconds for the OS to read four blocks from data file 15, starting at block 19015.We can also get the summary information time spent on each Oracle process which has spent waiting on each type of wait event for the duration of the process, Oracle processes can be directed to write these wait information into a trace file

In case of long time consuming process to complete the full operation the wait event interface one can use it to get where time is being spent for the processes. A report may take six hours to completion such case wait event interface will enable you to know how much of that four hours was spent waiting for disk reads caused by full table scans, disk reads caused by index lookups, latch contention, parallel processing, and so on. The wait event interface gives both breadth and depth in the information it provides you. The wait event interface provides more useful information to work with as compared to cache hit ratios . You get data that can touch upon so many different areas of your database such as disk I/O, latches, index lookups ,parallel processing, network traffic, latch contention, checkpoints, and row-level locking. At the same time, you get incredible detail such as the file number and block number of a block being read from disk, or the name of a latch being waited on along with the number of retries.

The wait event interface will not always give you all of the information you need in order to diagnose and solve a problem, but it will certainly point you in the right direction. You might think the buffer cache is too small because the cache hit ratio is only 70%, but in fact, the application's slow response time could be caused by latch contention in the shared pool, a bottleneck in the log writer, or any of a number of other things.