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
/** 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;
/
[collapse]
Test results
_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.
[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)
/** 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;
/
[collapse]
Results
_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
[collapse]
Full test script: test_table.sql