Home > Oracle > _serial_direct_read is not a criteria for cursor [non-]sharing

_serial_direct_read is not a criteria for cursor [non-]sharing

October 22, 2018 Leave a comment Go to comments

Those who are on Exadata and change _SERIAL_DIRECT_READ on session or system level may be interested in the issue described below. The issue exists in all versions from 11g and above.

As you probably already know from 11g Oracle decides whether to do a serial full scan (table full scan, index fast full scan, mat view full scan) via buffer cache or by direct path reads. This behavior is controlled by hidden parameter _SERIAL_DIRECT_READ which may be set to (from 11.2.0.2)
AUTO (default) – leaves the decision to Oracle, the decision may be different for each run and each partition of one table. The decision depends on many factors like segment size, number of buffers in buffer cache, etc.
NEVER (same as FALSE) – disables serial full scan via direct path reads
ALWAYS (same as TRUE) – forces serial full scan via direct path reads
Note: values AUTO/ALWAYS/NEVER were introduced in 11.2.0.2, before that there were TRUE/FALSE where TRUE meant AUTO

Also direct path reads decision is a requirement for Smart Scan. So, finally one may want to change _SERIAL_DIRECT_READ on session level in order to force full scan read by direct path read or Smart Scan on Exadata.

The problem described in this post is when a SQL is parsed and a new child cursor is created, the value of _SERIAL_DIRECT_READ (not the decision, but the mode AUTO/ALWAYS/NEVER) is stored in the child cursor, and this parameter is not a criteria for cursor sharing. As a result if a SQL is parsed in a session with _SERIAL_DIRECT_READ=ALWAYS and then called in another session with another value of _SERIAL_DIRECT_READ, for example NEVER or AUTO and all the other cursor sharing criteria matched the existing cursor is reused which behaves as if it is run with _SERIAL_DIRECT_READ=ALWAYS. In other words, if you set _SERIAL_DIRECT_READ=NEVER in a session it doesn’t guarantee direct path reads disabled for all SQLs in the session, but only for the SQLs for which new child cursors are built. Those SQLs which already have child cursors which may be reused, will take value of _SERIAL_DIRECT_READ from the child cursor which was assigned in the moment when the child cursor is built.

Note: by contrast event 10949 when set (alter session set events ‘10949 trace name context forever, level 1’) disables direct path reads for all serial full scans in session, regardless how the SQL was parsed earlier. However the mentioned problem is not only a case when we want to disable direct full scan, but also a case when we may want to force it by ALWAYS or have auto decision by AUTO

One of the worst scenarios (testcase #2 below) is a little lightweight SQL which is supposed to be called many [thousands or millions of] times, e.g. in a PL/SQL function involved in various views or another SQLs. If the view with this function is called in a session with _SERIAL_DIRECT_READ=ALWAYS and the small SQL inside the mentioned PL/SQL is parsed first time there (until it is recursive, a little bit more about it in another post) it will be run in this mode in all another sessions even if they have _SERIAL_DIRECT_READ=AUTO or NEVER. In those cases you may see high number of related waits as ‘direct path read’ or ‘cell smart table scan’, ‘enq: KO – fast object checkpoint’, and others. Fortunately for this scenario there is a workaround – ALTER TABLE CACHE, the table will not be full scanned via direct path reads even in a session with by _SERIAL_DIRECT_READ=ALWAYS.

Below are outputs of two test cases demonstrating the problem. The test cases are at the bottom of this post.
Here is the output of test case #1 with a very simple SQL

SQL> alter system flush shared_pool;
System altered

SQL> alter session set "_serial_direct_read"=always;
Session altered

SQL> select value as direct_scans_before from v$mystat m natural join v$statname n where name like '%table scans (direct read)%';
DIRECT_SCANS_BEFORE
-------------------
                  0
SQL> select count(*) from t1;
  COUNT(*)
----------
         1
SQL> select count(*) from t1;
  COUNT(*)
----------
         1
SQL> select count(*) from t1;
  COUNT(*)
----------
         1
SQL> select value as direct_scans_after from v$mystat m natural join v$statname n where name like '%table scans (direct read)%';
DIRECT_SCANS_AFTER
------------------
                 3
SQL> -- 1.2 serial_direct_read"=NEVER, the existing child cursor is used

SQL> alter session set "_serial_direct_read"=never;
Session altered

SQL> select value as direct_scans_before from v$mystat m natural join v$statname n where name like '%table scans (direct read)%';
DIRECT_SCANS_BEFORE
-------------------
                  3

SQL> select count(*) from t1;
  COUNT(*)
----------
         1

SQL> select count(*) from t1;
  COUNT(*)
----------
         1

SQL> select count(*) from t1;
  COUNT(*)
----------
         1

SQL> select value as direct_scans_after from v$mystat m natural join v$statname n where name like '%table scans (direct read)%';
DIRECT_SCANS_AFTER
------------------
                 6

SQL> -- 1.3 serial_direct_read"=NEVER, new child cursor
SQL> alter system flush shared_pool;
System altered

SQL> select value as direct_scans_before from v$mystat m natural join v$statname n where name like '%table scans (direct read)%';
DIRECT_SCANS_BEFORE
-------------------
                  6
SQL> select count(*) from t1;
  COUNT(*)
----------
         1

SQL> select count(*) from t1;
  COUNT(*)
----------
         1

SQL> select count(*) from t1;
  COUNT(*)
----------
         1

SQL> select value as direct_scans_after from v$mystat m natural join v$statname n where name like '%table scans (direct read)%';
DIRECT_SCANS_AFTER
------------------
                 6

Here direct path reads were enabled when the SQL was run in a session with “_serial_direct_read”=NEVER until it was re-parsed.
This is the critical piece

SQL> alter session set "_serial_direct_read"=never;
Session altered

DIRECT_SCANS_BEFORE
-------------------
                  3
...
DIRECT_SCANS_AFTER
------------------
                 6

which demonstrates the full scan still works via direct path reads in a session with “_serial_direct_read”=never just because this SQL was earlier parsed in a session with “_serial_direct_read”=always;

Output of test case #2, where we have a lightweight SQL inside a PL/SQL function f(). The function is called 1K times from the parent SQL. The parent SQL is called in PL/SQL itself. The SQL inside function f() is not considered as recursive here.

SQL> drop table t1;
Table dropped

SQL> create table t1 as select 0 id from dual;
Table created

SQL> create or replace function f(i number) return number
  2  is
  3    n number;
  4  begin
  5    select count(*) into n from t1 where id < i;
  6    return n;
  7  end;
  8  /
Function created

SQL> alter system flush shared_pool;
System altered

SQL> alter session set "_serial_direct_read"=always;
Session altered

SQL> select value as direct_scans_before from v$mystat m natural join v$statname n where name like '%table scans (direct read)%';
DIRECT_SCANS_BEFORE
-------------------
                  0
SQL> declare
  2    n number;
  3  begin
  4   select sum(f(rownum))
  5     into n
  6     from dual connect by level <= 1000;
  7  end;
  8  /
PL/SQL procedure successfully completed

SQL> select value as direct_scans_after from v$mystat m natural join v$statname n where name like '%table scans (direct read)%';
DIRECT_SCANS_AFTER
------------------
              1000


SQL> alter session set "_serial_direct_read"=never;
Session altered

SQL> select value as direct_scans_before from v$mystat m natural join v$statname n where name like '%table scans (direct read)%';
DIRECT_SCANS_BEFORE
-------------------
               1000

SQL> declare
  2    n number;
  3  begin
  4   select sum(f(rownum))
  5     into n
  6     from dual connect by level <= 1000;
  7  end;
  8  /
PL/SQL procedure successfully completed

SQL> select value as direct_scans_after from v$mystat m natural join v$statname n where name like '%table scans (direct read)%';
DIRECT_SCANS_AFTER
------------------
              2000

Here we can see function f() was called 1K times and the problematic SQL inside function f() has done 1K direct full scans being run in a session with “_serial_direct_read”=never, just becase this SQL was parsed earlier in a session with “_serial_direct_read”=always.

In both test cases it doesn’t matter whether 2nd part of test case is run in the same session or another one, what matters is that existing child cursor is re-used which has different value of _SERIAL_DIRECT_READ, the session’s value is just ignored.

This behavior should be considered as a defect, obviously if _SERIAL_DIRECT_READ is stored in a child cursor it should be another criteria for cursor non-sharing. A bug is created, however it may hang for a long time before any progress.

Just be aware.

TEST CASES
testcase #1

drop table t1;
create table t1 as select 0 id from dual;


-- 1.1 serial_direct_read"=ALWAYS, new cursor
alter system flush shared_pool;
alter session set "_serial_direct_read"=always;
select value as direct_scans_before from v$mystat m natural join v$statname n where name like '%table scans (direct read)%';

select count(*) from t1;
select count(*) from t1;
select count(*) from t1;

select value as direct_scans_after from v$mystat m natural join v$statname n where name like '%table scans (direct read)%';


-- 1.2 serial_direct_read"=NEVER, the existing child cursor is used
alter session set "_serial_direct_read"=never;
select value as direct_scans_before from v$mystat m natural join v$statname n where name like '%table scans (direct read)%';

select count(*) from t1;
select count(*) from t1;
select count(*) from t1;

select value as direct_scans_after from v$mystat m natural join v$statname n where name like '%table scans (direct read)%';


-- 1.3 serial_direct_read"=NEVER, new child cursor
alter system flush shared_pool;
select value as direct_scans_before from v$mystat m natural join v$statname n where name like '%table scans (direct read)%';

select count(*) from t1;
select count(*) from t1;
select count(*) from t1;

select value as direct_scans_after from v$mystat m natural join v$statname n where name like '%table scans (direct read)%';

testcase #2

drop table t1;
create table t1 as select 0 id from dual;

create or replace function f(i number) return number 
is
  n number;
begin
  select count(*) into n from t1 where id < i;
  return n;
end;
/

alter system flush shared_pool;
alter session set "_serial_direct_read"=always;

select value as direct_scans_before from v$mystat m natural join v$statname n where name like '%table scans (direct read)%';

declare
  n number;
begin
 select sum(f(rownum)) 
   into n 
   from dual connect by level <= 1000;
end;
/

select value as direct_scans_after from v$mystat m natural join v$statname n where name like '%table scans (direct read)%';

alter session set "_serial_direct_read"=never;
select value as direct_scans_before from v$mystat m natural join v$statname n where name like '%table scans (direct read)%';

declare
  n number;
begin
 select sum(f(rownum)) 
   into n 
   from dual connect by level <= 1000;
end;
/

select value as direct_scans_after from v$mystat m natural join v$statname n where name like '%table scans (direct read)%';
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

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

%d bloggers like this: