OPTIMIZER ADAPTIVE CURSOR SHARING - 日本エクセム株式会社 Oracleパラメータ

Syntax：_OPTIMIZER_ADAPTIVE_CURSOR_SHARING
設定方法：・Parameter File
　　　　　・ALTER SYSTEM SET “_OPTIMIZER_ADAPTIVE_CURSOR_SHARING” = TRUE
　　　　　・ALTER SESSION SET “_OPTIMIZER_ADAPTIVE_CURSOR_SHARING” = TRUE

バージョンとデフォルト
9iR2 : -
10gR1: -
10gR2: -	
11gR1: TRUE

 -  create objects
drop table acs_table;
create table acs_table（id int、name char（10））;
create index acs_table_idx on acs_table（id）;

 -  generate skewed data
insert into acs_table select 1、 'name' from all_objects where rownum <= 100000
insert into acs_table values（99、 'name'）;

commit;
 
 -  gather statistics with histogram
exec dbms_stats.gather_table_stats（user、「ACS_TABLE '、estimate_percent 
　　　　=> 100、method_opt =>' FOR COLUMNS SIZE 2 ID」、cascade => true）;

 -  check histogram
select * from dba_tab_histograms where table_name = 'ACS_TABLE';

 -  Bind query
alter system flush shared_pool;

var id number;

 -  id == 1
 - 各ステップごとに次のクエリの結果を確認
select sql_id、sql_text、is_bind_sensitive、is_bind_aware
from v $ sql where sql_text like」select count（name）from acs_table％ ';

exec：id：= 1;

select count（name）from acs_table where id =：id;

 -  id == 99
exec：id：= 99;

select count（name）from acs_table where id =：id;
select count（name）from acs_table where id =：id;

 -  id == 1 again
exec：id：= 1;

select count（name）from acs_table where id =：id;

 -  check mismatch
select * from v $ sql_shared_cursor where sql_id = '';