Are Oracle Direct Path Reads Faster Than DB File Scattered Reads?

Posted on 30-Mar-2016 by Craig Shallahamer, craig@orapub.com

Oracle database direct path reads are supposed to be faster than db file scattered reads. But are direct reads really faster?

That's really the question isn't? If your objective is wall time, then that is what we should measure. So, that's what I did. How I did this along with the results is what this post is all about. And of course, there were a few surprises along the way!

In my previous article I focused on the differences between a direct read and a buffered read. Then I introduced synchronous and asynchronous reads into the mix. Picture a two by two matrix in your mind, and you're on the right track. So, which is faster?

Read on...

The Bottom Line: Direct Reads Are Faster

No matter how you look at the experimental results, direct reads are faster than buffered reads. In my experiment, the SQL statement wall time was around 6.9 seconds for buffered reads and around 5.7 seconds for direct reads. That's around 17% faster.

The results are statistically significant, I gathered 31 samples and did the entire experiment twice just to be sure.

Below is a table summarizing the results.

I will explain each of the three tests and also why there was not a fourth. Then in the sections below, I'll provide the experimental details for those of you who really want to dig into this.

The experiment consisted of a single SQL statement that took around 5 seconds to run. By manipulating three instance parameters shown above, I was able to influence Oracle to alter how the IO reads would occur. The three instance parameters, which I am not going to detail in this article, are disk_asynch_io, filesystemio_options and _small_table_threshold.

Now for the three experiments...

#1. Buffer Reads: db file sequential read

Reference #1 is the buffered read results with a median SQL statement wall time of 6.910 seconds. The Oracle wait event was "db file scattered read", Oracle was requesting multiple blocks from the IO subsystem and Oracle was clearly doing synchronous IO. This was numerically, statistically and visually the slowest result. Regardless of the Oracle instance parameter disk_asynch_io (true or false), Oracle always performed synchronous IOs.

I know this because I did a Linux "strace" on the Oracle foreground process. It always looked like the below:


$ strace -rp 61095
Process 61095 attached
     0.002408 pread(268, "\6\242\0\0\202w\1\10}\235 \0\0\0\2\4\301\333\0\0\1\0\0\0\201~\1\0`\235 \0"..., 1032192, 787496960) = 1032192
     0.002132 pread(269, "\6\242\0\0\202\303@\10\177\235 \0\0\0\2\0043 \0\0\1\0\0\0\201~\1\0`\235 \0"..., 1032192, 410009600) = 1032192
     0.002214 pread(268, "\6\242\0\0\2x\1\10\202\235 \0\0\0\1\4d$\0\0\1\0\0\0\201~\1\0`\235 \0"..., 1032192, 788545536) = 1032192
     0.002334 pread(269, "\6\242\0\0\2\304@\10\205\235 \0\0\0\1\4N_\0\0\1\0\0\0\201~\1\0`\235 \0"..., 1032192, 411058176) = 1032192
...

There was never any asynchronous system call... always and only pread's asking the OS for multiple Oracle blocks.

#2. Direct Reads: direct path read

Reference #2 is where the Oracle foreground process was posting "direct path read" wait events and consuming lots of CPU resulting in a median SQL statement wall time of 5.654 seconds. The Oracle process was clearly NOT doing asynchronous IO by the fact that the Linux strace results looked nearly identical to the above buffered read results (#1) directly above. That is, the IO system calls where pread's and there were no asynchronous IO calls.

#3. No Wait Event: Asynchronous IO

Reference #3 is where the Oracle foreground process was posting NO wait events, only burning CPU resulting in a median SQL statement wall time of 5.714. Perhaps if my IO subsystem was busier, then I would have seen some direct path reads... but I did not.

Using my OSM rss.sql tool sampling every 1/16 of a second, I never once saw an Oracle wait event... only CPU consumption.

How did I know Oracle was doing synchronous or asynchronous IO? Look at the below Linux strace output from the Oracle foreground process.


$ strace -rp 60625
Process 60625 attached
     0.000251 io_getevents(140613531254784, 2, 128, {{0x7fe32019ec40, 0x7fe32019ec40, 1032192, 0}, {0x7fe32019f2a0, 0x7fe32019f2a0, 1032192, 0}}, {0, 0}) = 2
     0.001960 io_submit(140613531254784, 1, {{data:0x7fe32019ec40, pread, filedes:268, buf:0x7fe31fd41000, nbytes:1032192, offset:908083200}}) = 1
     0.002251 io_submit(140613531254784, 1, {{data:0x7fe32019f2a0, pread, filedes:269, buf:0x7fe31fe51000, nbytes:1032192, offset:531644416}}) = 1
     0.000245 io_getevents(140613531254784, 2, 128, {{0x7fe32019ec40, 0x7fe32019ec40, 1032192, 0}, {0x7fe32019f2a0, 0x7fe32019f2a0, 1032192, 0}}, {0, 0}) = 2
     0.002061 io_submit(140613531254784, 1, {{data:0x7fe32019ec40, pread, filedes:268, buf:0x7fe31fd41000, nbytes:1032192, offset:909131776}}) = 1
...

The io_submit (ref: HERE and HERE) is a giveaway that Oracle is doing asynchronous IO.

Yes. Asynchronous IO Was Slower Than Synchronous

I was shocked to see that in my experiment, the median SQL statement wall time of the synchronous IO run was 5.654 but for the asynchronous IO wall time was 5.714 seconds. You read correctly, asynchronous IO was slightly but statistically slower then synchronous IO. I ran the entire experiment twice just to verify this.

Keep in mind, this experiment was not designed to compare synchronous IO with asynchronous IO. For that, I would have ensured the elapsed was longer (greater than 15 seconds) and the Oracle blocks were physically read from a spinning disk. My quick observations showed the disks where less than 10% busy.

So, please do not expect synchronous IO to be slower than asynchronous IO. This experiment was not a fair comparison. This experiment was focused on comparing buffered and direct reads... period.

Experimental Setup And Analysis

For those of you who can't get enough of this good stuff, you will want to know a little about my experiment, see the data, the "R" script, the statistical results and of course the histograms!

You can download it all HERE.

This experiment was actually pretty simple. I set the instance parameters, recycled the Oracle instance, copy and pasted the experimental script into sqlplus and waited while 31 SQL statement run time samples were collected and presented to me via the glorious dbms_output.

While the SQL statements where running, I was observing its Oracle foreground process every 1/16 of second using my real time session sample, that is part of my OSM tool kit. This is how I verified the wait event or the absence of a wait event. Sometimes I also quickly ran a Linux strace on the foreground process to see if it was doing synchronous or asynchronous IO calls.

Visually, you can see in the above histogram there is a stark contrast in run times when the SQL statements were doing buffer reads (blue right plot) compared to direct reads (red and green left plot). Statistically the p-value is less than 0.000... which means it is extremely unlikely the difference is because we just happened to capture the "wrong" samples.

Real Time Session Sampler

My real time session sampler, which resides within my OSM toolkit, was instrumental in this experiment. It allowed me to quickly watch an Oracle session as it was running the SQL statement, flushing the buffer cache, sleeping and repeat. I have received quite a bit of feedback about my rss.sql tool, so I thought I should mention this.

A while back, I created a YouTube video how to use the script. Here it is:

While my realtime session sampler is not built for continuous sampling, the results were very interesting and enlightening as I watched an Oracle session through multiple frequent sampling. Give it a try. I think you'll get a lot out of it.

So, which is faster?

Direct reads or buffered reads? No matter how you look at the experimental results, direct reads are clearly faster than buffered reads.

It's easy to get confused with the mix of buffered, direct, synchronous and asynchronous reads. Remember that in my previous post I discussed this.

Enjoy the ride!

Craig.

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

Variable Load Impact On Oracle Database Hard Parsing Ouch! Log File Switch Checkpoint Incomplete Does Increasing An Oracle Database Background Process OS Priority Improve Performance?