11G adaptive direct path reads -- what is the cached/dirty blocks threshold?

11G's ability to do direct path reads during full table scans without utilizing PQ was covered in a number of places already (see this post by Doug Burns for example).

When direct path reads starts to happen?

It is known that somewhat reliable figure is your _small_table_threshold multiplied by 5 (mentioned by Tanel Poder on oracle-l recently). You can discover it using quick and dirty test case similar to this:

SQL> create tablespace adr_test datafile size 64m segment space management manual;
 
Tablespace created

SQL> create table t (v varchar2(100)) pctused 1 pctfree 99 tablespace adr_test;
 
Table created

SQL> create or replace function get_adr_trsh(
  2   p_step  in number,
  3   p_start  in number default 0,
  4   p_stop  in number default null
  5  ) return number is
  6   l_prd  number;
  7   l_blocks number:=0;
  8   l_start  number:=p_start;
  9  begin
 10   execute immediate 'truncate table t';
 11  
 12   loop
 13    insert /*+ append */ into t
 14     select rpad('*', 100, '*')
 15      from dual
 16      connect by level <= p_step + l_start;
 17    commit;
 18  
 19    l_blocks:=l_blocks + p_step + l_start;
 20    l_start:=0;
 21  
 22    execute immediate 'alter system flush buffer_cache';
 23  
 24    select /*+ full(t) */ count(*) into l_cnt from t;
 25  
 26    select value into l_prd
 27     from v$segment_statistics
 28     where owner=user
 29      and object_name='T'
 30      and statistic_name='physical reads direct';
 31  
 32    exit when (l_prd > 0 or l_blocks > nvl(p_stop, l_blocks));
 33  
 34   end loop;
 35  
 36   return l_blocks - p_step;
 37  end;
 38  /
 
Function created

My _small_table_threshold is:

SQL> select ksppstvl
  2   from x$ksppi x, x$ksppcv y
  3   where (x.indx = y.indx)
  4    and ksppinm='_small_table_threshold';
 
KSPPSTVL
--------------------------------------------------------------------------------
314

which is about 2% of my buffer cache (128MB) so you may expect 11G switch to direct path reads once table goes beyond 1570 blocks. Let's check it:

SQL> declare
  2   l_trsh number;
  3  begin
  4   l_trsh:=get_adr_trsh(10, 1500, 2000);
  5  
  6   dbms_output.put_line(l_trsh);
  7  end;
  8  /
 
1570
 
PL/SQL procedure successfully completed

Note that that number is somewhat "about" and you can get different results depending on stuff like using ASSM/MSSM.

What is the cached blocks threshold?

Direct path reads stops happening after certain amount of your table's blocks are in the buffer cache already. Discovering it is fairly easy as well:

SQL> --need this so we can do irs and cache table blocks
SQL> create index i_t on t (1);
 
Index created

SQL> create or replace function get_cached_trsh(
  2    p_start in number default 0,
  3    p_step in number default 1
  4  ) return number is
  5   cursor l_cur is select /*+ index(t i_t) */ * from t;
  6   l_v varchar2(100);
  7   l_trsh number:=0;
  8   l_prd number:=0;
  9   l_cnt number:=0;
 10   l_start number:=p_start;
 11  begin
 12   execute immediate 'alter system flush buffer_cache';
 13   open l_cur;
 14  
 15   loop
 16    for i in 1 .. p_step+l_start
 17    loop
 18     fetch l_cur into l_v;
 19    end loop;
 20    l_trsh:=l_trsh+p_step+l_start;
 21    l_start:=0;
 22  
 23    select /*+ full(t) */ count(*) into l_cnt from t;
 24  
 25    select value into l_cnt
 26     from v$segment_statistics
 27     where owner=user
 28      and object_name='T'
 29      and statistic_name='physical reads direct';
 30  
 31    exit when l_cnt=l_prd or l_cur%notfound;
 32  
 33    l_prd:=l_cnt;
 34  
 35   end loop;
 36  
 37   close l_cur;
 38   return l_trsh;
 39  end;
 40  /
 
Function created

Now, we can see after how many blocks 11G will stop doing direct path reads:

SQL> declare
  2   l_trsh number;
  3  begin
  4   l_trsh:=get_cached_trsh(500, 1);
  5  
  6   dbms_output.put_line(l_trsh);
  7  end;
  8  /
 
789
 
PL/SQL procedure successfully completed

Which happens to be half of the table's blocks. I've repeated the above test with 256MB buffer cache and got 3140 blocks (number of blocks for direct read to start happening) and 1568 (number of cached blocks) respectively. Please note that cached blocks threshold seems to be not dependent on your buffer cache size (to a degree where it can find space of course).

What is the dirty blocks threshold?

Doing direct path reads requires segment level checkpoint which may not be something you would like to do if you have a lot of these for the sake of direct read alone.

Something we can start with:

SQL> create or replace function get_dirty_trsh(
  2    p_step in number,
  3    p_start in number default 0,
  4    p_stop in number default null
  5  ) return number is
  6   l_trsh number:=0;
  7   l_prd number:=0;
  8   l_cnt number:=0;
  9   l_start number:=p_start;
 10  begin
 11   execute immediate 'alter system flush buffer_cache';
 12  
 13   loop
 14    l_trsh:=l_trsh+p_step+l_start;
 15    update t set v=v where rownum <= l_trsh;
 16    commit;
 17    l_start:=0;
 18  
 19    select /*+ full(t) */ count(*) into l_cnt from t;
 20  
 21    select value into l_cnt
 22     from v$segment_statistics
 23     where owner=user
 24      and object_name='T'
 25      and statistic_name='physical reads direct';
 26  
 27    exit when l_cnt=l_prd or l_trsh > nvl(p_stop, l_trsh);
 28  
 29    l_prd:=l_cnt;
 30  
 31   end loop;
 32  
 33   return l_trsh;
 34  end;
 35  /
 
Function created

SQL> declare
  2   l_trsh number;
  3  begin
  4   l_trsh:=get_dirty_trsh(1, 350, 400);
  5  
  6   dbms_output.put_line(l_trsh);
  7  end;
  8  /
384

PL/SQL procedure successfully completed.

Which turns out to be 1/4 of a table size.

Quick and dirty

Please note that adaptive direct path reads could (and most probably do) have much more variables to make a decision. The above test were done using ad-hoc approach to at least have an idea what could be potential factors there. Things like system statistics, tablespace block sizes, delayed blocks cleanouts, etc. has a potential to interfere over there.

SQL> select * from v$version;
 
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for 64-bit Windows: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production