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]