Oracle SQL

Example of controlling “direct path reads” decision through SQL profile hints (index_stats/table_stats)

Posted on March 18, 2013 by Sayan Malakshinov Posted in adaptive serial direct path reads, oracle, undocumented Leave a 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.

BTW, it is very interesting that a turning point was _very_large_object_threshold, but not 5 * _small_table_threshold. AFAIK, direct path reads decision depends on many factors (such as number of object blocks in buffer cache), and would be great to know all of them.
You can download script as file: controlling_direct_reads_with_profiles.sql

Spoiler:: Test code	Select Show
/** 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; /

Spoiler:: Test code

Select Show

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

Spoiler:: Test results	Select Show
_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.

Spoiler:: Test results

Select Show

_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.

Materialization in subquery factoring without hint “materialize” can be considered only when exists at least one predicate

Posted on February 9, 2013 by Sayan Malakshinov Posted in oracle, query optimizing, undocumented 6 Comments

I found just now that materialization can not be considered by optimizer if there are no predicates in subquery factoring clause. Of course, i mean cases without forcing materialization through hint “materialize”.
Simple example:

Spoiler:: Hidden text	Select Show
SQL> create table tt1 as select 1 id from dual; Table created. SQL> exec dbms_stats.gather_table_stats('','TT1'); PL/SQL procedure successfully completed. SQL> explain plan for 2 with gen as (select * from tt1) 3 select * from gen,gen g2; Explained. SQL> @xplan PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------- Plan hash value: 486748850 ----------------------------------------------------------------------------- \| Id \| Operation \| Name \| Rows \| Bytes \| Cost (%CPU)\| Time \| ----------------------------------------------------------------------------- \| 0 \| SELECT STATEMENT \| \| 1 \| 6 \| 6 (0)\| 00:00:01 \| \| 1 \| MERGE JOIN CARTESIAN\| \| 1 \| 6 \| 6 (0)\| 00:00:01 \| \| 2 \| TABLE ACCESS FULL \| TT1 \| 1 \| 3 \| 3 (0)\| 00:00:01 \| \| 3 \| BUFFER SORT \| \| 1 \| 3 \| 3 (0)\| 00:00:01 \| \| 4 \| TABLE ACCESS FULL \| TT1 \| 1 \| 3 \| 3 (0)\| 00:00:01 \| ----------------------------------------------------------------------------- SQL> explain plan for 2 with gen as (select * from tt1 where 1=1) 3 select * from gen,gen g2; Explained. SQL> @xplan PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------- Plan hash value: 2673059801 --------------------------------------------------------------------------------------------------------- \| Id \| Operation \| Name \| Rows \| Bytes \| Cost (%CPU)\| Time \| --------------------------------------------------------------------------------------------------------- \| 0 \| SELECT STATEMENT \| \| 1 \| 26 \| 7 (0)\| 00:00:01 \| \| 1 \| TEMP TABLE TRANSFORMATION \| \| \| \| \| \| \| 2 \| LOAD AS SELECT \| SYS_TEMP_0FD9D6610_6641830 \| \| \| \| \| \| 3 \| TABLE ACCESS FULL \| TT1 \| 1 \| 3 \| 3 (0)\| 00:00:01 \| \| 4 \| MERGE JOIN CARTESIAN \| \| 1 \| 26 \| 4 (0)\| 00:00:01 \| \| 5 \| VIEW \| \| 1 \| 13 \| 2 (0)\| 00:00:01 \| \| 6 \| TABLE ACCESS FULL \| SYS_TEMP_0FD9D6610_6641830 \| 1 \| 3 \| 2 (0)\| 00:00:01 \| \| 7 \| BUFFER SORT \| \| 1 \| 13 \| 4 (0)\| 00:00:01 \| \| 8 \| VIEW \| \| 1 \| 13 \| 2 (0)\| 00:00:01 \| \| 9 \| TABLE ACCESS FULL \| SYS_TEMP_0FD9D6610_6641830 \| 1 \| 3 \| 2 (0)\| 00:00:01 \| ---------------------------------------------------------------------------------------------------------

Spoiler:: Hidden text

Select Show


SQL> create table tt1 as select 1 id from dual;

Table created.

SQL> exec dbms_stats.gather_table_stats('','TT1');

PL/SQL procedure successfully completed.

SQL> explain plan for
  2  with gen as (select * from tt1)
  3  select * from gen,gen g2;

Explained.

SQL> @xplan

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
Plan hash value: 486748850

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |     6 |     6   (0)| 00:00:01 |
|   1 |  MERGE JOIN CARTESIAN|      |     1 |     6 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | TT1  |     1 |     3 |     3   (0)| 00:00:01 |
|   3 |   BUFFER SORT        |      |     1 |     3 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL | TT1  |     1 |     3 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

SQL> explain plan for
  2  with gen as (select * from tt1 where 1=1)
  3  select * from gen,gen g2;

Explained.

SQL> @xplan

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
Plan hash value: 2673059801

---------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                       | Rows  | Bytes | Cost (%CPU)| Time  |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                            |     1 |    26 |     7   (0)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION |                            |       |       |            |       |
|   2 |   LOAD AS SELECT           | SYS_TEMP_0FD9D6610_6641830 |       |       |            |       |
|   3 |    TABLE ACCESS FULL       | TT1                        |     1 |     3 |     3   (0)| 00:00:01 |
|   4 |   MERGE JOIN CARTESIAN     |                            |     1 |    26 |     4   (0)| 00:00:01 |
|   5 |    VIEW                    |                            |     1 |    13 |     2   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6610_6641830 |     1 |     3 |     2   (0)| 00:00:01 |
|   7 |    BUFFER SORT             |                            |     1 |    13 |     4   (0)| 00:00:01 |
|   8 |     VIEW                   |                            |     1 |    13 |     2   (0)| 00:00:01 |
|   9 |      TABLE ACCESS FULL     | SYS_TEMP_0FD9D6610_6641830 |     1 |     3 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

Update: I did some additional tests and found:

with “table()” but without “xmltable” materialization occurs always regardless of existence of predicates or another tables in subquery factoring clause
with “xmltable” behavior is very strange – decision about materialization depends from ‘columns …’ clause: when it exists – materialization occurs, if not – not occurs.
with “selects a subset of table columns” as David Aldridge said – decision still depends from predicates existence

Spoiler:: Tests with table() were like this:	Select Show
with t as (select * from table(cast(:a as sys.ku$_vcnt)) /where 1=0/ ) select count() from t,t t2; with t as (select from t10,table(cast(:a as sys.ku$_vcnt)) /where 1=0/ ) select count(*) from t,t t2;

Spoiler:: Tests with table() were like this:

Select Show

with t as (select * from table(cast(:a as sys.ku$_vcnt)) /*where 1=0*/ )
select count(*) from t,t t2;
with t as (select * from t10,table(cast(:a as sys.ku$_vcnt)) /*where 1=0*/ )
select count(*) from t,t t2;

Spoiler:: Tests with xmltable() were like this:	Select Show
with t as (select * from t10,xmltable(:a ) ttt where 1=1) select count() from t, t t1; with t as (select from t10,xmltable(:a columns n int) ttt where 1=0) select count() from t, t t1; with t as (select/+ no_merge / from table(cast(:a as sys.ku$_vcnt)),xmltable(:a) where 1=0 ) select count(*) from t,t t2;

Spoiler:: Tests with xmltable() were like this:

Select Show

with t as (select * from t10,xmltable(:a ) ttt where 1=1)
select count(*)
from t, t t1;
with t as (select * from t10,xmltable(:a columns n int) ttt where 1=0)
select count(*)
from t, t t1;
with t as (select/*+ no_merge */ * from table(cast(:a as sys.ku$_vcnt)),xmltable(:a) where 1=0 )
select count(*) from t,t t2;

Spoiler:: Test with 2 from 1000 columns	Select Show
declare c varchar2(32767):='create table t_1000_cols as select '; c2 varchar2(32767); begin for i in 1..1000 loop c2:=c2\|\|',lpad(1,4000,1) c'\|\|i; end loop; c:=c\|\|ltrim(c2,',')\|\|' from dual connect by level<=100'; execute immediate c; end; / exec dbms_stats.gather_table_stats('','T_1000_COLS'); alter session set tracefile_identifier = mat1000; alter session set events='10053 trace name context forever, level 1'; with t as (select c1,c2 from t_1000_cols) select count() from t, t t2; with t as (select c1,c2 from t_1000_cols where 1=1) select count() from t, t t2;

Spoiler:: Test with 2 from 1000 columns

Select Show

declare 
  c  varchar2(32767):='create table t_1000_cols as select ';
  c2 varchar2(32767);
begin
  for i in 1..1000 loop
    c2:=c2||',lpad(1,4000,1) c'||i;
  end loop;
  c:=c||ltrim(c2,',')||' from dual connect by level<=100';
  execute immediate c;
end;
/
exec dbms_stats.gather_table_stats('','T_1000_COLS');
alter session set tracefile_identifier = mat1000;
alter session set events='10053 trace name context forever, level 1';

with t as (select c1,c2 from t_1000_cols)
select count(*)
from t, t t2;

with t as (select c1,c2 from t_1000_cols where 1=1)
select count(*)
from t, t t2;

Bug in documentation about dbms_stats.gather_schema_stats or in the dbms_stats itself

Posted on January 26, 2013 by Sayan Malakshinov Posted in bug, documentation, oracle, statistics, undocumented 2 Comments

Recently I had to gather pending stats with Object Filter List by several objects, so I copied example from documentation and was surprised: instead of gather stats by specified filter list, oracle started to gather stats for all tables in current_schema! And ‘filter list’ applies only with dbms_stats.GATHER_DATABASE_STATS

UPD: Jared Still gave link to registered bug id, which i couldn’t find in MOS before:

Bug 12754926 – DBMS_STATS.gather_schema_stats does not work with an obj_filter_list parameter specified [ID 12754926.8]
Modified:04-Jan-2012 Type:PATCH Status:PUBLISHED
This issue is fixed in 11.2.0.4 (Future Patch Set)

Spoiler:: Little example	Select Show
SQL> exec dbms_stats.delete_schema_stats('HR'); PL/SQL procedure successfully completed. Elapsed: 00:00:01.31 SQL> begin 2 for r in (select table_name 3 from dba_tables t 4 where t.owner='XTENDER' 5 and table_name like 'TMP%' 6 )loop 7 dbms_stats.delete_table_stats('XTENDER',r.TABLE_NAME); 8 end loop; 9 end; 10 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.31 SQL> select 2 owner 3 ,table_name 4 ,num_rows 5 ,last_analyzed 6 from dba_tab_statistics t 7 where owner='XTENDER' and table_name like 'TMP%' 8 or owner='HR'; OWNER TABLE_NAME NUM_ROWS LAST_ANALYZED ------------------------------ ------------------------------ ---------- ------------------- HR COUNTRIES HR DEPARTMENTS HR EMPLOYEES HR JOBS HR JOB_HISTORY HR LOCATIONS HR REGIONS XTENDER TMP XTENDER TMP1 XTENDER TMP_CONTENT XTENDER TMP_DATA XTENDER TMP_DOCUMENT XTENDER TMP_EXCH 13 rows selected. Elapsed: 00:00:00.11 SQL> col dt new_value dt SQL> select sysdate dt from dual; DT ------------------- 2013-01-27 00:30:21 1 row selected. Elapsed: 00:00:00.00 SQL> DECLARE 2 filter_lst DBMS_STATS.OBJECTTAB := DBMS_STATS.OBJECTTAB(); 3 BEGIN 4 filter_lst.extend(2); 5 filter_lst(1).ownname := 'XTENDER'; 6 filter_lst(1).objname := 'TMP%'; 7 filter_lst(2).ownname := 'HR'; 8 DBMS_STATS.GATHER_SCHEMA_STATS(NULL, obj_filter_list => filter_lst, 9 options => 'gather'); 10 END; 11 / PL/SQL procedure successfully completed. Elapsed: 00:03:16.89 SQL> select owner,table_name 2 from dba_tables t 3 where t.LAST_ANALYZED>timestamp'&dt'+0; OWNER TABLE_NAME ------------------------------ ------------------------------ XTENDER TT XTENDER TR_LOG XTENDER IOT1 ...[skipped 171 rows]... XTENDER DEPARTMENTS 175 rows selected. Elapsed: 00:00:01.04

Spoiler:: Little example

Select Show

SQL> exec dbms_stats.delete_schema_stats('HR');

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.31
SQL> begin
  2    for r in (select table_name
  3              from dba_tables t
  4              where t.owner='XTENDER'
  5              and table_name like 'TMP%'
  6    )loop
  7       dbms_stats.delete_table_stats('XTENDER',r.TABLE_NAME);
  8     end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.31
SQL> select
  2    owner
  3   ,table_name
  4   ,num_rows
  5   ,last_analyzed
  6  from dba_tab_statistics t
  7  where owner='XTENDER' and table_name like 'TMP%'
  8        or owner='HR';

OWNER                          TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ---------- -------------------
HR                             COUNTRIES
HR                             DEPARTMENTS
HR                             EMPLOYEES
HR                             JOBS
HR                             JOB_HISTORY
HR                             LOCATIONS
HR                             REGIONS
XTENDER                        TMP
XTENDER                        TMP1
XTENDER                        TMP_CONTENT
XTENDER                        TMP_DATA
XTENDER                        TMP_DOCUMENT
XTENDER                        TMP_EXCH

13 rows selected.

Elapsed: 00:00:00.11
SQL> col dt new_value dt
SQL> select sysdate dt from dual;

DT
-------------------
2013-01-27 00:30:21

1 row selected.

Elapsed: 00:00:00.00
SQL> DECLARE
  2        filter_lst  DBMS_STATS.OBJECTTAB := DBMS_STATS.OBJECTTAB();
  3      BEGIN
  4        filter_lst.extend(2);
  5        filter_lst(1).ownname := 'XTENDER';
  6        filter_lst(1).objname := 'TMP%';
  7        filter_lst(2).ownname := 'HR';
  8        DBMS_STATS.GATHER_SCHEMA_STATS(NULL, obj_filter_list => filter_lst,
  9                                       options => 'gather');
 10      END;
 11  /

PL/SQL procedure successfully completed.

Elapsed: 00:03:16.89
SQL> select owner,table_name
  2  from dba_tables t
  3  where t.LAST_ANALYZED>timestamp'&dt'+0;

OWNER                          TABLE_NAME
------------------------------ ------------------------------
XTENDER                        TT
XTENDER                        TR_LOG
XTENDER                        IOT1
...[skipped 171 rows]...
XTENDER                        DEPARTMENTS

175 rows selected.

Elapsed: 00:00:01.04

PS. Also there is a typo in the example from oracle documentation: ‘gather_stale’ instead of ‘gather stale’ – underline instead of blank space.

Differences between integer(int) in SQL and PL/SQL

Posted on November 10, 2012 by Sayan Malakshinov Posted in oracle, undocumented Leave a comment

Very simple example:

create table t_integer(i integer);
insert into t_integer values(1e125);
select * from t_integer;

declare
  i1 integer;
begin 
  select i into i1 from t_integer;
end;
/
declare
  i2 t_integer.i%type;
begin 
  select i into i2 from t_integer;
end;
/

Although variable and the column specified as “integer”, but in both blocks you will get error: ORA-06502: PL/SQL: numeric or value error: number precision too large.
Same error will be with “int“.
Describe shows wrong datatype:

> desc t_integer;
 Name                        Null?    Type
 --------------------------- -------- ----------
 I                                    NUMBER(38)

Really it would be number without precision and scale=0. You can see it in dba_tab_columns.
sys.standard:

  subtype INTEGER is NUMBER(38,0);
  subtype INT is INTEGER;

Also a couple simple good-known but often forgotten things:
1. integer as parameter type or return type:

SQL> create function f_integer(i integer)
  2    return integer is
  3  begin
  4    return i;
  5  end;
  6  /

Function created.

SQL> select f_integer(1/3) from dual;

F_INTEGER(1/3)
--------------
    .333333333

1 row selected.

2. Old finding from sql.ru – no casting in assignments (from 10.2 till 11.2, was found by Elic):

declare
  numberVar  number        := 1/3;
  numberVar2 number(20,10) := 1/3;
  procedure Test(Label varchar2, Value int)
  is
    LocalVar int := Value;
  begin
    dbms_output.put_line(Label || '         : ' || Value);
    dbms_output.put_line(Label || ' assigned: ' || LocalVar);
  end Test;
begin
  Test('|                 const', 1/3);
  Test('|            number var', numberVar);
  Test('|constrained number var', numberVar2);
end;
/
|                 const         : .3333333333333333333333333333333333333333
|                 const assigned: .3333333333333333333333333333333333333333
|            number var         : .3333333333333333333333333333333333333333
|            number var assigned: .3333333333333333333333333333333333333333
|constrained number var         : .3333333333
|constrained number var assigned: .3333333333

PL/SQL procedure successfully completed.