SQL*Plus tips. #1

Posted on by Posted in oracle, SQL*Plus, SQL*PLus tips 3,716 Page views 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

[sourcecode language=”sql”]
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
[/sourcecode]

[collapse]

With SPOOL and DEFINE

[sourcecode language=”sql”]
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
[/sourcecode]

[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

[sourcecode language=”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;
[/sourcecode]

[collapse]

and
inc/s_end.sql

[sourcecode language=”sql”]
undef 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
@splus_restore;
[/sourcecode]

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

[sourcecode language=”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;
[/sourcecode]

[collapse]

Sample output

[sourcecode language=”sql”]
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
[/sourcecode]

[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 2,851 Page views 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

[sourcecode language=”sql” collapse=”true”]
/** SQLPLUS Example of controlling adaptive serial direct path reads through SQL profiles.
*/
set serverout on termout on timing off feed off;
clear scr;
def _IF_XT_RUNSTATS_EXISTS="–"
col if_xt_runstats_exists new_value _IF_XT_RUNSTATS_EXISTS noprint;
select decode(count(*),1,’ ‘,’–‘) if_xt_runstats_exists
from all_objects where object_name=’XT_RUNSTATS’ and rownum=1;
/**
* Main test.
* You can use it also in other clients,
* but in that case you have to manually
* set substitution variable _IF_XT_RUNSTATS_EXISTS:
* if you have not this package – to "–"
* otherwise – to space(" ")
* Latest version of XT_RUNSTATS you can get from:
* https://github.com/xtender/xt_runstats
*/

declare

C_SQL_TEXT constant varchar2(300):=’SELECT SUM(A) FROM XT_IFFS T’;
C_PROFILE_NAME constant varchar2(30) :=’PRF_ADPR_TEST’;
v_small_table_threshold int;
v_db_block_buffers int;
v_very_large_object_threshold int;
v__db_cache_size int;

procedure exec(p varchar2) is
e_table_is_not_created_yet exception;
e_index_is_not_created_yet exception;
pragma exception_init(e_table_is_not_created_yet, -942);
pragma exception_init(e_index_is_not_created_yet, -1418);
begin
execute immediate p;
exception
when e_table_is_not_created_yet
or e_index_is_not_created_yet
then null;
end;

/** Creating table and setting stats */
procedure create_table is
begin
exec(‘drop table xt_iffs purge’);
exec(‘create table xt_iffs as
with gen as(
select level a,mod(level,10) b,lpad(1,50,1) c
from dual
connect by level<=1e3
)
select gen.*
from gen,gen gen2′
);
–dbms_stats.set_table_stats(”,’XT_IFFS’,numrows => 1e6,numblks => 5e5,avgrlen => 800);
dbms_stats.gather_table_stats(”,’XT_IFFS’);
end;

/** Recreating index and setting stats */
procedure create_index is
begin
exec(‘drop index ix_iffs’);
exec(‘create index ix_iffs on xt_iffs(a,b)’);
dbms_stats.set_index_stats(”,’IX_IFFS’,numrows => 1e6,numlblks => 1e4);
end;

/** Setting index numblks for query through SQL profile */
procedure set_numblks(p_numblks int) is
e_profile_not_created_yet exception;
pragma exception_init( e_profile_not_created_yet, -13833);
begin
begin
dbms_sqltune.drop_sql_profile(C_PROFILE_NAME);
exception when e_profile_not_created_yet then null;
end;
dbms_sqltune.import_sql_profile(
sql_text => C_SQL_TEXT,
profile => sys.sqlprof_attr(
‘INDEX_FFS(@"SEL$1" "T"@"SEL$1" ("XT_IFFS"."A"))’
,’INDEX_STATS("’||user||’"."XT_IFFS", "IX_IFFS", scale, blocks=’||p_numblks||’)’
),
category => ‘DEFAULT’,
name => C_PROFILE_NAME,
force_match => false,
replace => true
);
end;

procedure test( p_description varchar2
, p_numblks int default null) is

type t_seg_stat is
table of number
index by v$segstat_name.name%type;
— segments statistics by index:
cursor c_stats is
select sn.name,nvl(st.value,0) value
from v$segstat_name sn
,v$segment_statistics st
where
sn.statistic# = st.statistic#(+)
and st.owner(+) = user
and st.object_name(+) =’IX_IFFS’;
— var for previous stats:
v_pre t_seg_stat;
v_delta number;
n number;
begin
dbms_output.put_line(‘-‘);
dbms_output.put_line(‘-‘);
dbms_output.put_line(lpad(‘-‘,150,’-‘));
dbms_output.put_line(lpad(‘-‘,150,’-‘));
dbms_output.put_line( ‘### ‘
|| p_description||’: ‘
||nvl(to_char(p_numblks),’default’)||’ blocks’);
dbms_output.put_line(‘-‘);

create_index;
— if p_numblks is null then default stats used
if p_numblks is not null then
set_numblks(p_numblks);
end if;
execute immediate C_SQL_TEXT into n;
exec(‘alter system flush buffer_cache’);
— saving segment statistics
for r in c_stats loop
v_pre(r.name) := r.value;
end loop;

&_IF_XT_RUNSTATS_EXISTS xt_runstats.init(p_latches => false);
— executing query
execute immediate C_SQL_TEXT into n;
&_IF_XT_RUNSTATS_EXISTS xt_runstats.snap;
&_IF_XT_RUNSTATS_EXISTS xt_runstats.print(
&_IF_XT_RUNSTATS_EXISTS p_stats_mask => ‘(reads (cache|direct)\.)|index fast full scans \((full|direct)’
&_IF_XT_RUNSTATS_EXISTS ,p_sta_diff_pct => 0);

— printing segments stats delta:
for r in c_stats loop
v_delta:= r.value – v_pre(r.name);
if v_delta!=0 then
dbms_output.put_line( rpad(r.name,40,’.’)||v_delta );
end if;
end loop;
end;

procedure load_and_print_params is
begin
select
max(decode(a.ksppinm, ‘_small_table_threshold’ ,b.ksppstvl))
,max(decode(a.ksppinm, ‘_db_block_buffers’ ,b.ksppstvl))
,max(decode(a.ksppinm, ‘_very_large_object_threshold’ ,b.ksppstvl))
,max(decode(a.ksppinm, ‘__db_cache_size’ ,b.ksppstvl))
into v_small_table_threshold,v_db_block_buffers,v_very_large_object_threshold,v__db_cache_size
from
sys.x$ksppi a
,sys.x$ksppcv b
where
a.indx = b.indx
and a.ksppinm in(‘_small_table_threshold’,’_db_block_buffers’,’_very_large_object_threshold’,’__db_cache_size’);
dbms_output.put_line(‘_small_table_threshold = ‘||v_small_table_threshold);
dbms_output.put_line(‘_db_block_buffers = ‘||v_db_block_buffers);
dbms_output.put_line(‘_very_large_object_threshold = ‘||v_very_large_object_threshold);
dbms_output.put_line(‘__db_cache_size = ‘||v__db_cache_size);

end;
begin
create_table;
load_and_print_params;

test( ‘_very_large_object_threshold + 1’
,v_db_block_buffers * v_very_large_object_threshold/100 + 1 );
test( ‘_very_large_object_threshold’
,v_db_block_buffers * v_very_large_object_threshold/100 );
test( ‘_very_large_object_threshold – 1’
,v_db_block_buffers * v_very_large_object_threshold/100 – 1 );

test( ‘_db_block_buffers + 1’
,v_db_block_buffers + 1 );
test( ‘_db_block_buffers – 1’
,v_db_block_buffers – 1 );

test( ‘_small_table_threshold * 5 + 1’
,v_small_table_threshold * 5 + 1 );
test( ‘_small_table_threshold * 5 – 1’
,v_small_table_threshold * 5 – 1 );

test( ‘ 1 block ‘, 1);
test( ‘ Default ‘, null);

test( ‘ Again _very_large_object_threshold + 1’
,v_db_block_buffers * v_very_large_object_threshold/100 + 1 );
test( ‘ Again _very_large_object_threshold’
,v_db_block_buffers * v_very_large_object_threshold/100 );

end;
/
[/sourcecode]

[collapse]

Test results

[sourcecode language=”sql” highlight=”9,17,19,25,31,41,52,62,73,83,94,104,115,125,136,146,157,167,178,188,199,209,221,231″ ruler=”true” collapse=”true”]
_small_table_threshold = 166
_db_block_buffers = 8347
_very_large_object_threshold = 500
__db_cache_size = 4194304
–
–
——————————————————————————
——————————————————————————
### _very_large_object_threshold + 1: 41736 blocks
–
################ Results: ##################
Run # 01 ran in 11 hsecs
##########################################################
Statistics | Run # 1
##########################################################
physical reads cache……………….. | 1
physical reads direct………………. | 2,491
index fast full scans (full)………… | 1
index fast full scans (direct read)….. | 1
##########################################################
–
logical reads………………………2496
physical reads……………………..2492
physical read requests………………86
physical reads direct……………….2491
segment scans………………………1
–
–
——————————————————————————
——————————————————————————
### _very_large_object_threshold: 41735 blocks
–
################ Results: ##################
Run # 01 ran in 6 hsecs
##########################################################
Statistics | Run # 1
##########################################################
physical reads cache……………….. | 2,494
physical reads direct………………. | 0
index fast full scans (full)………… | 1
index fast full scans (direct read)….. | 0
##########################################################
–
logical reads………………………2496
physical reads……………………..2494
physical read requests………………95
segment scans………………………1
–
–
——————————————————————————
——————————————————————————
### _very_large_object_threshold – 1: 41734 blocks
–
################ Results: ##################
Run # 01 ran in 11 hsecs
##########################################################
Statistics | Run # 1
##########################################################
physical reads cache……………….. | 3,386
physical reads direct………………. | 1
index fast full scans (full)………… | 1
index fast full scans (direct read)….. | 0
##########################################################
–
logical reads………………………2512
physical reads……………………..2494
physical read requests………………95
segment scans………………………1
–
–
——————————————————————————
——————————————————————————
### _db_block_buffers + 1: 8348 blocks
–
################ Results: ##################
Run # 01 ran in 6 hsecs
##########################################################
Statistics | Run # 1
##########################################################
physical reads cache……………….. | 2,494
physical reads direct………………. | 0
index fast full scans (full)………… | 1
index fast full scans (direct read)….. | 0
##########################################################
–
logical reads………………………2512
physical reads……………………..2494
physical read requests………………95
segment scans………………………1
–
–
——————————————————————————
——————————————————————————
### _db_block_buffers – 1: 8346 blocks
–
################ Results: ##################
Run # 01 ran in 8 hsecs
##########################################################
Statistics | Run # 1
##########################################################
physical reads cache……………….. | 2,494
physical reads direct………………. | 0
index fast full scans (full)………… | 1
index fast full scans (direct read)….. | 0
##########################################################
–
logical reads………………………2512
physical reads……………………..2494
physical read requests………………95
segment scans………………………1
–
–
——————————————————————————
——————————————————————————
### _small_table_threshold * 5 + 1: 831 blocks
–
################ Results: ##################
Run # 01 ran in 6 hsecs
##########################################################
Statistics | Run # 1
##########################################################
physical reads cache……………….. | 2,494
physical reads direct………………. | 0
index fast full scans (full)………… | 1
index fast full scans (direct read)….. | 0
##########################################################
–
logical reads………………………2512
physical reads……………………..2494
physical read requests………………95
segment scans………………………1
–
–
——————————————————————————
——————————————————————————
### _small_table_threshold * 5 – 1: 829 blocks
–
################ Results: ##################
Run # 01 ran in 7 hsecs
##########################################################
Statistics | Run # 1
##########################################################
physical reads cache……………….. | 2,494
physical reads direct………………. | 0
index fast full scans (full)………… | 1
index fast full scans (direct read)….. | 0
##########################################################
–
logical reads………………………2496
physical reads……………………..2494
physical read requests………………95
segment scans………………………1
–
–
——————————————————————————
——————————————————————————
### 1 block : 1 blocks
–
################ Results: ##################
Run # 01 ran in 6 hsecs
##########################################################
Statistics | Run # 1
##########################################################
physical reads cache……………….. | 2,494
physical reads direct………………. | 0
index fast full scans (full)………… | 1
index fast full scans (direct read)….. | 0
##########################################################
–
logical reads………………………2512
physical reads……………………..2494
physical read requests………………95
segment scans………………………1
–
–
——————————————————————————
——————————————————————————
### Default : default blocks
–
################ Results: ##################
Run # 01 ran in 7 hsecs
##########################################################
Statistics | Run # 1
##########################################################
physical reads cache……………….. | 2,494
physical reads direct………………. | 0
index fast full scans (full)………… | 1
index fast full scans (direct read)….. | 0
##########################################################
–
logical reads………………………2496
physical reads……………………..2494
physical read requests………………95
segment scans………………………1
–
–
——————————————————————————
——————————————————————————
### Again _very_large_object_threshold + 1: 41736 blocks
–
################ Results: ##################
Run # 01 ran in 6 hsecs
##########################################################
Statistics | Run # 1
##########################################################
physical reads cache……………….. | 1
physical reads direct………………. | 2,491
index fast full scans (full)………… | 1
index fast full scans (direct read)….. | 1
##########################################################
–
logical reads………………………2496
physical reads……………………..2492
physical read requests………………86
physical reads direct……………….2491
segment scans………………………1
–
–
——————————————————————————
——————————————————————————
### Again _very_large_object_threshold: 41735 blocks
–
################ Results: ##################
Run # 01 ran in 7 hsecs
##########################################################
Statistics | Run # 1
##########################################################
physical reads cache……………….. | 2,494
physical reads direct………………. | 0
index fast full scans (full)………… | 1
index fast full scans (direct read)….. | 0
##########################################################
–
logical reads………………………2496
physical reads……………………..2494
physical read requests………………95
segment scans………………………1

PL/SQL procedure successfully completed.
[/sourcecode]

[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)

[sourcecode language=”sql”]
/** SQLPLUS Example of controlling adaptive serial direct path reads through SQL profiles.
*/
set serverout on termout on timing off feed off;
clear scr;
/**
* Main test.
*/

declare

C_SQL_TEXT constant varchar2(300):=’SELECT SUM(A) FROM XT_IFFS T’;
v_small_table_threshold int;
v_db_block_buffers int;
v_very_large_object_threshold int;
v__db_cache_size int;

procedure exec(p varchar2) is
e_table_is_not_created_yet exception;
e_index_is_not_created_yet exception;
pragma exception_init(e_table_is_not_created_yet, -942);
pragma exception_init(e_index_is_not_created_yet, -1418);
begin
execute immediate p;
exception
when e_table_is_not_created_yet
or e_index_is_not_created_yet
then null;
end;

/** Creating table and setting stats */
procedure create_table is
begin
exec(‘drop table xt_iffs purge’);
exec(‘create table xt_iffs as
with gen as(
select level a,mod(level,10) b,lpad(1,50,1) c
from dual
connect by level<=100
)
select gen.*
from gen,gen gen2′
);
dbms_stats.gather_table_stats(”,’XT_IFFS’,no_invalidate => false);
dbms_stats.set_table_stats(”,’XT_IFFS’,numrows => 1e6,numblks => 5e5,avgrlen => 800,no_invalidate => false);
end;

/** Setting index numblks for query through SQL profile */
procedure set_numblks(p_numblks int) is
e_profile_not_created_yet exception;
pragma exception_init( e_profile_not_created_yet, -13833);
begin
dbms_stats.set_table_stats(user,’XT_IFFS’,numrows => 1e6,numblks => p_numblks, avgrlen => 800,no_invalidate => false);
end;

procedure test( p_description varchar2
, p_numblks int default null) is
v_delta number;
n number;

type t_ses_stat is
table of number
index by v$segstat_name.name%type;

— var for previous stats:
v_pre t_ses_stat;

— segments statistics by segment name:
cursor c_stats is
select name,value
from v$mystat st join v$statname sn using(STATISTIC#)
where regexp_like(name,’^physical reads (cache|direct)$’);

procedure ses_stats_save is
begin
for r in c_stats loop
v_pre(r.name) := r.value;
end loop;
end ses_stats_save;

procedure ses_stats_print is
begin
for r in c_stats loop
v_delta:= r.value – v_pre(r.name);
if v_delta!=0 then
dbms_output.put_line( rpad(r.name,40,’.’)||v_delta );
end if;
end loop;
end ses_stats_print;

begin
dbms_output.put_line(chr(160));
dbms_output.put_line(lpad(‘-‘,150,’-‘));
dbms_output.put_line( ‘### ‘
|| p_description||’: ‘
||nvl(to_char(p_numblks),’default’)||’ blocks’);
dbms_output.put_line(chr(160));

–create_index;
— if p_numblks is null then default stats used
if p_numblks is not null then
set_numblks(p_numblks);
end if;
execute immediate C_SQL_TEXT into n;
exec(‘alter system flush buffer_cache’);
— saving segment statistics
ses_stats_save;

— executing query
execute immediate C_SQL_TEXT into n;

— printing segments stats delta:
ses_stats_print;
end;

procedure load_and_print_params is
begin
select
max(decode(a.ksppinm, ‘_small_table_threshold’ ,b.ksppstvl))
,max(decode(a.ksppinm, ‘_db_block_buffers’ ,b.ksppstvl))
,max(decode(a.ksppinm, ‘_very_large_object_threshold’ ,b.ksppstvl))
,max(decode(a.ksppinm, ‘__db_cache_size’ ,b.ksppstvl))
into v_small_table_threshold,v_db_block_buffers,v_very_large_object_threshold,v__db_cache_size
from
sys.x$ksppi a
,sys.x$ksppcv b
where
a.indx = b.indx
and a.ksppinm in(‘_small_table_threshold’,’_db_block_buffers’,’_very_large_object_threshold’,’__db_cache_size’);
dbms_output.put_line(‘_small_table_threshold = ‘||v_small_table_threshold);
dbms_output.put_line(‘_db_block_buffers = ‘||v_db_block_buffers);
dbms_output.put_line(‘_very_large_object_threshold = ‘||v_very_large_object_threshold);
dbms_output.put_line(‘__db_cache_size = ‘||v__db_cache_size);

end;
begin
create_table;
load_and_print_params;

test( ‘_very_large_object_threshold + 1’
,v_db_block_buffers * v_very_large_object_threshold/100 + 1 );
test( ‘_very_large_object_threshold’
,v_db_block_buffers * v_very_large_object_threshold/100 );
test( ‘_very_large_object_threshold – 1’
,v_db_block_buffers * v_very_large_object_threshold/100 – 1 );

test( ‘_db_block_buffers + 1’
,v_db_block_buffers + 1 );
test( ‘_db_block_buffers – 1’
,v_db_block_buffers – 1 );

test( ‘_small_table_threshold * 5 + 1’
,v_small_table_threshold * 5 + 1 );
test( ‘_small_table_threshold * 5 – 1’
,v_small_table_threshold * 5 – 1 );

test( ‘_small_table_threshold + 1’
,v_small_table_threshold + 1 );
test( ‘_small_table_threshold’
,v_small_table_threshold );
test( ‘_small_table_threshold – 1’
,v_small_table_threshold – 1 );

test( ‘ 1 block ‘, 1);
test( ‘ Default ‘, null);

test( ‘ Again _very_large_object_threshold + 1’
,v_db_block_buffers * v_very_large_object_threshold/100 + 1 );
test( ‘ Again _very_large_object_threshold’
,v_db_block_buffers * v_very_large_object_threshold/100 );

end;
/

[/sourcecode]

[collapse]

Results

[sourcecode language=”sql” highlight=”43,46,49,52,55,57″]
_small_table_threshold = 2246
_db_block_buffers = 112347
_very_large_object_threshold = 500
__db_cache_size = 939524096

————————————————————————
### _very_large_object_threshold + 1: 561736 blocks

physical reads cache………………..1
physical reads direct……………….87

————————————————————————
### _very_large_object_threshold: 561735 blocks

physical reads cache………………..1
physical reads direct……………….87

————————————————————————
### _very_large_object_threshold – 1: 561734 blocks

physical reads cache………………..1
physical reads direct……………….87

————————————————————————
### _db_block_buffers + 1: 112348 blocks

physical reads cache………………..1
physical reads direct……………….87

————————————————————————
### _db_block_buffers – 1: 112346 blocks

physical reads cache………………..1
physical reads direct……………….87

————————————————————————
### _small_table_threshold * 5 + 1: 11231 blocks

physical reads cache………………..1
physical reads direct……………….87

————————————————————————
### _small_table_threshold * 5 – 1: 11229 blocks

physical reads cache………………..1
physical reads direct……………….87

————————————————————————
### _small_table_threshold + 1: 2247 blocks

physical reads cache………………..1
physical reads direct……………….87

————————————————————————
### _small_table_threshold – 1: 2245 blocks

physical reads cache………………..88

————————————————————————
### 1 block : 1 blocks

physical reads cache………………..88

————————————————————————
### Default : default blocks

physical reads cache………………..88

————————————————————————
### Again _very_large_object_threshold + 1: 561736 blocks

physical reads cache………………..1
physical reads direct……………….87

————————————————————————
### Again _very_large_object_threshold: 561735 blocks

physical reads cache………………..1
physical reads direct……………….87
[/sourcecode]

[collapse]

Full test script: test_table.sql

Unresolved quiz: Avoiding in-list iterator

Posted on by Posted in CBO, oracle, query optimizing 3,641 Page views 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

[sourcecode language=”sql”]
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
[/sourcecode]

[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 2,799 Page views 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 2,835 Page views 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 2,280 Page views 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

[sourcecode language=”sql” highlight=”45,56″]
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;
[/sourcecode]

[collapse]

Result

[sourcecode language=”sql” highlight=”50,69″]

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
############################################################################################
[/sourcecode]

[collapse]

Workaround for deadlock with select for update order by on 11.2.0.2-11.2.0.3

Posted on by Posted in bug, oracle 4,312 Page views 2 Comments

There is well-known bug with “for update order by” on 11.2, when rows locks not in specified order, although the ordering occurs.
I already wrote on my russian blog about the appearance of “buffer sort” in plans with “for update” even if sort order was not specified. And this behavior can be disabled for example by specifying /*+ opt_param( ‘optimizer_features_enable’ ‘11.1.0.7’ ) */.
But if we want to solve problem with deadlock, we need to force index full scan/index range scan ascending with “buffer sort” usage.

UPD: The patch is already available for 11.2.0.2 and 11.2.0.3: Patch 13371104: LOCK ORDER HAS CHANGED BETWEEN 10.2.0.5 AND 11.2.0.2.
Read more: Simple example

Why between to_date(‘1582-10-15′,’yyyy-mm-dd’) and to_date(‘1582-10-04′,’yyyy-mm-dd’) only one day

Posted on by Posted in curious, oracle 2,477 Page views Leave a comment

You may wonder why between these dates only one day:

SQL> select date'1582-10-15'-date'1582-10-04' from dual;

DATE'1582-10-15'-DATE'1582-10-04'
---------------------------------
                                1

SQL> select date'1582-10-05'                   "dt_1"
  2        ,date'1582-10-05' + 1               "dt_1 + 1"
  3        ,date'1582-10-05' - 1               "dt_1 - 1"
  4        ,to_date('1582-10-05','yyyy-mm-dd') "dt_1 and to_date"
  5  from dual;

dt_1               dt_1 + 1           dt_1 - 1           dt_1 and to_date
------------------ ------------------ ------------------ ------------------
October   05, 1582 October   16, 1582 October   04, 1582 October   15, 1582

Over the last 2 months, I gave link to answer several times, so i decided to post it here: http://www.orafaq.com/papers/dates_o.doc

BTW, yet another trick:

SQL> select date'0000-02-29','to_char:'||date'0000-02-29' from dual;

DATE'0000-02-29'    'TO_CHAR:'||DATE'0000-02-29'
------------------- ---------------------------
29.02.0000 00:00:00 to_char:00.00.0000 00:00:00

SQL> select to_date('0000-02-29','yyyy-mm-dd') error from dual;
select to_date('0000-02-29','yyyy-mm-dd') error from dual
               *
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

About unnecessary work with predicate “field=:bind_variable” where bind_variable is null

Posted on by Posted in CBO, oracle, query optimizing 1,963 Page views Leave a comment

Although we know that in the case when we do select from a table and one of the predicates is “field=:bind_variable” and :bind_variable is null, we should not get the rows, oracle not always “think” the same and don’t add upper filter predicate “:bind_variable is not null”, so it can do unnecessary work. It is completely depends from a plan, which will be chosen. Of course this applies to other predicates like >, < or != too. For example, there will not be any reads only if it is an access predicate on index range/unique scan or full table scan on 11.2.0.3 and with gathered stats. If it is FTS on previous versions, then only segment header is read. In others cases oracle will do useless scans. So if bind variable can be null and you want to be sure that oracle will not do futile work in such cases, just add predicate ":bind_variable is not null". This note is just aggregated info from recent question from our forum where i participated

Update:The explanation about segment header reads you can listen from Enkitec.tv by Tanel Poder

Full test code you can download as file – test.sql.

Little example #1

[sourcecode language=”sql”]
set timing off feed off

create table XT_NULLS_TEST(a not null, b not null,c)
as
select
level a
, mod(level,100) b
, lpad(1,100,1) c
from dual
connect by level<=1e5;

create index IX_NULLS_TEST on XT_NULLS_TEST(a);

exec dbms_stats.gather_table_stats(”,’XT_NULLS_TEST’);

——————- Main test with statistics: ———————————————-
— Variable with null:
var v_null number;

exec xt_runstats.init(p_latches => false);

— INDEX RANGE SCAN:
select/*+ INDEX(XT_NULLS_TEST IX_NULLS_TEST) */ count(*) cnt from XT_NULLS_TEST where a > :v_null;
exec xt_runstats.snap(‘IRS’);

— FULL TABLE SCAN:
select/*+ FULL(XT_NULLS_TEST) */ count(*) cnt from XT_NULLS_TEST where a > :v_null;
exec xt_runstats.snap(‘FTS’);

— INDEX FAST FULL SCAN:
select/*+ INDEX_FFS(XT_NULLS_TEST IX_NULLS_TEST) */ count(*) cnt from XT_NULLS_TEST where a > :v_null;
exec xt_runstats.snap(‘IFFS’);

–Results
set serveroutput on
exec xt_runstats.print(p_stats_mask => ‘reads|buff.*gets|consistent gets’,p_sta_diff_pct => 1);

drop table xt_nulls_test purge;
[/sourcecode]
Results:
[sourcecode language=”sql”]
SQL> @test.sql.txt

CNT
———-
0

CNT
———-
0

CNT
———-
0
################ Results: ##################
Run # 01 ran in 0 hsecs
Run # 02 ran in 0 hsecs
Run # 03 ran in 1 hsecs
############################################################################################
Statistics | IRS | FTS | IFFS
############################################################################################
session logical reads………………. | 0 | 1 | 230
consistent gets……………………. | 0 | 1 | 230
consistent gets from cache………….. | 0 | 1 | 230
consistent gets from cache (fastpath)… | 0 | 1 | 230
############################################################################################
[/sourcecode]

[collapse]

Example #2
DDL

[sourcecode language=”sql”]
SQL> alter session set optimizer_dynamic_sampling=0;
SQL> alter session set statistics_level=all;
SQL> create table xt_test as select 1 i from dual;

Table created.
[/sourcecode]

[collapse]
NULL 1

[sourcecode language=”sql”]
SQL> — NULL 1:
SQL> var a number;

SQL> select * from xt_test where i=:a;

no rows selected

SQL> select * from table(dbms_xplan.display_cursor(”,”,’ALLSTATS LAST’));

PLAN_TABLE_OUTPUT
————————————————————————————————-
SQL_ID 4rjbsjvwbq5m0, child number 0
————————————-
select * from xt_test where i=:a

Plan hash value: 3713359643

—————————————————————————————
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
—————————————————————————————
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 1 |
|* 1 | TABLE ACCESS FULL| XT_TEST | 1 | 3 | 0 |00:00:00.01 | 1 |
—————————————————————————————

Predicate Information (identified by operation id):
—————————————————
1 – filter("I"=:A)
[/sourcecode]

[collapse]
Not NULL 1

[sourcecode language=”sql”]
SQL> — NOT NULL 1:
SQL> exec :a := 0;

PL/SQL procedure successfully completed.

SQL> select * from xt_test where i=:a;

no rows selected

SQL> select * from table(dbms_xplan.display_cursor(”,”,’ALLSTATS LAST’));

PLAN_TABLE_OUTPUT
————————————————————————————————-
SQL_ID 4rjbsjvwbq5m0, child number 0
————————————-
select * from xt_test where i=:a

Plan hash value: 3713359643

————————————————————————————————
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
————————————————————————————————
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 3 | 1 |
|* 1 | TABLE ACCESS FULL| XT_TEST | 1 | 3 | 0 |00:00:00.01 | 3 | 1 |
————————————————————————————————

Predicate Information (identified by operation id):
—————————————————
1 – filter("I"=:A)
[/sourcecode]

[collapse]
Null 2

[sourcecode language=”sql”]
SQL> alter table xt_test add primary key(i);

Table altered.

SQL> exec :a := null;

PL/SQL procedure successfully completed.

SQL> — NULL 2:
SQL> select * from xt_test where i=:a;

no rows selected

SQL> select * from table(dbms_xplan.display_cursor(”,”,’ALLSTATS LAST’));

PLAN_TABLE_OUTPUT
————————————————————————————————-
SQL_ID 4rjbsjvwbq5m0, child number 0
————————————-
select * from xt_test where i=:a

Plan hash value: 136758570

———————————————————————————–
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
———————————————————————————–
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 |
|* 1 | INDEX UNIQUE SCAN| SYS_C00161305 | 1 | 1 | 0 |00:00:00.01 |
———————————————————————————–

Predicate Information (identified by operation id):
—————————————————
1 – access("I"=:A)
[/sourcecode]

[collapse]
Not NULL 2

[sourcecode language=”sql”]
SQL> exec :a := 0;

PL/SQL procedure successfully completed.

SQL> — NOT NULL 2:
SQL> select * from xt_test where i=:a;

no rows selected

SQL> select * from table(dbms_xplan.display_cursor(”,”,’ALLSTATS LAST’));

PLAN_TABLE_OUTPUT
————————————————————————————————-
SQL_ID 4rjbsjvwbq5m0, child number 0
————————————-
select * from xt_test where i=:a

Plan hash value: 136758570

———————————————————————————————
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
———————————————————————————————
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 1 |
|* 1 | INDEX UNIQUE SCAN| SYS_C00161305 | 1 | 1 | 0 |00:00:00.01 | 1 |
———————————————————————————————

Predicate Information (identified by operation id):
—————————————————
1 – access("I"=:A)
[/sourcecode]

[collapse]

Deterministic function vs scalar subquery caching. Part 2

Posted on by Posted in deterministic functions, oracle, query optimizing, scalar subquery caching 2,810 Page views 3 Comments

In previous part 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.

Today’s topic:
4. Deterministic functions does not keeps last result as 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.
Continue reading→