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 2,554 Page views 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 2,861 Page views 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

[sourcecode language=”sql” collapse=”true”]
/** 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;
/
[/sourcecode]

[collapse]

Test results

[sourcecode language=”sql” highlight=”9,17,19,25,31,41,52,62,73,83,94,104,115,125,136,146,157,167,178,188,199,209,221,231″ ruler=”true” collapse=”true”]
_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.
[/sourcecode]

[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)

[sourcecode language=”sql”]
/** 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;
/

[/sourcecode]

[collapse]

Results

[sourcecode language=”sql” highlight=”43,46,49,52,55,57″]
_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
[/sourcecode]

[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 2,291 Page views 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

[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]

[collapse]

Result

[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]

[collapse]