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
[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]
[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]
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:
[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]
[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]
Full test script: test_table.sql