IO Read Wait Occurrence Mismatch - Part 2

This page has been permanently moved. Please CLICK HERE to be redirected.

Thanks, Craig.

In Part 1 of this series, it was clearly demonstrated the number of IO read requests is not likely to equal the number of wait event IO read occurrences. This concerned me.

My primary concern centered with my systematic quantitative performance analysis methods. I always quantify my analysis so I have numbers to back up my recommendations. This also allows me to anticipate and compare the impact of various performance solutions. If the system under examination is heavily read IO centric, the IO read wait time is a key input parameter in my analysis and therefore, I need to have confidence in the read IO wait time. This "mismatch" concern lead to me to research the various IO read related instance statistics (which I have not blogged about) and the relationship between IO block reads, IO requests, and read wait occurrences.

What I discovered is that it is not a simple task to determine the number of physical IO read requests for each classification (e.g., direct path read requests associated with temporary segments). What began as a simple quest to predict IO read wait time without depending on read related wait occurrences ended up being a major research project that is still not completed...but I digress...

Simply put, Oracle blocks are physically read by IO requests which, may result in an IO read wait occurrence.

If you don't have the time (or desire) to read this entire blog entry, here is a summary of this blog entry:

1. An IO read wait occurrence is different than a read request. A request is truly a request from Oracle to the IO subsystem. A read wait occurrence should only occur when the process must wait for blocks so it can continue processing.
2. The request to occurrence mismatch is to be expected when asynchronous IO (async IO) or something similar is active. Greater async IO activity is likely to increase the difference between read wait occurrences and read requests.
3. It appears total IO read wait time sourced entirely from the wait interface is fine, but don't count on it being perfect.

Requests vs Occurrence

In Part 1 of this series I demonstrated the number of Oracle IO read requests is very unlikely to equal the number of IO read wait occurances. I believe there are two core possibilities and I wouldn't be surprised if there are others. First, it is because the existance of asyncrounous IO. Second, Oracle does not guarentee all IO waits are instrumented. Let's take a look at each.

Asynchronous IO.

Asyncronous IO allows a process/thread to submit a single multiblock request and start processing the results as soon as some of the results become available. In contrast, when a process issues a syncrounous IO request it cannot process the results until all the requested blocks have been returned. As reported in my previous blog entry (Part 1), whether a synchronous or an asynchronous request, it appears that Oracle tracks each IO request correctly and makes the number of requests available through the physical read total IO requests instance statistic.

It can get more complicated than this. I'm not an IO operating system expert, but I can see that even without async IO technically in use, a process with multiple threads each making synchronous IO read calls will effectively be doing asynchronous IO. I like to keep in mind that Oracle kernel developers and the porting groups are always looking for ways to increase IO performance and will use whatever technique they can find to reach their goals. I have also noticed that in more recent Oracle versions, there are more IO related system calls.

A read wait occurrence is different than an IO read request. Oracle should not register a wait occurrence (along with the associated time) unless the process is truly waiting. For a synchronous IO this is easy to understand; the process asks the time, makes the synchronous IO request, receives all the blocks, and asks for the time gain. In this synchronous scenario, we can see the process retrieved multiple blocks, and is able to both count the wait occurrence and to time the IO request, that is, the wait time.

For an asynchronous IO call things become more complicated. (Remember that an Oracle process is either consuming CPU or waiting for something.) After the async IO multiblock call, as long as Oracle is busy processing the results, it does not post a wait event. Why? Because it is not waiting on IO. It is consuming CPU processing the IO is has already received. However, at anytime before all the blocks have been returned and the Oracle process has completed processing all the IO it has available, then the process will post an IO read wait occurrence and record the time.

The following statement should make perfect sense: In a synchronous world the number of IO read waits equals the number of IO read requests. But in a mixed sync and async environment, the number of IO read requests can exceed the number of IO read wait occurrences.

So is there truly a wait occurrence mismatch? Yes and in most situations it should be this way. The word request is purely an IO read request. The word occurrence refers to a wait occurrence. A more correct blog title would have been, "IO Read Request and Read Wait Occurrence Mismatch."

Oracle's Non-Instrumentation.

Oracle does not guarantee all waits are instrumented. Not that Oracle wants it that they, but perhaps a kernel developer made a mistake or for some reason choose to not instrument the call. Instrumentation consumes resources, so ironically performance may be a motivating factor for not instrumenting. Interestingly, in 11g Oracle added a wait event named, "unspecified wait event" and you may see some time associated with it...not a great way to build confidence in the the wait interface! So it is reasonable to assume that not all IO read wait time is accounted for.

Direct Reads vs Cached Reads

Most DBAs will tell you async IO is more active when direct reads are heavily used. I have not tested this but have no information to contradict the assertion. [Background: Direct reads do not place the block(s) into the buffer cache before they are processed. This can enable faster per process IO throughput but the blocks are not shared (no cache IO) with other processes which can force repeat IO calls (physical IO) thereby slowing block throughput (physical IO + cache IO). So Oracle must choose the access strategy wisely.]

The table below contains actual Oracle performance statistics from four different systems. Here is a quick explanation of each column.

1. Sample. Simply my internal identifier for the data set.
2. WI Direct Read Occurs. The sum of v$system_event.total_waits for all IO direct read events.
3. WI Cache Read Occurs. The sum of v$system_event.total_waits for all IO non-direct read events.
4. Total Occurs = WI Direct (2) + WI Cache (3) Read Occurs
5. Percent Direct Occurs = WI Direct (2)  /  Total Occurs (4)
6. Instance Statistic, Total Read IO Requests. The value of v$sysstat, physical read total IO requests.
7. Occurs/Requests = Total Occurs (4)  /  Total Read IO Requests (6)

All but the final sample are based on internal of activity from either a Statspack or AWR report. The final sample (Interactive) contains data since the instance had started.

When there is little direct IO (col 2 / col 3 = col 5), we might expect to see the number of read wait occurrences (col 4) close to the number of IO requests (col 6).

The most startling example is the SIG sample. In this production system, there is virtually no direct reads (col 2) yet there is a significant difference between the total read wait event occurrences (col 4) compared to the total read requests (col 6). In fact, the read wait occurrences (column 4) only account for 67% (col 7) of all IO read requests (col 6).

These samples demonstrate that async IO can be active in both direct read and cache read environments.

Total IO Read Wait Time

It appears the most accurate way to gather the total IO read wait time is through the wait interface (session, group of sessions, instance, and system) and of course, by tracing a process. I am very pleased about this as it means I do not need to create a predictive algorithm based on input from both the wait interface and the instance statistics. (As I mentioned at the top, this seemingly simple exercise is extremely completed.) Instead, I simply query from the wait interface as I have always been doing.

Remember there are multiple read wait events. Here is a list that, at this time, I believe contains all read IO wait events: db file sequential read, db file scattered read, direct path read, direct path read temp, db file parallel read, control file sequential read, and log file sequential read.

Concluding Thoughts

Like I wrote in Part One of this series, Oracle has done a great job instrumenting its kernel code. Such a great job in fact, that most performance analysts rely heavily on the data it provides. But that doesn't mean we shouldn't challenge what it is telling us if the supporting evidence shows otherwise. In my case, I got sloppy with the words request and occurrence and it lead me down a very interesting path that ended up bringing me back to the wait interface. I hope I have clearly shown that an IO read request does not always result in an occurrence of a read wait event. For me personally, my confidence in Oracle's wait interface is back up to where it once was; great but not perfect.

As I mentioned in my "P.S." below, I don't check for comments on my blog, so when someone emails me about an entry enabling wonderful dialog, like Jonathan Lewis did regarding Part 1 of this series, I appreciate it very much.

Thanks for reading!

Craig.


P.S. If you want me to respond to a comment or have a question, please feel free to email me directly at craig@orapub .com. I use a challenge-response spam blocker, so you'll need to open the challenge email and click on the link or I will not receive your email.