Do Direct Path Reads Count As Logical Reads?

Posted on 4-Dec-2018 by Craig Shallahamer / OraPub / craig@orapub.com

This blog will answer the question, "Do direct path reads count as logical reads?”

The answer is important because we use and talk about logical LIOs, session logical reads, buffer gets, direct path reads, physical reads, etc. all the time.

But do we really know what these statistics mean? And, how can we use them?

The answers to these questions, if the focus of this post.

Do Not Look At The Doc

Forget about the documentation or what you have been told. Let's check this out for ourselves. This is how you build confidence... test it.

To answer this question, we need to run a repeatable experiment. So, that is what we are going to do!

This is one of the situations, where experimental results end the conversation... good fun!

What Is A Read?

In Oracle terms, unless additional words are included, each read refers to a single Oracle block. This means that any read statistic is not about an tablespace, segment or extent. It always refers a single Oracle block.

For example, the statistic physical reads refers to a single block being physical read. In contrast, the statistic physical read bytes refers the bytes being read associated with the blocks read. (I will provide detail below.)

In fact, I think a better name for an Oracle read could be a block read.

As we will see, there are different kinds of Oracle block reads and their differences is where this story begins to get interesting.

Oracle Read Statistics

In Oracle 12.1 there are 85 statistics that contain the word read.

SQL> l
  1  select count(name)
  2  from   v$sysstat
  3* where  name like '%read%'
SQL> /

COUNT(NAME)
-----------
	 85

In this post, I will focus on only four!

STAT_NAME                                MYSTAT_DELTA
---------------------------------------- ------------
session logical reads                          354708
physical reads                                 354289
physical reads cache                                0
physical reads direct                          354289

My objective in this post, is to demonstrate what each of these five statistics represent and also how they relate to each other.

All The Words We Use

Because Oracle is a mature yet evolving product with a diverse set of development groups, there are multiple words or terms used to describe the same thing. Here is an example.

Instance/system statistic: session logical reads
Instance/system statistic: physical reads
Instance/system statistic: physical reads cache
Instance/system statistic: physical reads direct
AWR Report Load Profile  : Logical Reads   
Common term              : buffer gets
Common term              : logical IOs, LIOs, lios, lio
AWR Report SQL Statistics: SQL ordered by Gets
Wait Event               : direct path read

What do these words and terms mean, and how do they relate to each other? Read on.

Experiment: Prepare the environment

For this experiment I want to create a clean and reproducible environment. So, I am going to disable the resource manager, encourage Oracle do direct path reads, disable parallel query and ensure async IO is enabled.

To save space and time, I'll make this quick.

To disable the resource manager, I ran the command, alter system set resource_manager_plan = '';.

To encourage Oracle to do direct path reads, I set the instance parameter, _small_table_threshold = 10. Setting small table threshold to 999999 will discourage Oracle from doing direct path reads.

To disable parallel query, while there are many ways to accomplish this task, I set the instance parameter, parallel_max_servers = 0.

Finally, to ensure async IO is enabled, I set the instance parameters, filesystemio_options=ASYNCH and disk_asynch_io=true.

If I really want to know if async IO is operational, I do a operating system trace on an Oracle foreground process or one of the database writers, looking at the actual IO call. The details about this, is out of scope for this post. My point is, simply setting a parameter guarantees only, that you set the parameter.

Experiment: The Script

There are many different ways to crack this nut so, you perhaps you would have written the below script differently.

I am not going to detail the script. For this post, it will be a distraction.

To run this, I simply connected to Oracle in SQL*Plus as system, copied the text below and pasted it. Really... that's all I did.

-----------------------------------
-- COPY - START
-----------------------------------

drop table bogus_dpr_event;
create table bogus_dpr_event as select * from v$session_event where 1=0;

drop table bogus_dpr_sesstat;
create table bogus_dpr_sesstat as select * from v$mystat where 1=0;

set pagesize 60
set linesize 500
set tab off
set pagesize 100
set linesize 500

variable sid number
begin
  select distinct sid into :sid from v$mystat;
end;
/
select :sid from dual;

show parameter parallel_max_servers
show parameter _small_table_threshold

truncate table bogus_dpr;

truncate table bogus_dpr_sesstat;

insert into bogus_dpr_event select * from v$session_event where sid = :sid;

insert into bogus_dpr_sesstat select * from v$mystat;

-- I want the SQL to run a few seconds and I want some physical reads.
-- Usually these two tables combined with a small buffer cache does the trick.
--
--select count(*) from dba_hist_active_sess_history;
select count(*) from dba_extents;

col event_name format a35
select event_end.event event_name,
       event_end.total_waits-event_start.total_waits event_waits_delta
from   bogus_dpr_event event_start,
       v$session_event event_end
where  event_start.sid   = :sid
  and  event_start.sid   = event_end.sid
  and  event_start.event = event_end.event
  and  event_end.total_waits-event_start.total_waits > 0
/

col stat_name format a40
select sname.name stat_name,
       ms_end.value-ms_start.value mystat_delta
from   bogus_dpr_sesstat ms_start,
       v$mystat          ms_end,
       v$statname        sname
where  ms_end.sid          = :sid
  and  ms_start.sid        = ms_end.sid
  and  ms_start.statistic# = ms_end.statistic#
  and  ms_end.statistic#   = sname.statistic#
  and  (   sname.name = 'physical reads'
        or sname.name = 'physical reads cache'
        or sname.name = 'physical reads direct'
        or sname.name = 'session logical reads'
       )
/

-----------------------------------
-- COPY - END
-----------------------------------

Here is an example of the output, excluding the wait event details. For this post, I am focusing only on the system/instance statistics.

STAT_NAME                                MYSTAT_DELTA
---------------------------------------- ------------
session logical reads                          355409
physical reads                                 354332
physical reads cache                               89
physical reads direct                          354243

4 rows selected.

In the next section, I will explain the results.

Experiment: The Numbers

If you look at the output just above, there are four statistic we need to understand to meet our objective. But first, I want to show you a few more experimental runs. After only a few runs, the pattern becomes crystal clear.

From dba_hist_active_sess_history:

STAT_NAME                                MYSTAT_DELTA
---------------------------------------- ------------
session logical reads                          355409
physical reads                                 354332
physical reads cache                               89
physical reads direct                          354243

4 rows selected.

From dba_hist_active_sess_history:

STAT_NAME                                MYSTAT_DELTA
---------------------------------------- ------------
session logical reads                          354651
physical reads                                 354243
physical reads cache                                0
physical reads direct                          354243

4 rows selected.

From dba_extents:

STAT_NAME                                MYSTAT_DELTA
---------------------------------------- ------------
session logical reads                           77412
physical reads                                  12572
physical reads cache                            12572
physical reads direct                               0

4 rows selected.

From dba_extents:

STAT_NAME                                MYSTAT_DELTA
---------------------------------------- ------------
session logical reads                           75348
physical reads                                  12153
physical reads cache                            12153
physical reads direct                               0

4 rows selected.

Experiment: Understanding Physical Reads

After only a minute or two, you will notice that the physical reads equals the physical reads cache plus the physical reads direct. It's like this:

physical reads = physical reads cache + physical reads direct

So, let's define a few terms:

Physical reads cache increments when an Oracle foreground process physically reads a block from an Oracle DBF file as a result of an IO subsystem call and places the block into Oracle's buffere cache. Simply put, Oracle reads a block from disk and put into the buffer cache.

Physical reads direct increments when an Oracle foreground process physically reads a block from an Oracle DBF file as a result of an IO subsystem call and places the block into the foreground process's memory (i.e., its PGA or process global area). Simply put, Oracle reads a block from disk and put into the foreground process's PGA.

Cached Versus Direct

The differences are profound and can be quickly understood.

Cached Read

If a block is placed into the buffer cache, the negative is Oracle kernel code must be run, which includes significant memory management which includes serialization control. ...lots of room for concurrency issues to arise.

The positive is while one process brings the block into the cache, now other processes can access the block from the cache, avoiding an additional physical read. That is, the block is cached which avoids a future physical read.

Direct Read

If a block is not placed into the buffer cache, the negative is no other process can benefit from the read because it was not placed into the buffer cache. This can result in Oracle processes physically re-reading the same block from disk. This is why a direct read is sometimes called a selfish read because the block is not shared.

The positive is a ton of Oracle kernel code and memory serialization control is avoided because there is no sharing of blocks or buffers. For large full table scans, Oracle will more likely choose to do a direct path read.

The Break Even

You may ask, "Why doesn't Oracle just do direct path reads?" Two reasons really.

The first is the block, by already being in the buffer cache, avoids an additional physical IO read call. Second, at some point, as the number of processes doing direct reads increases, the IO subsystem will be overwhelmed with IO requests, negating the benefit of avoiding the buffer cache.

Simply put, a few selfish process are OK, but a lot of selfish processes ruins the fun for everyone.

Experiment: Understanding Session Logical Reads

The goal of this post is to understand what a session logical read really means.

The tension or uneasiness comes from the word logical. I personally struggle with this, because I think in terms of physical and logical. There are physical designs and there are logical designs... so I get caught in that.

But in my experiment, without exception there are always more logical reads than physical reads. This supports what I have been taught that every block read from the IO subsystem counts as a physical read. And, whenever a block is touched in Oracle memory it counts as a logical read. And, each re-touch also counts as a logical read.

The re-touch is key to understand the stats. While this experiment does not clearly demonstrate this, if a query does 10 physical reads, there will be at least 10 logical reads. Why? Because each block read from the IO sysystem counts as a physical read and each block touched in Oracle memory counts as a logical read.

It is also common for SQL statements re-touch/access a buffer. This is why you can see 10 physical reads but 12 logical reads.

Here is the formula:

session logical reads = cache touches
session logical reads = physical reads + cache re-touches

Don't Forget About Dictionary Cache Access

Finally, don't forget about data dictionary access. Data dictionary blocks are highly likely to already be in the data dictionary's cache (called the row cache, which resides within the SGA's Shared Pool). If a data dictionary block is touched that counts as a logical read.

Below are two of the experimental results from above. Look at the difference between the logical reads and the physical reads below.

From dba_hist_active_sess_history:

STAT_NAME                                MYSTAT_DELTA
---------------------------------------- ------------
session logical reads                          354651
physical reads                                 354243
physical reads cache                                0
physical reads direct                          354243

4 rows selected.


From dba_extents:

STAT_NAME                                MYSTAT_DELTA
---------------------------------------- ------------
session logical reads                           75348
physical reads                                  12153
physical reads cache                            12153
physical reads direct                               0

4 rows selected.

The above dba_extents stats makes sense, because dba_extents is referencing a ton of data dictionary information and in this case, most of it is already in the dictionary cache. Plus, all the dba_extent blocks are already in the buffer cache... no direct reads! In contrast, the ASH table is focused on non-data dictionary data and doing 100% direct reads compared to cache reads. Pretty cool, eh?

Summary: The Final Definitions

Our core question is, Do Direct Path Reads Count As Logical Reads? Based on our experiment, the answer is a big YES! Direct path reads do count as logical reads. And, they also count as physical reads.

I need to mention there is other activity and their associated statistics that count as a physical read, but my experiment did not test or focus on this.

A physical read is the number of blocks read from the IO subsystem and placed into Oracle memory.

The key question is, which area of Oracle memory is the read block being placed? If it's a physical read cache then the block read from disk is placed into the buffer cache. In contrast, if it's a physical reads direct then the block from disk is placed into an Oracle process's PGA.

Based on our experimental results, these are the core equations.

physical reads        = physical reads cache + physical reads direct + other
session logical reads = Oracle memory touches
session logical reads = physical reads + Oracle memory re-touches
session logical reads = block in buffer cache touches + block in PGA touches

Remember all those similar but different read related names? Well, it turns out there are many acceptable and commonly used names to describe a session logical read. Sometimes a session logical read is referred to as a logical IO, LIO, buffer get, get or perhaps something else.

All the best in your Oracle tuning work,

Craig.

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

Does Table Column Order Affect SELECT Performance? The Importance of Oracle Database Process CPU Consumption Where Does Oracle Database View v$osstat Get It's Data? Trustworthy?