Chapter 2: Oracle Wait Interface Components

Oracle has always been self-monitoring, but it hasn ‚ t always been self-tuning . This means there is a wealth of data in each Oracle instance that can help answer a lot of performance questions. You just need to know where to find the data. There is instance-level and session-level performance data, as well as current and historical activities in various views.

This chapter provides a roadmap into the Oracle Wait Interface. You will be introduced to all the wait event views; their applications, and the extended SQL trace facility (trace event 10046 with appropriate debug level) to capture wait events in a trace file. At the completion of this chapter you will know where, when, and how to find wait event information that will help diagnose performance problems. You will begin your journey by understanding what an Oracle wait event is.

What Is a Wait Event?

An event can be defined as a particular function, or task, that the Oracle kernel performs on behalf of the user session or its own background process. Tasks such as reading and writing data blocks to and from data files, waiting for latch acquisitions before accessing or manipulating data in the memory, interprocess postings and communications, and so on are known as database events, and they have specific names .

But why are these events called wait events ?

Every session that is connected to an Oracle instance needs resources to perform its tasks. A resource may be a data buffer, a latch, an enqueue (lock), a pin, or a database object. Whenever a session has to wait for something, the wait time is tracked and charged to the event that is associated with that wait. For example, a session that needs an index block that is not in the SGA makes a read call to the operating system and waits for the delivery of the block. The wait time is charged to the db file sequential read event. Another session may have completed the last instruction and is now idle, waiting for user input. In this case, the wait time is charged to the SQL*Net message from client event. In short, when a session is not using the CPU, it may be waiting for a resource, an action to complete, or simply more work. Hence, events that are associated with all such waits are known as wait events.

Note ‚  

Oracle wait events are different from Oracle diagnostic events. The diagnostic events are discussed in Appendix A.