Monthly Archives: March 2013

SQL*Plus tips. #1

Posted on by Posted in oracle, SQL*Plus, SQL*PLus tips 5 Comments

If you are using SQL*Plus, you are likely to use the input parameters. And if you omit one of them, SQL*Plus will show prompt for it, like this:

SQL> get test.sql
  1  select 'Input variable 1 = &1' from dual
  2  union all
  3  select 'Input variable 2 = &2' from dual
  4  union all
  5* select 'Input variable 3 = &3' from dual
SQL> @test var1 var2
Enter value for 3:

'INPUTVARIABLE1=VAR1'
-----------------------
Input variable 1 = var1
Input variable 2 = var2
Input variable 3 =

Elapsed: 00:00:00.01

It is allright, if all variables are needed, but what if we don’t want to press enter for all omitted variables or specify they(especially if script can be start in silent, non interactive mode) and want to use default values for omitted variables or these variables can be unnecessary?
Strictly speaking, there are many different techniques for solving it, see some of them:

Comma-separated params 

SQL> get test1
  1  col var1 new_value var1 noprint
  2  col var2 new_value var2 noprint
  3  col var3 new_value var3 noprint
  4  set termout off
  5  with any_splitting_technique as (
  6       select *
  7       from xmltable('ora:tokenize(.,",")[position()>1]'
  8                      passing ','||'&1'
  9                      columns
 10                        i for ordinality
 11                       ,"." varchar2(30)
 12                    )
 13  )
 14  select
 15    nvl("1",'default1') var1
 16   ,nvl("2",'default2') var2
 17   ,nvl("3",'default3') var3
 18  from any_splitting_technique
 19  pivot (max(".") for i in (1,2,3))
 20  /
 21  set termout on
 22* prompt var1 = &var1, var2 = &var2, var3 = &var3;
SQL> @test1 1,2,3
var1 = 1, var2 = 2, var3 = 3
SQL> @test1 1,2
var1 = 1, var2 = 2, var3 = default3

[collapse]

With SPOOL and DEFINE 

SQL> get test2
  1  set termout off
  2  spool tmp.sql
  3  def 1
  4  def 2
  5  def 3
  6  spool off
  7  col var1 new_value var1
  8  col var2 new_value var2
  9  col var3 new_value var3
 10  with tmp as (
 11  select '
 12  @tmp.sql
 13  ' params from dual
 14  )
 15  select
 16    nvl(regexp_substr(params,'DEFINE 1\s+ = "([^"]*)',1,1,'i',1),'default1') var1
 17  , nvl(regexp_substr(params,'DEFINE 2\s+ = "([^"]*)',1,1,'i',1),'default2') var2
 18  , nvl(regexp_substr(params,'DEFINE 3\s+ = "([^"]*)',1,1,'i',1),'default3') var3
 19  from tmp
 20  ;
 21  col var1 clear;
 22  col var2 clear;
 23  col var3 clear;
 24  set termout on
 25  prompt var1 = &var1, var2 = &var2, var3 = &var3;
 26  undef 1
 27  undef 2
 28* undef 3
 29  .
SQL> @test2 1 2 3
var1 = 1, var2 = 2, var3 = 3
SQL> @test2 1 2
var1 = 1, var2 = 2, var3 = default3

[collapse]

Last example also shows a very useful way to read file into a variable.
But i think, the best option for initializing parameters is the solution by Vladimir Begun:

SQL> get test3
  1  set termout off
  2  COLUMN 1 NEW_VALUE 1    noprint
  3  COLUMN 2 NEW_VALUE 2    noprint
  4  COLUMN 3 NEW_VALUE 3    noprint
  5  SELECT '' "1", '' "2", '' "3" FROM dual WHERE 1=0;
  6  SELECT nvl('&1','default1') "1"
  7       , nvl('&2','default2') "2"
  8       , nvl('&3','default3') "3"
  9    FROM dual;
 10  col var1 clear;
 11  col var2 clear;
 12  col var3 clear;
 13  set termout on
 14  prompt var1 = &1, var2 = &2, var3 = &3;
 15  undef 1
 16  undef 2
 17* undef 3
 18  .
SQL> @test3 1 2 3
var1 = 1, var2 = 2, var3 = 3
SQL> @test3 1 2
var1 = 1, var2 = 2, var3 = default3

So i can create 2 include files – for execution at the start and at the end of all scripts. I created directory “inc” for include files and files:

inc/s_begin.sql 

store set splus_restore.sql replace
set termout off
COLUMN  1  NEW_VALUE  1    noprint
COLUMN  2  NEW_VALUE  2    noprint
COLUMN  4  NEW_VALUE  4    noprint
COLUMN  3  NEW_VALUE  3    noprint
COLUMN  5  NEW_VALUE  5    noprint
COLUMN  6  NEW_VALUE  6    noprint
COLUMN  7  NEW_VALUE  7    noprint
COLUMN  8  NEW_VALUE  8    noprint
COLUMN  9  NEW_VALUE  9    noprint
COLUMN 10  NEW_VALUE 10    noprint
COLUMN 11  NEW_VALUE 11    noprint
COLUMN 12  NEW_VALUE 12    noprint
COLUMN 13  NEW_VALUE 13    noprint
COLUMN 14  NEW_VALUE 14    noprint
COLUMN 15  NEW_VALUE 15    noprint
COLUMN 16  NEW_VALUE 16    noprint


SELECT ''  "1", '' "5", ''  "9", '' "13"
      ,''  "2", '' "6", '' "10", '' "14"
      ,''  "3", '' "7", '' "11", '' "15"
      ,''  "4", '' "8", '' "12", '' "16"
  FROM dual
 WHERE 1=0;
set termout on;

[collapse]

and
inc/s_end.sql 

undef 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
@splus_restore;

[collapse]
And for example get_index.sql:
get_index.sql 

@inc/s_begin;
col table_owner format a12
col column_name format a30
col index_owner format a12
col index_name  format a30

col "#" format 99
break on table_owner on table_name on index_owner on index_name on partition_name on mbytes on bytes on blocks
select 
         ic.table_owner
        ,ic.table_name
        ,ic.index_owner
        ,ic.index_name
        ,s.partition_name
        ,round(s.bytes/1024/1024) mbytes
        ,s.blocks
        ,ic.column_position "#"
        ,decode(ic.column_position,1,'','  ,')||ic.column_name column_name
from dba_ind_columns ic 
    ,dba_segments s
where
     upper(ic.table_name) like upper('&1')
 and upper(ic.table_owner) like nvl(upper('&2'),'%')
 and s.owner        = ic.index_owner
 and s.segment_name = ic.index_name
order by
         1,2,3,4,8
/
clear break;
col "#" clear;
@inc/s_end;

[collapse]

Sample output 
SQL> @get_indexes wrh$%tab%stat xtender
Wrote file splus_restore.sql

no rows selected

Elapsed: 00:00:05.79
SQL> @get_indexes wrh$%undostat
Wrote file splus_restore.sql

TABLE_OWNER  TABLE_NAME     INDEX_OWNER  INDEX_NAME        PARTITION_NAME  MBYTES  BLOCKS   # COLUMN_NAME
------------ -------------- ------------ ----------------- -------------- ------- ------- --- --------------------
SYS          WRH$_UNDOSTAT  SYS          WRH$_UNDOSTAT_PK                       0      16   1 BEGIN_TIME
                                                                                            2   ,END_TIME
                                                                                            3   ,DBID
                                                                                            4   ,INSTANCE_NUMBER

[collapse]
As you see i can omit owner parameter and in this case it will search in all schemas.

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

Posted on by Posted in adaptive serial direct path reads, CBO, oracle, undocumented 1 Comment

Previously i showed not obvious example with hint “INDEX_STATS(“OWNER”.”TABLE_NAME”, “INDEX_NAME”, scale, blocks=X, rows=Y)“. Strictly speaking i don’t know how exactly cbo calculates number of index leaf blocks in that case: in those examples they was 1981 for “blocks=1, rows=50” and 49525 for “blocks=5, rows=10”.
But i know that with “INDEX_STATS(“OWNER”.”TABLE_NAME”, “INDEX_NAME”, scale, blocks=X)” i can set exact blocks number.
Also those test-cases didn’t show when occurs decision changing. So todays my test will show it.

UPD: btw previously I wrote that _very_large_object_threshold is a percentage of _db_block_buffers and Igor Usoltsev have tested and confirmed my guess

Test code 

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

Unresolved quiz: Avoiding in-list iterator

Posted on by Posted in CBO, oracle, query optimizing 11 Comments

A couple days ago i had very interesting quiz, which is not resolved yet.
Look at this simplified query:

  select *
  from xt1,xt2
  where
       xt1.b=10
   and xt1.a=xt2.a
   and xt2.b in (1,2);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 2715236140

----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |   100 | 36900 |   501   (0)| 00:00:07 |
|   1 |  NESTED LOOPS                 |        |       |       |            |          |
|   2 |   NESTED LOOPS                |        |   100 | 36900 |   501   (0)| 00:00:07 |
|   3 |    TABLE ACCESS BY INDEX ROWID| XT1    |   100 | 31000 |   101   (0)| 00:00:02 |
|*  4 |     INDEX RANGE SCAN          | IX_XT1 |   100 |       |     1   (0)| 00:00:01 |
|   5 |    INLIST ITERATOR            |        |       |       |            |          |
|*  6 |     INDEX RANGE SCAN          | IX_XT2 |     1 |       |     3   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID | XT2    |     1 |    59 |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("XT1"."B"=10)
   6 - access("XT1"."A"="XT2"."A" AND ("XT2"."B"=1 OR "XT2"."B"=2))
Full test case 
create table xt1 as
select 
   level a
 , mod(level,1000) b
 , lpad(1,300,1) padding 
from dual
connect by level<=1e5;

create index ix_xt1 on xt1(b);

create table xt2 as
select 
   level        a
 , mod(level,5) b
 , lpad(1,50,1) padding 
from dual
connect by level<=1e6;

alter table xt2 
  add constraint uq_xt2
  unique (a)
  using index(create index ix_xt2 on xt2(a,b));

exec dbms_stats.gather_table_stats('','XT1',cascade=>true);
exec dbms_stats.gather_table_stats('','XT2',cascade=>true);

explain plan for 
select *
from xt1,xt2
where 
     xt1.b=10
 and xt1.a=xt2.a
 and xt2.b in (1,2);

@?/rdbms/admin/utlxpls.sql

[collapse]

As you see, in such queries cbo always generating plans with INLIST ITERATOR, and it is reasonably in cases when there are many rows with different values of field B for most values of A, and this number is much larger than number of values in the “INLIST”. But in such case as shown, will be better to use index range scan with access by A and filter by B:

SQL> select *
  2  from xt1,xt2
  3  where
  4       xt1.b=10
  5   and xt1.a=xt2.a
  6   and xt2.b in (1,2);

no rows selected

Statistics
----------------------------------------------------------
        ...
        505  consistent gets
SQL> -- without inlist iterator:
SQL> select *
  2  from xt1,xt2
  3  where
  4       xt1.b=10
  5   and xt1.a=xt2.a
  6   and xt2.b+0 in (1,2);

no rows selected

Statistics
----------------------------------------------------------
        ...
        305  consistent gets

But how we can do it? I know 5 options:
1. Trace event 10157
2. Rewrite code. for example replacing “b in (1,2)” to “b+0 in (1,2)”
3. Changing query with “Advanced query rewrite” (DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE)
4. Recreating index from xt2(a,b) to xt2(a,1,b)
5. Changing optimizer_mode to “rule” through hint or SQL profile/baseline

But unfortunately all of them are inapplicable for the my real problem, because i cannot for some reasons rewrite query or change query with advanced rewrite, cannot recreate/add index, and can’t change optimizer_mode, because execution plan for the real query will become worst than plan generated with CBO with inlist iterator(some operations aren’t exists in RBO).

Could anybody suggest any another solution?

UPDATE #1:
This bug is fixed now in 12.2, and patch 16516751 is available now for 11.2.0.3 Solaris64.
Changes:
1. CBO can consider filters in such cases now
2. Hint NUM_INDEX_KEYS fixed and works fine

UPDATE #2:
Very interesting solution by Igor Usoltsev(in russian):
Ignored hint USE_CONCAT(OR_PREDICATES(N)) allows to avoid inlist iterator.
Example:

select--+ USE_CONCAT(OR_PREDICATES(32767))
 * from xt1,xt2
where
     xt1.b=10
 and xt1.a=xt2.a
 and xt2.b in (1,2)
/

Plan hash value: 2884586137          -- good plan:
 
----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |       |       |   401 (100)|          |
|   1 |  NESTED LOOPS                 |        |       |       |            |          |
|   2 |   NESTED LOOPS                |        |   100 | 36900 |   401   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| XT1    |   100 | 31000 |   101   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IX_XT1 |   100 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | IX_XT2 |     1 |       |     2   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | XT2    |     1 |    59 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("XT1"."B"=10)
   5 - access("XT1"."A"="XT2"."A")
       filter(("XT2"."B"=1 OR "XT2"."B"=2))

From 10053 trace on nonpatched 11.2.0.3:
inlist_concat_diff_10053

Deterministic function vs scalar subquery caching. Part 3

Posted on by Posted in deterministic functions, oracle, scalar subquery caching 1 Comment

In previous parts i already point out that:

  1. Both mechanisms are based on hash functions.
  2. Deterministic caching depends on fetch size(arraysize) – results cached only within one fetch call, ssc has no this limitation.
  3. Hash collisions depends on the single parameter “_query_execution_cache_max_size” for both mechanizms, but they are more frequent in SSC.
  4. Oracle doesn’t keep last result of deterministic functions as it does for scalar subquery caching
  5. Caching of deterministic functions results turns off after a certain number of attempts to get the value from the cache. But SSC always returns results from cache if values already cached.

Upd 2015-02-19:
A couple additions about deterministic functions:

Today’s post is just addition to previous topics:

I wrote about turning caching off after many unsuccessfull attempts to get value from cache, but i didn’t say what it is the number. In fact caching of deterministic functions also depends on two another hidden parameters:

SQL> @param_ plsql%cach

NAME                                 VALUE        DEFLT    TYPE       DESCRIPTION
------------------------------------ ------------ -------- ---------- ------------------------------------------------------------------
_plsql_cache_enable                  TRUE         TRUE     boolean    PL/SQL Function Cache Enabled
_plsql_minimum_cache_hit_percent     20           TRUE     number     plsql minimum cache hit percentage required to keep caching active

First parameter “_plsql_cache_enable” is just a parameter which enables/disables this caching mechanism.
But the second parameter – “_plsql_minimum_cache_hit_percent” – is responsible for the percentage of unsuccessful attempts which disables caching.

I will show their effects with the example from the previous post:

-- set this parameter to big value for maximizing caching:
alter session set "_query_execution_cache_max_size" = 131072;
-- log table clearing:
truncate table t_params;
-- test with percentage = 50
alter session set "_plsql_minimum_cache_hit_percent"=50;
select sum(f_deterministic(n)) fd
from
  xmltable('1 to 10000,1 to 10000'
           columns n int path '.'
          );
 
select 10000-count(count(*)) "Count of cached results"
from t_params
group by p
having count(*)>1;
/*
Count of cached results
-----------------------
                      0
*/
-- now i change cache hit percentage parameter to 0:
alter session set "_plsql_minimum_cache_hit_percent"=0;
truncate table t_params;
select sum(f_deterministic(n)) fd
from
  xmltable('1 to 10000,1 to 10000'
           columns n int path '.'
          );
 
select 10000-count(count(*)) "Count of cached results"
from t_params
group by p
having count(*)>1;
/*
Count of cached results
-----------------------
                   2039
*/

How works optimization of loops in PL/SQL in 11g: Part 1. Deterministic functions

Posted on by Posted in deterministic functions, oracle, PL/SQL optimization 4 Comments

As you know, since 10g Oracle can optimize PL/SQL loops with moving code out of loops for reducing number of identical code executions.
If you have not read yet about it, I advise you to first read these two articles:
1. “PLSQL_OPTIMIZE_LEVEL: The optimization strategy of Oracle” by Saurabh K. Gupta
2. “PL/SQL optimisation in 10g” by Adrian Billington

But since 11g Oracle also can optimize code with deterministic functions too. For this to happen, code must meet the following conditions:
1. PLSQL_OPTIMIZE_LEVEL greater or equal 2
2. Parameters should not be changed in the loop body. Strictly speaking, there should not be any assignment of parameters.
3. Should not be any implicit conversions of parameters in function call, i.e. all variables/literals must be the same type as input parameters declared.
4. Should not be any call of non-deterministic functions (except some standard sql functions like to_date, to_char, nvl) or procedures in the loop

Note that this rules concerns only same scope level as this loop and not inner loops or another blocks.

Let me show how it works on simple examples:

Controlling “direct path reads” decision with INDEX_STATS/table_stats

Posted on by Posted in adaptive serial direct path reads, CBO, oracle 3 Comments

Since 11.2.0.2 direct path read decision on full scans(FTS/IFFS) can be based on the statistics.

And if my test is correct, it appears that we can control this behavior on query level with changing number of blocks through index_stats/table_stats outlines:

UPD: I did a little change of the test case for avoiding impact of hard parse on main test.

Test case 

drop table xt_iffs purge;
spool &_spools/iffs_test.sql;
@param_ _direct_read_decision_statistics_driven
@param_ _small_table_threshold
SELECT name,block_size,buffers FROM v$buffer_pool;

create table xt_iffs as select level a,mod(level,100) b,lpad(1,100,1) c from dual connect by level<=1e5;
create index ix_iffs on xt_iffs(a);
exec dbms_stats.set_table_stats('','XT_IFFS',numrows => 1e6,numblks => 5e5,avgrlen => 800);
exec dbms_stats.set_index_stats('','IX_IFFS',numrows => 1e6,numlblks => 1e4);
set termout off echo off feed off timing off;
------------------- 1 run for avoiding hard parse in main test
alter system flush buffer_cache;
select/*+ index_ffs(t IX_IFFS) */ sum(a) from xt_iffs;

alter system flush buffer_cache;
select/*+ 
  BEGIN_OUTLINE_DATA
    INDEX_FFS(T IX_IFFS) 
    INDEX_STATS("XTENDER"."XT_IFFS", "IX_IFFS", scale, blocks=5, rows=10) 
  END_OUTLINE_DATA
  */ 
  sum(a) 
from xt_iffs t;

alter system flush buffer_cache;
select/*+ 
  BEGIN_OUTLINE_DATA
    INDEX_FFS(T IX_IFFS) 
    INDEX_STATS("XTENDER"."XT_IFFS", "IX_IFFS", scale, blocks=1, rows=50)  
  END_OUTLINE_DATA
  */ 
  sum(a) 
from xt_iffs t;
------------------- Main test with statistics: ----------------------------------------------
exec xt_runstats.init(p_latches => false);
alter system flush buffer_cache;
select/*+ index_ffs(t IX_IFFS) */ sum(a) from xt_iffs;
exec xt_runstats.snap;

alter system flush buffer_cache;
select/*+ 
  BEGIN_OUTLINE_DATA
    INDEX_FFS(T IX_IFFS) 
    INDEX_STATS("XTENDER"."XT_IFFS", "IX_IFFS", scale, blocks=5, rows=10) 
  END_OUTLINE_DATA
  */ 
  sum(a) 
from xt_iffs t;
exec xt_runstats.snap;

alter system flush buffer_cache;
select/*+ 
  BEGIN_OUTLINE_DATA
    INDEX_FFS(T IX_IFFS) 
    INDEX_STATS("XTENDER"."XT_IFFS", "IX_IFFS", scale, blocks=1, rows=50)  
  END_OUTLINE_DATA
  */ 
  sum(a) 
from xt_iffs t;
exec xt_runstats.snap;

set termout on echo on serverout on;

exec xt_runstats.print(p_stats_mask => 'reads|direct',p_sta_diff_pct => 0);
spool off;

[collapse]

Result 


NAME                                     VALUE        DEFLT        TYPE         DESCRIPTION
---------------------------------------- ------------ ------------ ------------ ------------------------------------------------------------
_direct_read_decision_statistics_driven  TRUE         TRUE         boolean      enable direct read decision based on optimizer statistics

Elapsed: 00:00:00.20

NAME                                     VALUE        DEFLT        TYPE         DESCRIPTION
---------------------------------------- ------------ ------------ ------------ ------------------------------------------------------------
_small_table_threshold                   166          TRUE         number       lower threshold level of table size for direct reads

Elapsed: 00:00:00.21

NAME                                     BLOCK_SIZE    BUFFERS
---------------------------------------- ---------- ----------
DEFAULT                                        8192        491

Elapsed: 00:00:00.19

Table created.

Elapsed: 00:00:00.29

Index created.

Elapsed: 00:00:00.46

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.15

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.13
SQL>
SQL> exec xt_runstats.print(p_stats_mask => 'reads|direct',p_sta_diff_pct => 0);
################     Results:      ##################
Run #  01 ran in 48 hsecs
Run #  02 ran in 47 hsecs
Run #  03 ran in 48 hsecs
############################################################################################
Statistics                               | Run # 1        | Run # 2        | Run # 3
############################################################################################
session logical reads................... |            229 |            227 |            229
session logical reads in local numa grou |              0 |              0 |              0
session logical reads in remote numa gro |              0 |              0 |              0
db block gets direct.................... |              0 |              0 |              0
consistent gets direct.................. |              0 |            223 |              0
physical reads.......................... |            224 |            224 |            224
physical reads cache.................... |            224 |              1 |            224
physical reads direct................... |              0 |            223 |              0
physical writes direct.................. |              0 |              0 |              0
physical reads direct temporary tablespa |              0 |              0 |              0
physical writes direct temporary tablesp |              0 |              0 |              0
recovery array reads.................... |              0 |              0 |              0
physical reads cache prefetch........... |            203 |              0 |            203
physical reads prefetch warmup.......... |              0 |              0 |              0
physical reads retry corrupt............ |              0 |              0 |              0
physical reads direct (lob)............. |              0 |              0 |              0
physical writes direct (lob)............ |              0 |              0 |              0
cold recycle reads...................... |              0 |              0 |              0
physical reads for flashback new........ |              0 |              0 |              0
flashback direct read optimizations for  |              0 |              0 |              0
redo size for direct writes............. |              0 |              0 |              0
cell physical IO bytes sent directly to  |              0 |              0 |              0
transaction tables consistent reads - un |              0 |              0 |              0
data blocks consistent reads - undo reco |              0 |              0 |              0
table scans (direct read)............... |              0 |              0 |              0
lob reads............................... |              0 |              0 |              0
index fast full scans (direct read)..... |              0 |              1 |              0
securefile direct read bytes............ |              0 |              0 |              0
securefile direct write bytes........... |              0 |              0 |              0
securefile direct read ops.............. |              0 |              0 |              0
securefile direct write ops............. |              0 |              0 |              0
############################################################################################

[collapse]