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]