Controlling “direct path reads” decision with INDEX_STATS/table_stats

[sourcecode language=”sql” highlight=”45,56″]
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;
[/sourcecode]

[sourcecode language=”sql” highlight=”50,69″]

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
############################################################################################
[/sourcecode]