/** 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; /