Category Archives: adaptive serial direct path reads

select * from table where rownum=1

Posted on by Posted in adaptive serial direct path reads, CBO, oracle, troubleshooting 1 Comment

I never thought I would have to optimize so simple query as 

select col1, col2, col4, col7 from table where rownum=1

(even though I read recently “SELECT * FROM TABLE” Runs Out Of TEMP Space)
But a few days ago frequent executions of this query caused big problems on the one of our databases(11.2.0.3) because of adaptive serial direct path reads.

I don’t know why, but I felt intuitively that full table scan with “First K rows” optimization (“_optimizer_rownum_pred_based_fkr“=true) should turn off adaptive serial direct path reads. It seems quite logical to me.

PS. Unfortunately I had a little time, so I didn’t investigate what process and why it was doing that, I just created profile with “index full scan” access, and it completely solved the problem.

Example of controlling “direct path reads” decision through SQL profile hints (index_stats/table_stats)

Posted on by Posted in adaptive serial direct path reads, CBO, oracle, undocumented 1 Comment

Previously i showed not obvious example with hint “INDEX_STATS(“OWNER”.”TABLE_NAME”, “INDEX_NAME”, scale, blocks=X, rows=Y)“. Strictly speaking i don’t know how exactly cbo calculates number of index leaf blocks in that case: in those examples they was 1981 for “blocks=1, rows=50” and 49525 for “blocks=5, rows=10”.
But i know that with “INDEX_STATS(“OWNER”.”TABLE_NAME”, “INDEX_NAME”, scale, blocks=X)” i can set exact blocks number.
Also those test-cases didn’t show when occurs decision changing. So todays my test will show it.

UPD: btw previously I wrote that _very_large_object_threshold is a percentage of _db_block_buffers and Igor Usoltsev have tested and confirmed my guess

Test code 

/** SQLPLUS Example of controlling adaptive serial direct path reads through SQL profiles.
*/
set serverout on termout on timing off feed off;
clear scr;
def _IF_XT_RUNSTATS_EXISTS="--"
col if_xt_runstats_exists new_value _IF_XT_RUNSTATS_EXISTS noprint;
select decode(count(*),1,'  ','--') if_xt_runstats_exists 
from all_objects where object_name='XT_RUNSTATS' and rownum=1;
/**
 * Main test.
 * You can use it also in other clients, 
 * but in that case you have to manually 
 * set substitution variable _IF_XT_RUNSTATS_EXISTS:
 *  if you have not this package - to "--" 
 *  otherwise                    - to space(" ")
 * Latest version of XT_RUNSTATS you can get from:
 *  https://github.com/xtender/xt_runstats
 */

declare

   C_SQL_TEXT      constant varchar2(300):='SELECT SUM(A) FROM XT_IFFS T';
   C_PROFILE_NAME  constant varchar2(30) :='PRF_ADPR_TEST';
   v_small_table_threshold        int;
   v_db_block_buffers             int;
   v_very_large_object_threshold  int;
   v__db_cache_size               int;
      
   procedure exec(p varchar2) is
     e_table_is_not_created_yet exception;
     e_index_is_not_created_yet exception;
     pragma exception_init(e_table_is_not_created_yet, -942);
     pragma exception_init(e_index_is_not_created_yet, -1418);
   begin
     execute immediate p;
   exception 
      when e_table_is_not_created_yet 
        or e_index_is_not_created_yet
        then null;
   end;

   /** Creating table and setting stats */   
   procedure create_table is
   begin
     exec('drop table xt_iffs purge');
     exec('create table xt_iffs as 
           with gen as(
                       select level a,mod(level,10) b,lpad(1,50,1) c
                       from dual 
                       connect by level<=1e3
           )
           select gen.* 
           from gen,gen gen2'
         );
     --dbms_stats.set_table_stats('','XT_IFFS',numrows => 1e6,numblks => 5e5,avgrlen => 800);
     dbms_stats.gather_table_stats('','XT_IFFS');
   end;
   
   /** Recreating index and setting stats */
   procedure create_index is
   begin
     exec('drop index ix_iffs');
     exec('create index ix_iffs on xt_iffs(a,b)');
     dbms_stats.set_index_stats('','IX_IFFS',numrows => 1e6,numlblks => 1e4);
   end;
   
   /** Setting index numblks for query through SQL profile */
   procedure set_numblks(p_numblks int) is
     e_profile_not_created_yet  exception;
     pragma exception_init( e_profile_not_created_yet, -13833);
   begin
     begin
        dbms_sqltune.drop_sql_profile(C_PROFILE_NAME);
     exception when e_profile_not_created_yet then null;
     end;
     dbms_sqltune.import_sql_profile(
        sql_text    => C_SQL_TEXT,
        profile     => sys.sqlprof_attr(
                                 'INDEX_FFS(@"SEL$1" "T"@"SEL$1" ("XT_IFFS"."A"))'
                                ,'INDEX_STATS("'||user||'"."XT_IFFS", "IX_IFFS", scale, blocks='||p_numblks||')'
                       ),
        category     => 'DEFAULT',
        name        => C_PROFILE_NAME,
        force_match => false,
        replace     => true
     );
   end; 
     
   procedure test( p_description varchar2
                 , p_numblks int default null) is

     type t_seg_stat is 
        table of number 
        index by v$segstat_name.name%type;
     -- segments statistics by index:
     cursor c_stats is
        select sn.name,nvl(st.value,0) value
        from v$segstat_name sn
            ,v$segment_statistics st 
        where 
              sn.statistic#     = st.statistic#(+)
          and st.owner(+)       = user 
          and st.object_name(+) ='IX_IFFS';
     -- var for previous stats:
     v_pre     t_seg_stat;
     v_delta   number;
     n         number;
   begin
     dbms_output.put_line('-');
     dbms_output.put_line('-');
     dbms_output.put_line(lpad('-',150,'-'));
     dbms_output.put_line(lpad('-',150,'-'));
     dbms_output.put_line(  '###                ' 
                         || p_description||': '
                         ||nvl(to_char(p_numblks),'default')||' blocks');
     dbms_output.put_line('-');

     create_index;
     -- if p_numblks is null then default stats used
     if p_numblks is not null then
       set_numblks(p_numblks);
     end if;
     execute immediate C_SQL_TEXT into n;
     exec('alter system flush buffer_cache');
     -- saving segment statistics
     for r in c_stats loop
        v_pre(r.name) := r.value;
     end loop;

     &_IF_XT_RUNSTATS_EXISTS  xt_runstats.init(p_latches => false);
     -- executing query
     execute immediate C_SQL_TEXT into n;
   &_IF_XT_RUNSTATS_EXISTS  xt_runstats.snap;
   &_IF_XT_RUNSTATS_EXISTS  xt_runstats.print(
   &_IF_XT_RUNSTATS_EXISTS     p_stats_mask => '(reads (cache|direct)\.)|index fast full scans \((full|direct)'
   &_IF_XT_RUNSTATS_EXISTS    ,p_sta_diff_pct => 0);

     -- printing segments stats delta:
     for r in c_stats loop
        v_delta:= r.value - v_pre(r.name);
        if v_delta!=0 then
          dbms_output.put_line( rpad(r.name,40,'.')||v_delta );
        end if;
     end loop;
   end;

   procedure load_and_print_params is
   begin
      select
         max(decode(a.ksppinm, '_small_table_threshold'       ,b.ksppstvl)) 
        ,max(decode(a.ksppinm, '_db_block_buffers'            ,b.ksppstvl))
        ,max(decode(a.ksppinm, '_very_large_object_threshold' ,b.ksppstvl))
        ,max(decode(a.ksppinm, '__db_cache_size'              ,b.ksppstvl))
         into v_small_table_threshold,v_db_block_buffers,v_very_large_object_threshold,v__db_cache_size
      from
        sys.x$ksppi a
       ,sys.x$ksppcv b
      where
           a.indx = b.indx
       and a.ksppinm in('_small_table_threshold','_db_block_buffers','_very_large_object_threshold','__db_cache_size');
      dbms_output.put_line('_small_table_threshold       = '||v_small_table_threshold);
      dbms_output.put_line('_db_block_buffers            = '||v_db_block_buffers);
      dbms_output.put_line('_very_large_object_threshold = '||v_very_large_object_threshold);
      dbms_output.put_line('__db_cache_size              = '||v__db_cache_size);
      
   end;
begin
  create_table;
  load_and_print_params;

  test( '_very_large_object_threshold + 1'
       ,v_db_block_buffers * v_very_large_object_threshold/100 + 1 );
  test( '_very_large_object_threshold'
       ,v_db_block_buffers * v_very_large_object_threshold/100     );
  test( '_very_large_object_threshold - 1'
       ,v_db_block_buffers * v_very_large_object_threshold/100 - 1 );

  test( '_db_block_buffers + 1'
       ,v_db_block_buffers + 1 );
  test( '_db_block_buffers - 1'
       ,v_db_block_buffers - 1 );
  
  test( '_small_table_threshold * 5 + 1'
       ,v_small_table_threshold * 5 + 1 );
  test( '_small_table_threshold * 5 - 1'
       ,v_small_table_threshold * 5 - 1 );

  test( ' 1 block ', 1);
  test( ' Default ', null);

  test( ' Again _very_large_object_threshold + 1'
       ,v_db_block_buffers * v_very_large_object_threshold/100 + 1 );
  test( ' Again _very_large_object_threshold'
       ,v_db_block_buffers * v_very_large_object_threshold/100     );

end;
/

[collapse]
Test results 
_small_table_threshold       = 166
_db_block_buffers            = 8347
_very_large_object_threshold = 500
__db_cache_size              = 4194304
-
-
------------------------------------------------------------------------------
------------------------------------------------------------------------------
###                _very_large_object_threshold + 1: 41736 blocks
-
################     Results:      ##################
Run #  01 ran in 11 hsecs
##########################################################
Statistics                               | Run # 1
##########################################################
physical reads cache.................... |              1
physical reads direct................... |          2,491
index fast full scans (full)............ |              1
index fast full scans (direct read)..... |              1
##########################################################
-
logical reads...........................2496
physical reads..........................2492
physical read requests..................86
physical reads direct...................2491
segment scans...........................1
-
-
------------------------------------------------------------------------------
------------------------------------------------------------------------------
###                _very_large_object_threshold: 41735 blocks
-
################     Results:      ##################
Run #  01 ran in 6 hsecs
##########################################################
Statistics                               | Run # 1
##########################################################
physical reads cache.................... |          2,494
physical reads direct................... |              0
index fast full scans (full)............ |              1
index fast full scans (direct read)..... |              0
##########################################################
-
logical reads...........................2496
physical reads..........................2494
physical read requests..................95
segment scans...........................1
-
-
------------------------------------------------------------------------------
------------------------------------------------------------------------------
###                _very_large_object_threshold - 1: 41734 blocks
-
################     Results:      ##################
Run #  01 ran in 11 hsecs
##########################################################
Statistics                               | Run # 1
##########################################################
physical reads cache.................... |          3,386
physical reads direct................... |              1
index fast full scans (full)............ |              1
index fast full scans (direct read)..... |              0
##########################################################
-
logical reads...........................2512
physical reads..........................2494
physical read requests..................95
segment scans...........................1
-
-
------------------------------------------------------------------------------
------------------------------------------------------------------------------
###                _db_block_buffers + 1: 8348 blocks
-
################     Results:      ##################
Run #  01 ran in 6 hsecs
##########################################################
Statistics                               | Run # 1
##########################################################
physical reads cache.................... |          2,494
physical reads direct................... |              0
index fast full scans (full)............ |              1
index fast full scans (direct read)..... |              0
##########################################################
-
logical reads...........................2512
physical reads..........................2494
physical read requests..................95
segment scans...........................1
-
-
------------------------------------------------------------------------------
------------------------------------------------------------------------------
###                _db_block_buffers - 1: 8346 blocks
-
################     Results:      ##################
Run #  01 ran in 8 hsecs
##########################################################
Statistics                               | Run # 1
##########################################################
physical reads cache.................... |          2,494
physical reads direct................... |              0
index fast full scans (full)............ |              1
index fast full scans (direct read)..... |              0
##########################################################
-
logical reads...........................2512
physical reads..........................2494
physical read requests..................95
segment scans...........................1
-
-
------------------------------------------------------------------------------
------------------------------------------------------------------------------
###                _small_table_threshold * 5 + 1: 831 blocks
-
################     Results:      ##################
Run #  01 ran in 6 hsecs
##########################################################
Statistics                               | Run # 1
##########################################################
physical reads cache.................... |          2,494
physical reads direct................... |              0
index fast full scans (full)............ |              1
index fast full scans (direct read)..... |              0
##########################################################
-
logical reads...........................2512
physical reads..........................2494
physical read requests..................95
segment scans...........................1
-
-
------------------------------------------------------------------------------
------------------------------------------------------------------------------
###                _small_table_threshold * 5 - 1: 829 blocks
-
################     Results:      ##################
Run #  01 ran in 7 hsecs
##########################################################
Statistics                               | Run # 1
##########################################################
physical reads cache.................... |          2,494
physical reads direct................... |              0
index fast full scans (full)............ |              1
index fast full scans (direct read)..... |              0
##########################################################
-
logical reads...........................2496
physical reads..........................2494
physical read requests..................95
segment scans...........................1
-
-
------------------------------------------------------------------------------
------------------------------------------------------------------------------
###                 1 block : 1 blocks
-
################     Results:      ##################
Run #  01 ran in 6 hsecs
##########################################################
Statistics                               | Run # 1
##########################################################
physical reads cache.................... |          2,494
physical reads direct................... |              0
index fast full scans (full)............ |              1
index fast full scans (direct read)..... |              0
##########################################################
-
logical reads...........................2512
physical reads..........................2494
physical read requests..................95
segment scans...........................1
-
-
------------------------------------------------------------------------------
------------------------------------------------------------------------------
###                 Default : default blocks
-
################     Results:      ##################
Run #  01 ran in 7 hsecs
##########################################################
Statistics                               | Run # 1
##########################################################
physical reads cache.................... |          2,494
physical reads direct................... |              0
index fast full scans (full)............ |              1
index fast full scans (direct read)..... |              0
##########################################################
-
logical reads...........................2496
physical reads..........................2494
physical read requests..................95
segment scans...........................1
-
-
------------------------------------------------------------------------------
------------------------------------------------------------------------------
###                 Again _very_large_object_threshold + 1: 41736 blocks
-
################     Results:      ##################
Run #  01 ran in 6 hsecs
##########################################################
Statistics                               | Run # 1
##########################################################
physical reads cache.................... |              1
physical reads direct................... |          2,491
index fast full scans (full)............ |              1
index fast full scans (direct read)..... |              1
##########################################################
-
logical reads...........................2496
physical reads..........................2492
physical read requests..................86
physical reads direct...................2491
segment scans...........................1
-
-
------------------------------------------------------------------------------
------------------------------------------------------------------------------
###                 Again _very_large_object_threshold: 41735 blocks
-
################     Results:      ##################
Run #  01 ran in 7 hsecs
##########################################################
Statistics                               | Run # 1
##########################################################
physical reads cache.................... |          2,494
physical reads direct................... |              0
index fast full scans (full)............ |              1
index fast full scans (direct read)..... |              0
##########################################################
-
logical reads...........................2496
physical reads..........................2494
physical read requests..................95
segment scans...........................1

PL/SQL procedure successfully completed.

[collapse]

Old test script for Index Fast Full Scan: controlling_direct_reads_with_profiles.sql

2015-05-26. Update #2: Thanks to Advait Deo for correcting common mistake about wrong calculation of the lower limit for direct path read decision: the right limit is just “_small_table_threshold” ( “_small_table_threshold” * 5 )

I have simplified the test for checking this behaviour:

Simplified test(table scan) 

/** SQLPLUS Example of controlling adaptive serial direct path reads through SQL profiles.
*/
set serverout on termout on timing off feed off;
clear scr;
/**
 * Main test.
 */
 
declare
 
   C_SQL_TEXT      constant varchar2(300):='SELECT SUM(A) FROM XT_IFFS T';
   v_small_table_threshold        int;
   v_db_block_buffers             int;
   v_very_large_object_threshold  int;
   v__db_cache_size               int;
       
   procedure exec(p varchar2) is
     e_table_is_not_created_yet exception;
     e_index_is_not_created_yet exception;
     pragma exception_init(e_table_is_not_created_yet, -942);
     pragma exception_init(e_index_is_not_created_yet, -1418);
   begin
     execute immediate p;
   exception 
      when e_table_is_not_created_yet 
        or e_index_is_not_created_yet
        then null;
   end;
 
   /** Creating table and setting stats */   
   procedure create_table is
   begin
     exec('drop table xt_iffs purge');
     exec('create table xt_iffs as 
           with gen as(
                       select level a,mod(level,10) b,lpad(1,50,1) c
                       from dual 
                       connect by level<=100
           )
           select gen.* 
           from gen,gen gen2'
         );
     dbms_stats.gather_table_stats('','XT_IFFS',no_invalidate => false);
     dbms_stats.set_table_stats('','XT_IFFS',numrows => 1e6,numblks => 5e5,avgrlen => 800,no_invalidate => false);
   end;
    
   /** Setting index numblks for query through SQL profile */
   procedure set_numblks(p_numblks int) is
     e_profile_not_created_yet  exception;
     pragma exception_init( e_profile_not_created_yet, -13833);
   begin
     dbms_stats.set_table_stats(user,'XT_IFFS',numrows => 1e6,numblks => p_numblks, avgrlen => 800,no_invalidate => false);
   end; 
      
   procedure test( p_description varchar2
                 , p_numblks int default null) is
     v_delta   number;
     n         number;
     
     type t_ses_stat is
        table of number 
        index by v$segstat_name.name%type;

     -- var for previous stats:
     v_pre     t_ses_stat;

     -- segments statistics by segment name:
     cursor c_stats is
        select name,value 
        from v$mystat st join v$statname sn using(STATISTIC#)
        where regexp_like(name,'^physical reads (cache|direct)$');
     
     procedure ses_stats_save is
     begin
       for r in c_stats loop
          v_pre(r.name) := r.value;
       end loop;
     end ses_stats_save;
     
     procedure ses_stats_print is
     begin
       for r in c_stats loop
          v_delta:= r.value - v_pre(r.name);
          if v_delta!=0 then
            dbms_output.put_line( rpad(r.name,40,'.')||v_delta );
          end if;
       end loop;
     end ses_stats_print;
     
   begin
     dbms_output.put_line(chr(160));
     dbms_output.put_line(lpad('-',150,'-'));
     dbms_output.put_line(  '###                '
                         || p_description||': '
                         ||nvl(to_char(p_numblks),'default')||' blocks');
     dbms_output.put_line(chr(160));
 
     --create_index;
     -- if p_numblks is null then default stats used
     if p_numblks is not null then
       set_numblks(p_numblks);
     end if;
     execute immediate C_SQL_TEXT into n;
     exec('alter system flush buffer_cache');
     -- saving segment statistics
     ses_stats_save;
 
     -- executing query
     execute immediate C_SQL_TEXT into n;
 
     -- printing segments stats delta:
     ses_stats_print;
   end;
 
   procedure load_and_print_params is
   begin
      select
         max(decode(a.ksppinm, '_small_table_threshold'       ,b.ksppstvl)) 
        ,max(decode(a.ksppinm, '_db_block_buffers'            ,b.ksppstvl))
        ,max(decode(a.ksppinm, '_very_large_object_threshold' ,b.ksppstvl))
        ,max(decode(a.ksppinm, '__db_cache_size'              ,b.ksppstvl))
         into v_small_table_threshold,v_db_block_buffers,v_very_large_object_threshold,v__db_cache_size
      from
        sys.x$ksppi a
       ,sys.x$ksppcv b
      where
           a.indx = b.indx
       and a.ksppinm in('_small_table_threshold','_db_block_buffers','_very_large_object_threshold','__db_cache_size');
      dbms_output.put_line('_small_table_threshold       = '||v_small_table_threshold);
      dbms_output.put_line('_db_block_buffers            = '||v_db_block_buffers);
      dbms_output.put_line('_very_large_object_threshold = '||v_very_large_object_threshold);
      dbms_output.put_line('__db_cache_size              = '||v__db_cache_size);
       
   end;
begin
  create_table;
  load_and_print_params;
 
  test( '_very_large_object_threshold + 1'
       ,v_db_block_buffers * v_very_large_object_threshold/100 + 1 );
  test( '_very_large_object_threshold'
       ,v_db_block_buffers * v_very_large_object_threshold/100     );
  test( '_very_large_object_threshold - 1'
       ,v_db_block_buffers * v_very_large_object_threshold/100 - 1 );
 
  test( '_db_block_buffers + 1'
       ,v_db_block_buffers + 1 );
  test( '_db_block_buffers - 1'
       ,v_db_block_buffers - 1 );
   
  test( '_small_table_threshold * 5 + 1'
       ,v_small_table_threshold * 5 + 1 );
  test( '_small_table_threshold * 5 - 1'
       ,v_small_table_threshold * 5 - 1 );

  test( '_small_table_threshold + 1'
       ,v_small_table_threshold + 1 );
  test( '_small_table_threshold'
       ,v_small_table_threshold     );
  test( '_small_table_threshold - 1'
       ,v_small_table_threshold - 1 );
 
  test( ' 1 block ', 1);
  test( ' Default ', null);
 
  test( ' Again _very_large_object_threshold + 1'
       ,v_db_block_buffers * v_very_large_object_threshold/100 + 1 );
  test( ' Again _very_large_object_threshold'
       ,v_db_block_buffers * v_very_large_object_threshold/100     );
 
end;
/

[collapse]

Results 

_small_table_threshold       = 2246
_db_block_buffers            = 112347
_very_large_object_threshold = 500
__db_cache_size              = 939524096

------------------------------------------------------------------------
###                _very_large_object_threshold + 1: 561736 blocks

physical reads cache....................1
physical reads direct...................87

------------------------------------------------------------------------
###                _very_large_object_threshold: 561735 blocks

physical reads cache....................1
physical reads direct...................87

------------------------------------------------------------------------
###                _very_large_object_threshold - 1: 561734 blocks

physical reads cache....................1
physical reads direct...................87

------------------------------------------------------------------------
###                _db_block_buffers + 1: 112348 blocks

physical reads cache....................1
physical reads direct...................87

------------------------------------------------------------------------
###                _db_block_buffers - 1: 112346 blocks

physical reads cache....................1
physical reads direct...................87

------------------------------------------------------------------------
###                _small_table_threshold * 5 + 1: 11231 blocks

physical reads cache....................1
physical reads direct...................87

------------------------------------------------------------------------
###                _small_table_threshold * 5 - 1: 11229 blocks

physical reads cache....................1
physical reads direct...................87

------------------------------------------------------------------------
###                _small_table_threshold + 1: 2247 blocks

physical reads cache....................1
physical reads direct...................87

------------------------------------------------------------------------
###                _small_table_threshold - 1: 2245 blocks

physical reads cache....................88

------------------------------------------------------------------------
###                 1 block : 1 blocks

physical reads cache....................88

------------------------------------------------------------------------
###                 Default : default blocks

physical reads cache....................88

------------------------------------------------------------------------
###                 Again _very_large_object_threshold + 1: 561736 blocks

physical reads cache....................1
physical reads direct...................87

------------------------------------------------------------------------
###                 Again _very_large_object_threshold: 561735 blocks

physical reads cache....................1
physical reads direct...................87

[collapse]

Full test script: test_table.sql

Controlling “direct path reads” decision with INDEX_STATS/table_stats

Posted on by Posted in adaptive serial direct path reads, CBO, oracle 3 Comments

Since 11.2.0.2 direct path read decision on full scans(FTS/IFFS) can be based on the statistics.

And if my test is correct, it appears that we can control this behavior on query level with changing number of blocks through index_stats/table_stats outlines:

UPD: I did a little change of the test case for avoiding impact of hard parse on main test.

Test case 

drop table xt_iffs purge;
spool &_spools/iffs_test.sql;
@param_ _direct_read_decision_statistics_driven
@param_ _small_table_threshold
SELECT name,block_size,buffers FROM v$buffer_pool;

create table xt_iffs as select level a,mod(level,100) b,lpad(1,100,1) c from dual connect by level<=1e5;
create index ix_iffs on xt_iffs(a);
exec dbms_stats.set_table_stats('','XT_IFFS',numrows => 1e6,numblks => 5e5,avgrlen => 800);
exec dbms_stats.set_index_stats('','IX_IFFS',numrows => 1e6,numlblks => 1e4);
set termout off echo off feed off timing off;
------------------- 1 run for avoiding hard parse in main test
alter system flush buffer_cache;
select/*+ index_ffs(t IX_IFFS) */ sum(a) from xt_iffs;

alter system flush buffer_cache;
select/*+ 
  BEGIN_OUTLINE_DATA
    INDEX_FFS(T IX_IFFS) 
    INDEX_STATS("XTENDER"."XT_IFFS", "IX_IFFS", scale, blocks=5, rows=10) 
  END_OUTLINE_DATA
  */ 
  sum(a) 
from xt_iffs t;

alter system flush buffer_cache;
select/*+ 
  BEGIN_OUTLINE_DATA
    INDEX_FFS(T IX_IFFS) 
    INDEX_STATS("XTENDER"."XT_IFFS", "IX_IFFS", scale, blocks=1, rows=50)  
  END_OUTLINE_DATA
  */ 
  sum(a) 
from xt_iffs t;
------------------- Main test with statistics: ----------------------------------------------
exec xt_runstats.init(p_latches => false);
alter system flush buffer_cache;
select/*+ index_ffs(t IX_IFFS) */ sum(a) from xt_iffs;
exec xt_runstats.snap;

alter system flush buffer_cache;
select/*+ 
  BEGIN_OUTLINE_DATA
    INDEX_FFS(T IX_IFFS) 
    INDEX_STATS("XTENDER"."XT_IFFS", "IX_IFFS", scale, blocks=5, rows=10) 
  END_OUTLINE_DATA
  */ 
  sum(a) 
from xt_iffs t;
exec xt_runstats.snap;

alter system flush buffer_cache;
select/*+ 
  BEGIN_OUTLINE_DATA
    INDEX_FFS(T IX_IFFS) 
    INDEX_STATS("XTENDER"."XT_IFFS", "IX_IFFS", scale, blocks=1, rows=50)  
  END_OUTLINE_DATA
  */ 
  sum(a) 
from xt_iffs t;
exec xt_runstats.snap;

set termout on echo on serverout on;

exec xt_runstats.print(p_stats_mask => 'reads|direct',p_sta_diff_pct => 0);
spool off;

[collapse]

Result 


NAME                                     VALUE        DEFLT        TYPE         DESCRIPTION
---------------------------------------- ------------ ------------ ------------ ------------------------------------------------------------
_direct_read_decision_statistics_driven  TRUE         TRUE         boolean      enable direct read decision based on optimizer statistics

Elapsed: 00:00:00.20

NAME                                     VALUE        DEFLT        TYPE         DESCRIPTION
---------------------------------------- ------------ ------------ ------------ ------------------------------------------------------------
_small_table_threshold                   166          TRUE         number       lower threshold level of table size for direct reads

Elapsed: 00:00:00.21

NAME                                     BLOCK_SIZE    BUFFERS
---------------------------------------- ---------- ----------
DEFAULT                                        8192        491

Elapsed: 00:00:00.19

Table created.

Elapsed: 00:00:00.29

Index created.

Elapsed: 00:00:00.46

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.15

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.13
SQL>
SQL> exec xt_runstats.print(p_stats_mask => 'reads|direct',p_sta_diff_pct => 0);
################     Results:      ##################
Run #  01 ran in 48 hsecs
Run #  02 ran in 47 hsecs
Run #  03 ran in 48 hsecs
############################################################################################
Statistics                               | Run # 1        | Run # 2        | Run # 3
############################################################################################
session logical reads................... |            229 |            227 |            229
session logical reads in local numa grou |              0 |              0 |              0
session logical reads in remote numa gro |              0 |              0 |              0
db block gets direct.................... |              0 |              0 |              0
consistent gets direct.................. |              0 |            223 |              0
physical reads.......................... |            224 |            224 |            224
physical reads cache.................... |            224 |              1 |            224
physical reads direct................... |              0 |            223 |              0
physical writes direct.................. |              0 |              0 |              0
physical reads direct temporary tablespa |              0 |              0 |              0
physical writes direct temporary tablesp |              0 |              0 |              0
recovery array reads.................... |              0 |              0 |              0
physical reads cache prefetch........... |            203 |              0 |            203
physical reads prefetch warmup.......... |              0 |              0 |              0
physical reads retry corrupt............ |              0 |              0 |              0
physical reads direct (lob)............. |              0 |              0 |              0
physical writes direct (lob)............ |              0 |              0 |              0
cold recycle reads...................... |              0 |              0 |              0
physical reads for flashback new........ |              0 |              0 |              0
flashback direct read optimizations for  |              0 |              0 |              0
redo size for direct writes............. |              0 |              0 |              0
cell physical IO bytes sent directly to  |              0 |              0 |              0
transaction tables consistent reads - un |              0 |              0 |              0
data blocks consistent reads - undo reco |              0 |              0 |              0
table scans (direct read)............... |              0 |              0 |              0
lob reads............................... |              0 |              0 |              0
index fast full scans (direct read)..... |              0 |              1 |              0
securefile direct read bytes............ |              0 |              0 |              0
securefile direct write bytes........... |              0 |              0 |              0
securefile direct read ops.............. |              0 |              0 |              0
securefile direct write ops............. |              0 |              0 |              0
############################################################################################

[collapse]