How The Oracle Database Determines Wait Time When It's Not Set

Posted on 30-Jun-2014 by Craig Shallahamer, craig@orapub.com

Have you ever wondered how the Oracle Database 12c (and earlier versions) determines the wait time when it has absolutely no control over how long the wait will take? If so, then read on!

The Back Story

Using wait time is part of an Oracle Time Based Analysis (OTBA). While Oracle process CPU consumption is a big part of the analysis, the other category is non-idle wait time.

You can see the two categories of time clearly "in action" with my Real-Time Session Sampler script, rss.sql. It's part of my OraPub System Monitor (OSM) toolkit, that can be downloaded for free. Here's an example of the output:

I need to explain a little more about CPU time and then Oracle wait time.

An Oracle processes wants to burn CPU. Without consuming CPU an Oracle process, can well...not process work! Oracle keeps track of the CPU consumption time. It's the actual CPU time consumed, for example, 500 ms or 3 seconds.

When an Oracle process can not burn CPU, the process, in Oracle terms, it must wait. For example, when an Oracle process waits, it's like it yells out details about why it's waiting. We call this yell a wait event. Each wait event has a name. And the name provides clues about why the process can't burn CPU and is therefore waiting.

There Are Different Reasons Why An Oracle Process Waits

There are three broad categories Oracle must time, when a process is not consuming CPU:

Exploring When Oracle Has No Idea How Long The Wait Will Take

Let's say when I work it's like an Oracle process consuming CPU. And if I have to stop working to drive to a meeting, it's like an Oracle process waiting. And in this situation, I really don't know how long the wait will take. It's out of my control. There could be an accident along the way (think: table level lock)! An Oracle process can experience this same kind of thing.

If I'm an Oracle server process and I discover a block I need is not in the buffer cache, I'm going to need to make a call to the OS for that block. When I make the synchronous IO call, I really do not know how long it will take and I have to wait, that is, the Oracle process must wait.

From a DBA perspective, when I perform an Oracle Time Based Analysis (OTBA) I need know how long the IO, that is, the wait took. How does Oracle figure this out? It's pretty simple actually. It's basiclly like this:

Get the current time, start time

Make the synchronous IO call and wait until IO received

Get the current time, end time

Calculate the delta, end time - start time

The "delta" is the wait time for the single block read. If the single block read is a synchronous read and then placed into the buffer cache, Oracle will tag or name the wait time calling it a "db file sequential read".

Actually Watching An Oracle Process Figuring Out the Wait Time

Some say that seeing is believing. For sure it helps one to learn quickly. If you want to see with your own eyes an Oracle server process determine wait time for a multiple block synchronous read (event name is, db file scattered read), watch the below video.

Pretty cool, eh? By the way, the processing of timing processes and events has a special name, called instrumentation. As we can see, Oracle has instrumented its kernel code.

Enjoy your work and thanks for reading!

Craig.

If you have any questions or comments, feel free to email me directly at craig at orapub.com.

Part 2. Does Table Column Order Affect Performance? Top 7 Reasons Why Oracle Database Conferences Are A Waste Of Time How To Change The Priority Of Oracle Database Background Processes