What's The Difference Between Direct Path Reads And DB File Scattered Reads?
Oracle database direct path reads are supposed to be faster than db file scattered reads. But are direct reads really faster?
When you think about it, why would we expect them to be faster? What is so different between the two? If direct reads are faster, then I'd like to know how much faster? Just a little faster or an amazing "wow" faster?
The answers to these questions is what this article series is all about.
This article focuses on the difference between the wait events and the next post will focus on the "faster" question based on experimental results.
Buffered Versus Non-Buffered Reads
After an Oracle process discovers (think: optimizer) it needs to access a bunch of blocks, the process embarks on an amazing journey. Part of this journey is deciding to do either "buffered" or "non-buffered" reads.
It's pretty simple actually. A buffered read means the Oracle process gets the blocks from the IO subsystem, places them into Oracle's buffer cache and then access their contents. A non-buffered read means the Oracle process gets the blocks from the IO subsystem and then accesses their contents.
Did you notice the non-buffered read "skipped" a step? Non-buffered reads do not place the blocks into Oracle's buffer cache. Understanding the implications is important to understand.
Remember, that regardless of how fast the IO subsystem is or if the Oracle blocks are in some non-Oracle cache, it is always faster to access blocks that already reside in the buffer cache. Why? Because there is Oracle kernel code involved with placing blocks into the buffer cache that is not executed if the blocks are already in the cache. If you want dispute this, please email me real data.
Once the decision to get blocks from the IO subsystem is made, Oracle will choose to either place the blocks into the buffer cache before accessing them (buffer read) or simply process the buffers when they become available from the IO subsystem (non-buffered read).
A non-buffered read is also called a direct read. From an Oracle wait event perspective, a direct read is named a "direct path read" for non-temporary segments and for temporary segments, "direct path read temp". For a buffered read Oracle assigns the name "db file scattered read".
But gets even better...
Buffering Is Better, Right?
Buffered reads are always better because it's faster to read blocks from cache instead of disk, right? Well, it's more complicated than this.
If a block is only going to be accessed once, then placing them into the buffer cache is a waste of resources. Also, if a process is going to replace say 20% of the buffer cache with its blocks, will that negatively affect other processes?
If buffered reads are likely to disrupt other Oracle process performance, then perhaps its better to do a direct read. Not only does this have the potential to be faster for the process but it has the potential to cause less negative impact on other processes.
But it gets even more interesting...
Suppose two Oracle sessions need to full-table scan the same table. Or how about a single session that needs to do multiple full-table scans (think: batch processing) on the same table!
A non-buffered asynchronous direct read, forces other Oracle processes to re-read the same blocks from the IO subsystem. Why? Because those non-buffered blocks are not placed in Oracle's buffer cache. So, while a non-buffered read should be able to process a full-table scan faster if all the blocks are not cached, it can result in more total IO activity.
But this is a tricky balancing act. It always has been. Generally, the Oracle optimizer will choose to do a non-buffered read if a the Oracle process is doing a full table scan or is going to replace a significant portion of the buffer cache with this single table. Oracle's touch count algorithm also helps to reduce the cache disruption.
Besides the buffered versus non-buffered consideration, the optimizer must also choose between either a synchronous or asynchronous read. What's that? Read on...
Synchronous Versus Asynchronous
If an Oracle process needs to read multiple blocks from the IO subsystem, like when it does a full-table scan, in addition to the buffer and non-buffered decision, the Oracle process must decide to do either synchronous or asynchronous IO requests.
While the difference between synchronous and asynchronous IO calls is significant, they it can be summarized this like this.
A synchronous read is a sequential process. A batch of blocks are requested from the IO subsystem and the process waits until the entire batch is returned and then processes them. Repeat. For sure, there will always be Oracle wait time and CPU consumption. Synchronous reads makes sense for single block reads, but for a full-table scan...no.
In contrast, an asynchronous read is a parallel process because the Oracle process issues the IO read requests in batches of blocks but then keeps checking to see if the any of the blocks have been returned. If a block is returned, the Oracle process will attempt to process it. If there are no blocks to process, the Oracle process may submit another set of blocks. Notice, that at this point there has been no wait time, only CPU consumption.
However, if the Oracle process for any reason can not submit any more blocks and there are no returned blocks to process, the Oracle process has no choice but to wait a bit and then re-check to see if there are blocks to process. So, it is possible asynchronous reads will NOT have any associated wait time. In fact, that is one of the objectives and benefits of asynchronous reads!
We Have Some Influence
As Oracle DBAs, we do have some control of what it means to "read a significant portion" of the buffer cache. There are two control levers we have. The first is event 10949 and the second is the instance parameter _small_table_threshold. Others have blogged about both of these and I have blogged about it HERE. While we can influence Oracle's direct versus buffered read decision, Oracle may choose to ignore our request. ...typical.
So, Which Is Faster?
What seems like such a simple choice is now actually quite complicated. There are essentially four choices (for each Oracle segment); synchronous buffered read, synchronous non-buffered read, asynchronous buffered read and an asynchronous non-buffered read. So, which is faster?
You are busy and so am I. For this reason, I'm going to cut this post off here. In my next post, I will answer the question based on experimental data.
Enjoy the ride!
Start my FREE 18 lesson Machine Learning For Oracle Professionals E-Course here.
Craig Shallahamer is a long time Oracle DBA who specializes in predictive analytics, machine learning and Oracle performance tuning. Craig is a performance researcher and blogger, consultant, author of two books, an enthusiastic conference speaker a passionate teacher and an Oracle ACE Director. More about Craig Shallahamer...
If you have any questions or comments, feel free to email me directly at craig at orapub.com.
|Oracle Database SQL Statement Elapsed Times||Part 2. Does Table Column Order Affect Performance?||Inferring SQL Run Time Using ASH And PQ Program Filtering|