A Case Study on the “Direct Path Reads” Event – Why Is It So Important for Oracle Exadata?

by

A Case Study on the “Direct Path Reads” Event – Why Is It So Important for Oracle Exadata?

As a DBA, you’ve probably heard of Oracle Smart Scan. Indeed, Smart Scan is one of the key secrets behind the exceptional processing speed within the Oracle Exadata Machine. There is also the concept of data offloading in the Exadata architecture, which refers to the fact that data processing is done at the storage layer rather than in the database layer. Smart Scan, on the other hand, is more focused at the SQL level rather than the data level like offloading. However, as seen in Oracle’s documentation, these two concepts were ultimately merged and commonly referred to simply as “Smart Scan.”

Despite all the processing speed the Exadata machine offers, it wouldn’t be what it is without the capability of Offloading and Smart Scan. Without these features, Exadata would just be another high-powered machine—lacking intelligent data processing. And as we can see, it’s precisely this intelligence that makes all the difference.

But what exactly does “Direct Path Reads” have to do with data offloading and Exadata Smart Scan? The answer is simple: Offloading and/or Smart Scan will not occur if your SQL statement does not use Direct Path Reads.

There are two basic prerequisites for Offloading/Smart Scan:

  1. The statement must be executed against Exadata storage.
  2. The statement must use Direct Path Reads.

Of course, some restrictions apply, but with these two requirements in place, your query will most likely trigger Smart Scan.

What Is Direct Path Reads?

Direct Path Reads was created by Oracle to bypass the Buffer Cache. The Buffer Cache, as you likely know, is an area in the SGA used to store recently read blocks so that all sessions connected to the instance can read and share this cache without having to reread blocks from disk. This results in a great performance gain, as disk access is avoided for each query execution. Over the years, Oracle has done an excellent job refining this cache through LRU and MRU algorithms (see: http://docs.oracle.com/cd/B28359_01/server.111/b28318/memory.htm#CNCPT1224).

While there are far more advantages than disadvantages to using the Buffer Cache, its automatic buffer management can be problematic. Oracle may end up loading “junk” into the cache and evicting blocks that are more frequently accessed by other sessions. Consider the example of a report that runs once a month to calculate period-end metrics and processes a huge volume of data—why would you want to place all that data in your buffer cache if the statement is run only once and won’t be shared with other sessions? That data may even exceed the cache size, causing excessive overhead from removing frequently used blocks to make space for data that will never be accessed again. It would require unnecessary work reallocating memory over and over.

That’s where Direct Path Reads comes in.

The Direct Path Reads mechanism has been available in Oracle’s kernel for quite a long time. It was initially implemented to work exclusively with slave processes when statements were executed using parallelism. Since parallel processes typically need to read large volumes of data, Direct Path Reads was introduced to bypass the standard buffer mechanism altogether. From that point on, blocks would be stored in the process’s own memory (PGA) rather than in the SGA when the query was executed via DPR.

According to My Oracle Support, starting from Oracle 11gR2, the kernel was modified to favor Direct Path Reads more frequently than in version 10g. In 10g, serial table scans were more likely to be executed using the shared buffer cache (scattered reads), whereas in 11gR2, they are more likely to use Direct Path Reads.

How to Identify the Direct Path Reads Event

There are several ways to identify the “Direct Path Reads” event. One of them is through Oracle’s wait event views, such as v$session_wait.

The v$session_wait view always shows the current wait event occurring for each session. Using the SQL below, we can identify, through the EVENT column, whether the statement (referenced by sql_id) is using the “direct path read” event:

SELECT  s.sid, w.state, w.event, s.sql_id, s.sql_child_number, 
        w.seq#, w.seconds_in_wait, w.p1text||'=  '||w.p1 p1,
        w.p2text||'= '||w.p2 p2, w.p3text||'=  '||w.p3 p3
FROM  v$session s, v$session_wait w 
WHERE  w.sid = s.sid AND w.sid = <your_sid>;

Let’s test this. From now on, we’ll use the hidden parameter _serial_direct_read to force the usage of Direct Path Reads. I’ll discuss this parameter further ahead—what matters now is that it allows us to force the use of Direct Path Reads.

The tests will use the table fss.hsk1, a test table I frequently use. It’s created under the FSS schema and contains about 4GB of data (you can adjust the size by changing the number of rows in the script). The scripts below can be used to reproduce the tests:

  • Create user FSS: fss_user.sql
  • Create FSS tables: fss_create_tables.sql

All tests were performed on Oracle 12c

Identifying Direct Reads via the v$session_wait View

Let’s start testing. In session 1, execute the following SQL on the fss.hsk1 table. First, set the hidden parameter _serial_direct_read to ALWAYS, forcing all queries to use Direct Path Reads:

SQL> ALTER SESSION SET "_serial_direct_read"=ALWAYS;

Session altered

SQL> SELECT AVG(LENGTH(col1) + LENGTH(col2)) FROM fss.hsk1 WHERE col3 > 1;

While the query is running in session 1, in session 2 we use v$session_wait to confirm the active wait event is direct path read:

SQL>  SELECT 
      1  s.sid, w.state, w.event, s.sql_id,  s.sql_child_number, w.seq# 
      2  FROM v$session s, v$session_wait w
      3  WHERE w.sid = s.sid AND w.sid=152;
          

SID       STATE               EVENT                        SQL_ID         CH#  SEQ#
--------  ------------------- ---------------------------- ------------- ----- -------
152       WAITED SHORT TIME   direct path read             36b84f5s2yj4a   0   36081

1 row  selected.

SQL> /

SID       STATE               EVENT                        SQL_ID         CH#  SEQ#
--------  ------------------- ---------------------------- ------------- ----- -------
152       WAITED SHORT TIME   direct path read             74kfrv5xqpbxf   0   52652

1 row  selected.

SQL> /

SID       STATE               EVENT                        SQL_ID         CH#  SEQ#
--------  ------------------- ---------------------------- ------------- ----- -------
152       WAITED SHORT TIME   direct path read             74kfrv5xqpbxf   0   56786

You should see the event direct path read appear.

Now go back to session 1 and change the hidden parameter _serial_direct_read to NEVER, then execute the same query again. Observe that now the wait event changes to db file scattered read, meaning the data is being loaded into the SGA:

SQL>  ALTER SESSION SET "_serial_direct_read"=NEVER;

Session  altered.

SQL>  select avg(length(col1) + length(col2)) from fss.hsk1 where col3 > 1;
 -->  SESSÃO 2

SQL>  SELECT 
      1  s.sid, w.state, w.event, s.sql_id,  s.sql_child_number, w.seq# 
      2  FROM v$session s, v$session_wait w
      3  WHERE w.sid = s.sid AND w.sid=152;

SID       STATE               EVENT                     SQL_ID         CH#  SEQ# 
--------  ------------------- ------------------------- ------------- ----- -------
152       WAITED SHORT TIME   db file scattered read    74kfrv5xqpbxf   0   23902

1 row  selected.

SQL> /

SID       STATE               EVENT                     SQL_ID         CH#  SEQ#
--------  ------------------- ------------------------- ------------- ----- -------
152       WAITED SHORT TIME   db file scattered read    74kfrv5xqpbxf   0   26483

1 row  selected.

SQL> /

SID       STATE               EVENT                     SQL_ID         CH#  SEQ#
--------  ------------------- ------------------------- ------------- ----- -------
152       WAITED SHORT TIME   db file scattered read    74kfrv5xqpbxf   0   26977

Use session 2 again to check the wait even – it should now be db file scattered read.

Identifying Direct Reads via Statistical Views

We can also identify the direct path event using the v$sesstat and v$mystat views. The v$sesstat view shows statistics for all database sessions, while v$mystat displays statistics only for the current session. Unlike the v$session_wait view, which shows the session’s current wait state, these statistical views are cumulative across all operations. In this context, the statistic called “table scans (direct read)” represents how many times the direct path event was used across all statements executed in that session.

Because the statistics in v$sesstat and v$mystat are cumulative, we need to compare the values before and after a given operation to determine whether there was an increase—i.e., if the session triggered the event.

We can perform this test as follows:

SQL>  col value format 999999999999999

SQL>  SELECT s.name, m.value
      2  FROM  v$mystat m, v$statname s
      3   WHERE m.statistic# = s.statistic#
      4  AND  s.name = 'table scans (direct read)';

NAME                                            VALUE
----------------------------------------------  ----------------
table  scans (direct read)                      0

1 row  selected.

As you can see above, the “table scans (direct read)” statistic for my session is currently zero. This indicates that the session has not yet performed any block reads using the Direct Path Reads event.

Now let’s change the hidden parameter _SERIAL_DIRECT_READ to NEVER in order to force a full table scan on the fss.hsk1 table without using Direct Path Reads

SQL>  ALTER SESSION SET "_serial_direct_read"=NEVER;
Session  altered.

SQL>  select avg(length(col1)) from fss.hsk1 where col3 > 1;

AVG(LENGTH(COL1))
-----------------
175.015666

1 row  selected.

After running the query, let’s check again the value of the “table scans (direct read)” statistic.

SQL>  SELECT s.name, m.value
   2  FROM  v$mystat m, v$statname s
   3  WHERE m.statistic# = s.statistic#
   4  AND  s.name = 'table scans (direct read)';   

NAME                                            VALUE
----------------------------------------------  ----------------
table  scans (direct read)                      0

1 row  selected.

You’ll notice that the statistic is still zero. Now let’s run the same SQL statement again, but this time forcing the use of Direct Reads.

SQL>  ALTER SESSION SET "_serial_direct_read"=ALWAYS;
Session  altered.

SQL>  select avg(length(col1)) from fss.hsk1 where col3 > 1;

AVG(LENGTH(COL1))
-----------------
175.015666

1 row  selected.

After executing the query, let’s once again check the value of the “table scans (direct read)” statistic.

SQL> SELECT s.name, m.value
     2  FROM  v$mystat m, v$statname s
     3   WHERE m.statistic# = s.statistic#
     4  AND  s.name = 'table scans (direct read)';

NAME                                             VALUE
-----------------------------------------------  ----------------
table  scans (direct read)                       1

As you can see, the statistic increased from 0 to 1. This happened because the statement was executed using Direct Path Reads. For each query that triggers the Direct Reads event, a value of 1 is added to the “table scans (direct read)” statistic. The same applies to the “index fast full scans (direct read)” statistic.

SQL>  select avg(length(col1)) from fss.hsk1 where col3 > 1;

AVG(LENGTH(COL1))
-----------------
175.015666

1 row  selected.

You’ll see once again that the “table scans (direct read)” statistic now shows a value of 2.

SQL>  SELECT s.name, m.value
      2  FROM  v$mystat m, v$statname s
      3   WHERE m.statistic# = s.statistic#
      4  AND  s.name = 'table scans (direct read)';

NAME                                            VALUE
----------------------------------------------  ----------------
table  scans (direct read)                      2

Identifying Direct Reads via Trace Event 10046

Another quick way to determine if your query uses db file scattered read instead of direct path reads is by enabling trace event 10046:

SQL>  ALTER SESSION SET EVENTS '10046 trace name context forever, level 12'

Session  altered.

With the session set to event 10046, let’s identify the session’s trace file.

SQL>  SELECT tracefile 
      2    FROM v$process WHERE addr = (
      3    SELECT paddr FROM v$session 
      4    WHERE sid = (SELECT sid FROM v$mystat  WHERE rownum < 2)
      5    );

TRACEFILE
--------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/dbtst/dbtst/trace/dbtst_ora_60173.trc

Using the same tests as above—where we forced the use of Direct Reads—the trace file should look like the listing below for the statement executed with _serial_direct_read set to ALWAYS.


WAIT  #140675437128128: nam='direct path read' ela= 780 file number=6 first dba=42624  block cnt=128 obj#=76837 
tim=1397656466688788
WAIT  #140675437128128: nam='direct path read' ela= 824 file number=6 first dba=42752  block cnt=128 obj#=76837 
tim=1397656466692249
WAIT  #140675437128128: nam='direct path read' ela= 831 file number=6 first dba=42880  block cnt=128 obj#=76837 
tim=1397656466696735
WAIT  #140675437128128: nam='direct path read' ela= 757 file number=6 first dba=43008  block cnt=128 obj#=76837 
tim=1397656466701094
WAIT  #140675437128128: nam='direct path read' ela= 765 file number=6 first dba=43136  block cnt=128 obj#=76837 
tim=1397656466705783
WAIT  #140675437128128: nam='direct path read' ela= 574 file number=6 first dba=43268  block cnt=124 obj#=76837 
tim=1397656466708691
WAIT  #140675437128128: nam='direct path read' ela= 590 file number=6 first dba=43392  block cnt=128 obj#=76837 
tim=1397656466711190
WAIT  #140675437128128: nam='direct path read' ela= 568 file number=6 first dba=43520  block cnt=128 obj#=76837 
tim=1397656466713200
WAIT  #140675437128128: nam='direct path read' ela= 610 file number=6 first dba=43648  block cnt=128 obj#=76837 
tim=1397656466715460
WAIT  #140675437128128: nam='direct path read' ela= 562 file number=6 first dba=43776  block cnt=128 obj#=76837 
tim=1397656466718398
WAIT  #140675437128128: nam='direct path read' ela= 524 file number=6 first dba=43904  block cnt=128 obj#=76837 
tim=1397656466720576

WAIT  #140675437128128: nam='direct path read' ela= 489 file number=6 first dba=44032  block cnt=128 obj#=76837 
tim=1397656466723296
WAIT  #140675437128128: nam='direct path read' ela= 792 file number=6 first dba=44160  block cnt=128 obj#=76837 
tim=1397656466726823
WAIT  #140675437128128: nam='direct path read' ela= 726 file number=6 first dba=44292  block cnt=124 obj#=76837 
tim=1397656466731733
WAIT  #140675437128128: nam='direct path read' ela= 782 file number=6 first dba=44416  block cnt=128 obj#=76837 
tim=1397656466736128
WAIT  #140675437128128: nam='direct path read' ela= 786 file number=6 first dba=44544  block cnt=128 obj#=76837 
tim=1397656466740659
WAIT  #140675437128128: nam='direct path read' ela= 621 file number=6 first dba=44672  block cnt=128 obj#=76837 
tim=1397656466743702
WAIT  #140675437128128: nam='direct path read' ela= 808 file number=6 first dba=44800  block cnt=128 obj#=76837 
tim=1397656466747454
WAIT  #140675437128128: nam='direct path read' ela= 568 file number=6 first dba=44928  block cnt=128 obj#=76837 
tim=1397656466751477
WAIT  #140675437128128: nam='direct path read' ela= 553 file number=6 first dba=45056  block cnt=128 obj#=76837 
tim=1397656466753675
WAIT  #140675437128128:  nam='direct path read' ela= 579 file number=6 first dba=45184 block cnt=128  obj#=76837 
tim=1397656466758527
WAIT  #140675437128128: nam='direct path read' ela= 610 file number=6 first dba=45316  block cnt=124 obj#=76837 
tim=1397656466761760
WAIT  #140675437128128: nam='direct path read' ela= 768 file number=6 first dba=45440  block cnt=128 obj#=76837 
tim=1397656466765429
WAIT  #140675437128128: nam='direct path read' ela= 751 file number=6 first dba=45568  block cnt=128 obj#=76837 
tim=1397656466768958
WAIT  #140675437128128: nam='direct path read' ela= 757 file number=6 first dba=45696  block cnt=128 obj#=76837 
tim=1397656466772449
FETCH #140675437128128:c=342947,e=1194482,p=45285,cr=45289,cu=0,mis=0,r=1,dep=0,og=1,plh=3450470040,
tim=1397656466776035
STAT  #140675437128128 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=45289  pr=45285 pw=0 time=1194103 us)'
STAT  #140675437128128 id=2 cnt=799999 pid=1 pos=1 obj=76837 op='TABLE ACCESS FULL  HSK1 (cr=45289 pr=45285 pw=0 
time=1363830 us cost=12370 size=145598544  card=799992)'
WAIT  #140675437128128: nam='SQL*Net message from client' ela= 1640 driver  id=1413697536 #bytes=1 p3=0 obj#=76837 
tim=1397656466782990
FETCH #140675437128128:c=0,e=223,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=3450470040,tim=1397656466785440
WAIT  #140675437128128: nam='SQL*Net message to client' ela= 4 driver id=1413697536  #bytes=1 p3=0 obj#=76837 
tim=1397656466788033

Now, running the same statement without using Direct Reads should result in the following trace:

(Note the large number of “direct path read” events)

Identifying Direct Reads via Oracle Internals – For Oracle Geek Guys Only 🙂

The Direct Path Reads event is implemented through an operating system function called kcbldrget, which stands for Kernel Block Direct Read Get.

To verify whether Direct Path Reads are being used, you can check if the function kcbldrget was triggered by the operating system process. On Linux systems, this is possible using the pstack command, which shows the execution stack trace of a running process.

Using the SPID of the session executing a SQL statement with _serial_direct_read set to ALWAYS, run the pstack command from the OS (in this case, Linux), pointing it to the session’s SPID (e.g., 50834):


[root@oralnx001  ~]# pstack 50834
#0  0x0000003f1960ee33 in __pread_nocancel ()  from /lib64/libpthread.so.0
#1  0x00000000093521fb in skgfqio ()
#2  0x0000000009222e03 in ksfd_skgfqio ()
#3  0x0000000009222b68 in ksfdgo ()
#4  0x000000000234e30e in ksfdaio ()
#5  0x00000000021ef424 in kcflbi ()
#6  0x0000000000ebc90a in kcbldio ()
#7  0x0000000000ebba84 in kcblrs ()
#8  0x0000000000ebb165 in kcblgt ()
#9  0x0000000000eb9941 in kcbldrget () 
#10 0x000000000907b554 in kcbgtcr ()
#11 0x000000000905ff29 in ktrget3 ()
#12 0x000000000905f784 in ktrget2 ()
#13 0x0000000009016ead in kdst_fetch ()
#14 0x0000000000c87f89 in kdstf00000010000kmP ()
#15 0x0000000008ffc6e8 in kdsttgr ()
#16 0x0000000009245970 in qertbFetch ()
#17 0x000000000926cc1f in qergsFetch ()
#18 0x0000000009136e83 in opifch2 ()
#19 0x00000000091404e8 in opiefn0 ()
#20 0x000000000914dfc4 in opipls ()
#21 0x000000000913d4d4 in opiodr ()
#22 0x00000000091e7043 in rpidrus ()
#23 0x0000000009354764 in skgmstack ()
#24 0x00000000091e8b5e in rpiswu2 ()
#25 0x00000000091e8188 in rpidrv ()
#26 0x00000000091d14d1 in psddr0 ()
#27 0x00000000091d10e7 in psdnal ()
#28 0x0000000003736b52 in pevm_EXIM ()
#29 0x000000000372831b in pfrinstr_EXIM ()
#30 0x00000000093eae35 in pfrrun_no_tool ()
#31 0x00000000093e9509 in pfrrun ()
#32 0x00000000093f0b61 in plsql_run ()
#33 0x000000000371cb6b in peicnt ()
#34 0x0000000002fa18b1 in kkxexe ()
#35 0x00000000091450f9 in opiexe ()
#36 0x0000000001b5cb07 in kpoal8 ()
#37 0x000000000913d4d4 in opiodr ()
#38 0x00000000092e02d6 in ttcpip ()
#39 0x00000000017ece01 in opitsk ()
#40 0x00000000017f19fa in opiino ()
#41 0x000000000913d4d4 in opiodr ()
#42 0x00000000017e8d3c in opidrv ()
#43 0x0000000001de40cb in sou2o ()
#44 0x0000000000a0b0c1 in opimai_real ()
#45 0x0000000001dea03c in ssthrdmain ()
#46 0x0000000000a0b02d in main ()

As mentioned earlier, starting with Oracle 11g, it is much more likely that your query will automatically use Direct Path Reads. However, there are some ways to anticipate whether a query will actually trigger the event.

In addition to parallel slave processes—which always run using Direct Path Reads—serial statements may also use it under specific conditions. The decision is based on several factors, such as the number of blocks in the object and the size of the buffer cache. There’s also a hidden parameter called _SMALL_TABLE_THRESHOLD, which defines the minimum number of blocks a table must have in order to be eligible for Direct Path Reads. The default value for this parameter is 2680, which means that if a table has more than 2680 blocks, a full table scan is more likely to use Direct Path Reads.


[root@oralnx001  ~]# pstack 50834
#0  0x0000003f1960ee33 in __pread_nocancel ()  from /lib64/libpthread.so.0
#1  0x00000000093521fb in skgfqio ()
#2  0x0000000009222e03 in ksfd_skgfqio ()
#3  0x0000000009222b68 in ksfdgo ()
#4  0x000000000234e30e in ksfdaio ()
#5  0x00000000021ef424 in kcflbi ()
#6  0x0000000000ebc90a in kcbldio ()
#7  0x0000000000ebba84 in kcblrs ()
#8  0x0000000000ebb165 in kcblgt ()
#9  0x0000000000eb9941 in kcbldrget () 
#10 0x000000000907b554 in kcbgtcr ()
#11 0x000000000905ff29 in ktrget3 ()
#12 0x000000000905f784 in ktrget2 ()
#13 0x0000000009016ead in kdst_fetch ()
#14 0x0000000000c87f89 in kdstf00000010000kmP ()
#15 0x0000000008ffc6e8 in kdsttgr ()
#16 0x0000000009245970 in qertbFetch ()
#17 0x000000000926cc1f in qergsFetch ()
#18 0x0000000009136e83 in opifch2 ()
#19 0x00000000091404e8 in opiefn0 ()
#20 0x000000000914dfc4 in opipls ()
#21 0x000000000913d4d4 in opiodr ()
#22 0x00000000091e7043 in rpidrus ()
#23 0x0000000009354764 in skgmstack ()
#24 0x00000000091e8b5e in rpiswu2 ()
#25 0x00000000091e8188 in rpidrv ()
#26 0x00000000091d14d1 in psddr0 ()
#27 0x00000000091d10e7 in psdnal ()
#28 0x0000000003736b52 in pevm_EXIM ()
#29 0x000000000372831b in pfrinstr_EXIM ()
#30 0x00000000093eae35 in pfrrun_no_tool ()
#31 0x00000000093e9509 in pfrrun ()
#32 0x00000000093f0b61 in plsql_run ()
#33 0x000000000371cb6b in peicnt ()
#34 0x0000000002fa18b1 in kkxexe ()
#35 0x00000000091450f9 in opiexe ()
#36 0x0000000001b5cb07 in kpoal8 ()
#37 0x000000000913d4d4 in opiodr ()
#38 0x00000000092e02d6 in ttcpip ()
#39 0x00000000017ece01 in opitsk ()
#40 0x00000000017f19fa in opiino ()
#41 0x000000000913d4d4 in opiodr ()
#42 0x00000000017e8d3c in opidrv ()
#43 0x0000000001de40cb in sou2o ()
#44 0x0000000000a0b0c1 in opimai_real ()
#45 0x0000000001dea03c in ssthrdmain ()
#46 0x0000000000a0b02d in main ()

Conditions for Using Direct Path Reads

As mentioned earlier, starting with Oracle 11g, it is much more likely that your query will automatically use Direct Path Reads. However, there are some ways to anticipate whether a query will actually trigger the event.

In addition to parallel slave processes—which always run using Direct Path Reads—serial statements may also use it under specific conditions. The decision is based on several factors, such as the number of blocks in the object and the size of the buffer cache.

There’s also a hidden parameter called _SMALL_TABLE_THRESHOLD, which defines the minimum number of blocks a table must have in order to be eligible for Direct Path Reads. The default value for this parameter is 2680, which means that if a table has more than 2680 blocks, a full table scan is more likely to use Direct Path Reads.

See the example below:

SQL>  show parameter _small_table_threshold

NAME                           VALUE
------------------------------ -------
_small_table_threshold         2680

As shown above, the hidden parameter _SMALL_TABLE_THRESHOLD is set to its default value of 2680 blocks. Now I’ll create a table by selecting all the data from the dba_objects view.

SQL>  create table t as select * from dba_objects;

Table  created.

SQL>  select blocks from dba_segments where segment_name='T';

BLOCKS
----------
1152

Notice that our table ended up with 1,152 blocks, which is well below the _small_table_threshold value. Let’s perform a full table scan on this table and check whether Direct Path Reads were automatically triggered.

SQL>  select s.name, m.value 
2    from v$statname s, v$mystat m 
3    where m.statistic#=s.statistic# 
4    and s.name = 'table scans (direct read)';

NAME                                              VALUE
------------------------------------------------  ----------
table  scans (direct read)                        0

1 rows  selected.

SQL>  select count(*) from t;

COUNT(*)
----------
75214

1 row  selected.

SQL>  select 
2    s.name, m.value 
3    from v$statname s, v$mystat m 
4    where m.statistic#=s.statistic# 
5    and s.name = 'table scans (direct read)';

NAME                                               VALUE
-------------------------------------------------  ----------
table  scans (direct read)                         0

1 rows  selected.

As seen above, the table scans (direct read) statistic was not incremented. Let’s now increase the number of blocks in the table and run the same test again

SQL>  insert into t (select * from t);

75214  rows created.

SQL>  insert into t (select * from t);

150428  rows created.

SQL>  commit;

Commit  complete.

SQL>  select blocks from dba_segments where segment_name='T';

BLOCKS
----------
4352

1 row selected.

Now the table has more blocks than the threshold defined by _small_table_threshold. Before performing the new test, let’s flush Oracle’s memory to ensure the previous test doesn’t affect the results.

SQL>  ALTER SYSTEM FLUSH BUFFER_CACHE;

System  altered.

SQL>  ALTER SYSTEM FLUSH SHARED_POOL;

System  altered.

After flushing the memory, let’s run the same query again.

SQL>  select count(*) from t;

COUNT(*)
----------
300856

1 row  selected.

SQL>  select 
      2      s.name, m.value 
      3      from v$statname s, v$mystat m 
      4      where m.statistic#=s.statistic# 
      5      and s.name = 'table scans (direct read)';

NAME                                            VALUE
----------------------------------------------  ----------
table  scans (direct read)                      1

1 row  selected.

Now the query was indeed executed using Direct Path Reads.

Another very important point about Direct Reads is that they only occur when a full scan is performed. In other words, the function direct path reads (kcbldrget) is only invoked after a full scan. Keep in mind that full scans refer to operations such as TABLE ACCESS FULL and INDEX FAST FULL SCAN in the execution plan. Therefore, if your query performs a UNIQUE SCAN, Direct Path Reads will not be triggered.

Parallel Processes and Direct Path Reads

Parallel processes are another important aspect. As mentioned earlier, it doesn’t matter what type of parallel execution you’re using (AUTO DOP, IN MEMORY PARALLEL, QUEUEING PARALLEL, etc.) or what degree of parallelism is defined—any time your query performs a full scan in parallel mode, it will use Direct Path Reads.

See the example below: even when the _serial_direct_read parameter is set to NEVER, a parallel query will still make use of Direct Reads.

SQL>  ALTER SESSION SET "_serial_direct_read"=never;

Session  altered.

SQL>  select 
2     s.name, m.value 
3     from v$statname s, v$mystat m 
4     where m.statistic#=s.statistic#  
5     and s.name = 'table scans (direct read)';

NAME                                            VALUE
----------------------------------------------  ----------
table  scans (direct read)                        0

1 row selected.

SQL> select count(*) from t;

COUNT(*)
----------
300856
 
1 row selected.

SQL>  select 
      2    s.name, m.value 
      3    from v$statname s, v$mystat m 
      4    where m.statistic#=s.statistic#  
      5    and s.name = 'table scans (direct read)';

NAME                                             VALUE
-----------------------------------------------  --------
table  scans (direct read)                         0

1 row  selected.

SQL> alter table t parallel 2;

Table altered.

SQL> select count(*) from t;

COUNT(*)
----------
300856

1 row  selected.

SQL>  select 
2     s.name, m.value 
3     from v$statname s, v$mystat m 
4     where m.statistic#=s.statistic#  
5     and s.name = 'table scans (direct read)';

NAME                                               VALUE
------------------------------------------------  --------
table  scans (direct read)                          26
 
1 row  selected.

As you can see, parallel processes completely ignore the _serial_direct_read parameter.

A Bit About the _serial_direct_read Parameter

By now, you probably have a good understanding of how the hidden _serial_direct_read parameter works. It either forces or disables the use of Direct Path Reads. It’s worth noting that since this is a hidden parameter, it is strongly recommended not to set it in a production environment without first consulting Oracle Support.

Starting from Oracle 11g, the _serial_direct_read parameter supports the following values:

  1. ALWAYS
  2. AUTO
  3. NEVER
  4. TRUE
  5. FALSE

Each of these values controls the behavior of Direct Path Reads differently. The default is AUTO, which means Oracle will decide automatically based on internal logic, such as the number of blocks and other resource-related factors.

According to the Oracle support document Exadata Smartscan Is Not Being Used On Insert As Select (Doc ID 1348116.1), Oracle describes a scenario where Smart Scan is not triggered because the statement is not executed using Direct Path Reads (one of the most common causes of poor Exadata performance). In that note, Oracle recommends setting _serial_direct_read to TRUE as a solution to the problem.

Leave a Comment