SQL & PL/SQL

For the scalability and performance needs of an OLTP application we may say; "Always use bind variables in programmed routines."

But I want to take your opinion on using bind varilables with skewed columns and histograms. After 9i there is a feature called bind variable peeking, but I can not find a way to demostrate how this feature works for our advantage?

Best regards.

Following tests were done on Windows Xp with Oracle XE;

sql*plus >

-- creating a demo table
connect hr/hr
drop table tbl_bind_peeking purge;
create table tbl_bind_peeking nologging as
select rownum id, owner, name, line from dba_source
order by owner, name, line;

create index nui_owner on tbl_bind_peeking(owner) nologging ;

-- statistics without histogram
exec dbms_stats.gather_table_stats(USER, 'tbl_bind_peeking', CASCADE=>TRUE);
commit;

-- first with OUTLN
conn hr/hr
set autotrace traceonly statistics
variable x varchar2(16);
alter session set max_dump_file_size=unlimited;
ALTER session SET timed_statistics = true;
alter session set STATISTICS_LEVEL = ALL ;
alter session set tracefile_identifier = TRACE_NOHIST_OUTLN_FIRST ;
Alter session set SQL_Trace = true ;
exec :x := 'OUTLN';
select * from tbl_bind_peeking where owner = :x;
Alter session set SQL_Trace = false ;

-- than with SYS
conn hr/hr
set autotrace traceonly statistics
variable x varchar2(16);
alter session set max_dump_file_size=unlimited;
ALTER session SET timed_statistics = true;
alter session set STATISTICS_LEVEL = ALL ;
alter session set tracefile_identifier = TRACE_NOHIST_SYS_SECOND ;
Alter session set SQL_Trace = true ;
exec :x := 'SYS';
select * from tbl_bind_peeking where owner = :x;
Alter session set SQL_Trace = false ;

-- flush shared pool and try the reverse case
conn / as sysdba
alter system flush shared_pool;

-- this time first with SYS
conn hr/hr
set autotrace traceonly statistics
variable x varchar2(16);
alter session set max_dump_file_size=unlimited;
ALTER session SET timed_statistics = true;
alter session set STATISTICS_LEVEL = ALL ;
alter session set tracefile_identifier = TRACE_NOHIST_SYS_FIRST ;
Alter session set SQL_Trace = true ;
exec :x := 'SYS';
select * from tbl_bind_peeking where owner = :x;
Alter session set SQL_Trace = false ;

-- than with OUTLN
conn hr/hr
set autotrace traceonly statistics
variable x varchar2(16);
alter session set max_dump_file_size=unlimited;
ALTER session SET timed_statistics = true;
alter session set STATISTICS_LEVEL = ALL ;
alter session set tracefile_identifier = TRACE_NOHIST_OUTLN_SECOND ;
Alter session set SQL_Trace = true ;
exec :x := 'OUTLN';
select * from tbl_bind_peeking where owner = :x;
Alter session set SQL_Trace = false ;

-- creating histogram for the skewed column
conn hr/hr
exec dbms_stats.gather_table_stats(user, 'tbl_bind_peeking', method_opt => 'for all indexed columns size 254', cascade => true );
commit;

-- flush shared pool before tests with histograms
conn / as sysdba
alter system flush shared_pool;

-- first with OUTLN
conn hr/hr
set autotrace traceonly statistics
variable x varchar2(16);
alter session set max_dump_file_size=unlimited;
ALTER session SET timed_statistics = true;
alter session set STATISTICS_LEVEL = ALL ;
alter session set tracefile_identifier = TRACE_WITHHIST_OUTLN_FIRST ;
Alter session set SQL_Trace = true ;
exec :x := 'OUTLN';
select * from tbl_bind_peeking where owner = :x;
Alter session set SQL_Trace = false ;

-- than with SYS
conn hr/hr
set autotrace traceonly statistics
variable x varchar2(16);
alter session set max_dump_file_size=unlimited;
ALTER session SET timed_statistics = true;
alter session set STATISTICS_LEVEL = ALL ;
alter session set tracefile_identifier = TRACE_WITHHIST_SYS_SECOND ;
Alter session set SQL_Trace = true ;
exec :x := 'SYS';
select * from tbl_bind_peeking where owner = :x;
Alter session set SQL_Trace = false ;

-- flush shared pool for the reverse test
conn / as sysdba
alter system flush shared_pool;

-- this time first with SYS
conn hr/hr
set autotrace traceonly statistics
variable x varchar2(16);
alter session set max_dump_file_size=unlimited;
ALTER session SET timed_statistics = true;
alter session set STATISTICS_LEVEL = ALL ;
alter session set tracefile_identifier = TRACE_WITHHIST_SYS_FIRST;
Alter session set SQL_Trace = true ;
exec :x := 'SYS';
select * from tbl_bind_peeking where owner = :x;
Alter session set SQL_Trace = false ;

-- than with OUTLN
conn hr/hr
set autotrace traceonly statistics
variable x varchar2(16);
alter session set max_dump_file_size=unlimited;
ALTER session SET timed_statistics = true;
alter session set STATISTICS_LEVEL = ALL ;
alter session set tracefile_identifier = TRACE_WITHHIST_OUTLN_SECOND;
Alter session set SQL_Trace = true ;
exec :x := 'OUTLN';
select * from tbl_bind_peeking where owner = :x;
Alter session set SQL_Trace = false ;

cmd >

tkprof oracos_ora_452_trace_nohist_outln_first.trc oracos_ora_452_trace_nohist_outln_first.txt explain=hr/hr sys=no waits=yes sort=execpu
tkprof oracos_ora_452_trace_nohist_sys_second.trc oracos_ora_452_trace_nohist_sys_second.txt explain=hr/hr sys=no waits=yes sort=execpu
tkprof oracos_ora_2044_trace_nohist_sys_first.trc oracos_ora_2044_trace_nohist_sys_first.txt explain=hr/hr sys=no waits=yes sort=execpu
tkprof oracos_ora_2044_trace_nohist_outln_second.trc oracos_ora_2044_trace_nohist_outln_second.txt explain=hr/hr sys=no waits=yes sort=execpu
tkprof oracos_ora_1648_trace_withhist_outln_first.trc oracos_ora_1648_trace_withhist_outln_first.txt explain=hr/hr sys=no waits=yes sort=execpu
tkprof oracos_ora_3820_trace_withhist_sys_second.trc oracos_ora_3820_trace_withhist_sys_second.txt explain=hr/hr sys=no waits=yes sort=execpu
tkprof oracos_ora_2276_trace_withhist_sys_first.trc oracos_ora_2276_trace_withhist_sys_first.t explain=hr/hr sys=no waits=yes sort=execpu
tkprof oracos_ora_3572_trace_withhist_outln_second.trc oracos_ora_3572_trace_withhist_outln_second.trct explain=hr/hr sys=no waits=yes sort=execpu

Results Summary >

-- OUTLN_FIRST_NOHIST
Rows Row Source Operation
------- ---------------------------------------------------
21 TABLE ACCESS BY INDEX ROWID tbl_bind_peeking (cr=8 pr=2 pw=0 time=26898 us)
21 INDEX RANGE SCAN NUI_OWNER (cr=5 pr=2 pw=0 time=26725 us)(object id 50493)

-- SYS_SECOND_NOHIST
Rows Row Source Operation
------- ---------------------------------------------------
91991 TABLE ACCESS BY INDEX ROWID tbl_bind_peeking (cr=12819 pr=192 pw=0 time=374432 us)
91991 INDEX RANGE SCAN NUI_OWNER (cr=6315 pr=192 pw=0 time=183997 us)(object id 50493)

-- SYS_FIRST_NOHIST
Rows Row Source Operation
------- ---------------------------------------------------
91991 TABLE ACCESS BY INDEX ROWID tbl_bind_peeking (cr=12819 pr=0 pw=0 time=282003 us)
91991 INDEX RANGE SCAN NUI_OWNER (cr=6315 pr=0 pw=0 time=92007 us)(object id 50493)

-- OUTLN_SECOND_NOHIST
Rows Row Source Operation
------- ---------------------------------------------------
21 TABLE ACCESS BY INDEX ROWID tbl_bind_peeking (cr=8 pr=0 pw=0 time=122 us)
21 INDEX RANGE SCAN NUI_OWNER (cr=5 pr=0 pw=0 time=65 us)(object id 50493)

-- OUTLN_FIRST_WITHHIST
Rows Row Source Operation
------- ---------------------------------------------------
21 TABLE ACCESS BY INDEX ROWID tbl_bind_peeking (cr=8 pr=0 pw=0 time=111 us)
21 INDEX RANGE SCAN NUI_OWNER (cr=5 pr=0 pw=0 time=52 us)(object id 50493)

-- SYS_SECOND_WITHHIST << how to get TABLE ACCESS FULL here?
Rows Row Source Operation
------- ---------------------------------------------------
91991 TABLE ACCESS BY INDEX ROWID tbl_bind_peeking (cr=12819 pr=0 pw=0 time=282166 us)
91991 INDEX RANGE SCAN NUI_OWNER (cr=6315 pr=0 pw=0 time=92014 us)(object id 50493)

-- SYS_FIRST_WITHHIST
Rows Row Source Operation
------- ---------------------------------------------------
91991 TABLE ACCESS FULL tbl_bind_peeking (cr=7453 pr=0 pw=0 time=110765 us)

-- OUTLN_SECOND_WITHHIST << or how to get INDEX RANGE SCAN here?
Rows Row Source Operation
------- ---------------------------------------------------
21 TABLE ACCESS FULL tbl_bind_peeking (cr=1343 pr=0 pw=0 time=19359 us)