Oracle Scratchpad

October 8, 2020

Direct Path

Filed under: Infrastructure,Oracle,Performance,Troubleshooting,Tuning — Jonathan Lewis @ 12:29 pm BST Oct 8,2020

This is a little addendum to a note I wrote a couple of days ago about serial direct path reads and KO (fast object checkpoint) enqueue waits.

The original note was prompted by a problem where someone had set the hidden parameter “_serial_direct_read” to ‘always’ because there were running 11g and wanted some “insert as select” statements to use direct path reads on the select portion and 11g wasn’t co-operating.

Serial direct path reads were introduced as a possibility in (at least) the 8.1.7.4 timeline, but the parameter was set to false until 11gR2 where it changed to auto. (Legal values are: false, true, never, auto, always.)

In 11.2, though, even though a simple select statement could use serial direct path reads for segment scans, Oracle would not use the mechanism for “insert as select”.

This note is just a little piece of code to demonstrate the point.  Run it on 11g and (unless your buffer cache is large enough to make the test table “small”) Oracle will use direct path reads on the select, but scattered reads to cache for the insert. Upgrade to 12.1 and Oracle will use direct path reads on both.

rem
rem     Script:         serial_fail.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Oct 2020
rem

create table t1
as
select
        ao.*
from
        all_objects     ao,
        (select rownum from dual connect by level <= 16) mult
/

create table t2
as
select  *
from    t1
where   rownum = 0
/

alter system flush buffer_cache;

prompt  =============
prompt  Simple Select
prompt  =============

execute snap_events.start_snap
select * from t1 where object_id = 98765;
execute snap_events.end_snap

prompt  ================
prompt  Insert as select
prompt  ================

execute snap_events.start_snap
insert into t2
select * from t1 where object_id = 98765;
execute snap_events.end_snap

prompt  =====================
prompt  Insert as select with
prompt  _serial_direct=always
prompt  =====================

alter session set "_serial_direct_read"=always;

execute snap_events.start_snap
insert /* serial direct */ into t2
select * from t1 where object_id = 98765;
execute snap_events.end_snap

alter session set "_serial_direct_read"=auto;

The calls to the snap_events package are the to produce the change in v$session_event for my session during the SQL.

You’ll notice I’ve included three main SQL statements rather than two – the third statement (2nd execution of the insert) is to demonstrate that it is possible to get direct path reads on the insert by setting the hidden parameter to ‘always’.

One detail to remember when testing this particular feature (and the same guideline applies to some other features), the “direct / not direct” becomes an attribute of the cursor, it’s not an attribute of the execution plan. This is why I’ve added a comment to the 2nd insert; if I hadn’t done so Oracle would have reused the (identical text) cursor from the first insert, which would have resulted in scattered reads being used instead of direct path reads. This distinction between cursor and plan explains why there is not hint that will allow you to force direct path reads for a specific query (not even the infamous opt_param() hint).

Here are the three sets of results from a system running 11.2.0.4:

=============
Simple Select
=============

Event                                             Waits   Time_outs           Csec    Avg Csec    Max Csec
-----                                             -----   ---------           ----    --------    --------
db file sequential read                               1           0           0.10        .100           4
direct path read                                    114           0          20.86        .183           6
SQL*Net message to client                             4           0           0.00        .000           0
SQL*Net message from client                           4           0           0.11        .028     174,435

================
Insert as select
================

Event                                             Waits   Time_outs           Csec    Avg Csec    Max Csec
-----                                             -----   ---------           ----    --------    --------
db file sequential read                              22           0           0.60        .027           4
db file scattered read                              130           0          35.97        .277           5
SQL*Net message to client                             4           0           0.01        .002           0
SQL*Net message from client                           4           0           0.10        .025     174,435

=====================
Insert as select with
_serial_direct=always
=====================

Event                                             Waits   Time_outs           Csec    Avg Csec    Max Csec
-----                                             -----   ---------           ----    --------    --------
direct path read                                    108           0          17.44        .161           6
SQL*Net message to client                             4           0           0.00        .000           0
SQL*Net message from client                           4           0           0.09        .022     174,435


Note the db file scattered read waits in the mddle test. If you re-run the test on 12.1.0.x (or later) you’ll find that the middle set of results will change to direct path read waits.

For reference, this limitation is covered by MOS note13250070.8: Bug 13250070 – Enh: Serial direct reads not working in DML. The actual bug note is not public.

Footnote (a couple of hours later):

A response from Roger MacNicol to my publication tweet has told us that the bug note says only that direct path reads had been restricted unnecessarily and the restriction has been removed.

4 Comments »

  1. Hi Jonathan! Thanks for the article!
    I was wondering, if you tested how scattered read of a “big” table affects the buffer cache? Would it push anything useful out of there, or will it use just a limited portion of the cache?
    Thank you

    Comment by Viacheslav Andzhich — October 9, 2020 @ 9:11 am BST Oct 9,2020 | Reply

    • Viacherslav,

      This is a topic that has received a lot of attention over the last 30 years or so, and although Oracle has introduced many variations on what can be done with various sizes of tables (when compared to the buffer cache) it has always operated with the principle that “small enough” tables will be handled just like single block reads in terms of caching, and “very large” tables will be handled carefully to avoid the threat of pushing every other block from the cache as they are scanned.

      The last time I checked (which was when writing Oracle Core) there were three basic mechanisms:
      “small” tables – read to the buffer cache midpoint
      “medium sized” tables – effectively read to the bottom 25% of the cache and can be promoted if repeat scanned
      “large” tables – effectively read to the bottom 25% of the cache but won’t be promoted if re-scanned

      (There are far more details, and this is just a very crude outline of actual activity – plus it’s had several years to go out of date.)

      It’s worth checking anything written by Roger MacNicol of Oracle relating to tablescans because he’s “Mr. Segment Scan” and has been working on them for decades. Here are a few references that you might find interesting:

      https://blogs.oracle.com/smartscan-deep-dive/the-beginners-guide-to-oracle-table-scans
      https://blogs.oracle.com/smartscan-deep-dive/when-bloggers-get-it-wrong-part-1
      https://blogs.oracle.com/smartscan-deep-dive/when-bloggers-get-it-wrong-part-2
      https://blogs.oracle.com/smartscan-deep-dive/revisiting-buffer-cache-very-large-object-threshold-vlot

      Regards
      Jonathan Lewis

      Comment by Jonathan Lewis — October 9, 2020 @ 11:05 am BST Oct 9,2020 | Reply

      • Jonathan, thank you very much for such a detailed explanation!
        I did read Oracle Core, I think, at least twice, but some chapters for 5 or 6 times :) (though, I had it in Russian translation, where translators some times didn’t really understood what they were translating, so you might find there puns like “Segment of a block header”). I’ve recently bought the original edition as well, will get to it once finished with Expert Oracle RAC :)

        I believe, my question was more about Oracle’s forgetfulness :) If it has forgotten about direct reads when doing INSERT, does it still remember that it reads a “big” table and it should be careful with cache? Now, that I think about, I believe it makes sense that cache mechanism still works as expected, because scattered reads might normally happen on big tables, and also, if this was the case, this won’t get left unnoticed, since it would lead to considerable performance issues.

        Thank you,
        Viacheslav

        Comment by Viacheslav Andzhich — October 9, 2020 @ 12:50 pm BST Oct 9,2020 | Reply

        • Viacheslav,

          I see your point.

          If DML and serial direct path read don’t go together in 11.2 does that mean a Very Large (VLOT) table will do scattered reads when it really ought to do serial direct path. The simple select will protect (most of) the cache, but will the DML/Select remember it’s supposed to protect the cache. Answer: Now that you’ve asked the question “I don’t know until I run a test to check”.

          As soon as you start looking closely at any little feature of Oracle there are always more questions to ask than you first think – and the answers you used to know doesn’t always apply any more.

          It’s nice to hear that you’ve got two copies of the book; I hope you enjoy reading the English-language version. I have a copy of the Russian translation, signed by a number of members of the Russian OUG from a visit I did to Moscow several years ago – but, alas, don’t have the linguistic skills to read it.

          Regards
          Jonathan Lewis

          Comment by Jonathan Lewis — October 9, 2020 @ 1:15 pm BST Oct 9,2020


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by WordPress.com.