cursor_sharing设置为similar 的弊端

将cursor_sharing设置为similar会产生许多问题：
1、对于语句中包含的范围查询（如between,
2、影响11g Adaptive Cursor sharing特性和CBO优化器
3、Similar可能产生的一个父游标， 多个子游标，其性能比多个父游标情况更加糟
糕（EXACT或 FORCE);
对于第一个，我们做实验如下：
1 修改参数，建表，统计信息
yang@rac1>alter session set cursor_sharing=similar;
Session altered.
yang@rac1>create table t_cur_sh as
  2  select rownum id ,
  3  dbms_random.string('s',12) val
  4  from dual
  5  connect by level <1e3;
Table created.
yang@rac1>begin
  2  dbms_stats.gather_table_stats(
  3   user,
  4  'T_CUR_SH',
  5  cascade=>true,
  6  estimate_percent=>null,
  7  method_opt=>'for all columns size 1');
  8  end;
  9  /
PL/SQL procedure successfully completed.
2 进行等值测试
yang@rac1>@/tmp/t.sql
select /*+yang */ count(1) from t_cur_sh where id=1;
select /*+yang */ count(1) from t_cur_sh where id=2;
select /*+yang */ count(1) from t_cur_sh where id=3;
select /*+yang */ count(1) from t_cur_sh where id=4;
select /*+yang */ count(1) from t_cur_sh where id=5;
select /*+yang */ count(1) from t_cur_sh where id=6;
select /*+yang */ count(1) from t_cur_sh where id=7;
select /*+yang */ count(1) from t_cur_sh where id=8;
select /*+yang */ count(1) from t_cur_sh where id=9;
select /*+yang */ count(1) from t_cur_sh where id=10;
--查看子游标的信息：现在是2个子游标
select count(hash_value) copies,
 substrb(sql_text,1,80) sql_text
from v$sql
where substrb(sql_text,1,80) like '%yang%'
group by substrb(sql_text,1,80)
order by copies asc
/
    COPIES SQL_TEXT
---------- --------------------------------------------------------------------------------
         2 select /*+yang */ count(:"SYS_B_0") from t_cur_sh where id=:"SYS_B_1"

yang@rac1>select        
  2          hash_value, IS_OBSOLETE,
  3          IS_BIND_SENSITIVE,
  4          IS_SHAREABLE,
  5          substrb(SQL_TEXT,1 ,80) SQL_TEXT
  6    from v$sql
  7    where substrb(sql_text,1,80) like '%yang%';
HASH_VALUE I I I SQL_TEXT
---------- - - ------------------------------------------------------------
3475970911 N N Yselect /*+yang */ count(:"SYS_B_0") from t_cur_sh where id=:"SYS_B_1"
3475970911 N N Yselect /*+yang */ count(:"SYS_B_0") from t_cur_sh where id=:"SYS_B_1"
上面三个 I 分别是：
是否绑定敏感（is_bind_sensitive）：不仅指出是否使用绑定变量窥测来生成执行计划，而且指出这个执行计划是否依赖于窥测到的值。如果是，这个字段会被设置为Y，否则会被设置为N。
是否绑定可知（is_bind_aware）：表明游标是否使用了扩展的游标共享。如果是，这个字段会被设置为Y，如果不是，这个字段会被设置为N。如果是设置为N，这个游标将被废弃，不再可用。
是否可共享（is_shareable）：表明游标能否被共享。如果可以，这个字段会被设置为Y，否则，会被设置为N。如果被设置为N，这个游标将被废弃，不再可用
这里
IS_OBSOLETE = N
IS_BIND_SENSITIVE=N
IS_SHAREABLE=Y  --游标是可以共享的。
再看一个例子：不等值的情况下使用 similar的情况：
yang@rac1>@/tmp/t2.sql
t2.sql
select /*+yang */ count(1) from t_cur_sh where id<2;
select /*+yang */ count(1) from t_cur_sh where id<3;
select /*+yang */ count(1) from t_cur_sh where id<4;
select /*+yang */ count(1) from t_cur_sh where id<5;
select /*+yang */ count(1) from t_cur_sh where id<6;
select /*+yang */ count(1) from t_cur_sh where id<7;
select /*+yang */ count(1) from t_cur_sh where id<8;
select /*+yang */ count(1) from t_cur_sh where id<9;
select /*+yang */ count(1) from t_cur_sh where id<10;

select count(hash_value) copies,
 substrb(sql_text,1,80) sql_text
from v$sql
where substrb(sql_text,1,80) like '%yang%'
group by substrb(sql_text,1,80)
order by copies asc
/
COPIES SQL_TEXT
---------- -------------------------------------------------------------------------------
2 select /*+yang */ count(:"SYS_B_0") from t_cur_sh where id=:"SYS_B_1"
9 select /*+yang */ count(:"SYS_B_0") from t_cur_sh where id<: style="color: rgb(255, 1, 2);">9个子游标
没有充分共享，所以在范围查询和直方图统计中不适合设置similar，
yang@rac1>select        
  2          hash_value, IS_OBSOLETE,
  3          IS_BIND_SENSITIVE,
  4          IS_SHAREABLE,
  5          substrb(SQL_TEXT,1 ,80) SQL_TEXT
  6    from v$sql
  7    where substrb(sql_text,1,80) like '%yang%';

HASH_VALUE I I I  SQL_TEXT
---------- - - -------------------------------------------------------------------
3475970911 N N Y select /*+yang */ count(:"SYS_B_0") from t_cur_sh where id=:"SYS_B_1"
3475970911 N N Y select /*+yang */ count(:"SYS_B_0") from t_cur_sh where id=:"SYS_B_1"
3690020262 N Y Y select /*+yang */ count(:"SYS_B_0") from t_cur_sh where id<:>3690020262 N Y Y select /*+yang */ count(:"SYS_B_0") from t_cur_sh where id<:>3690020262 N Y Y select /*+yang */ count(:"SYS_B_0") from t_cur_sh where id<:>3690020262 N Y Y select /*+yang */ count(:"SYS_B_0") from t_cur_sh where id<:>3690020262 N Y Y select /*+yang */ count(:"SYS_B_0") from t_cur_sh where id<:>3690020262 N Y Y select /*+yang */ count(:"SYS_B_0") from t_cur_sh where id<:>3690020262 N Y Y select /*+yang */ count(:"SYS_B_0") from t_cur_sh where id<:>3690020262 N Y Y select /*+yang */ count(:"SYS_B_0") from t_cur_sh where id<:>3690020262 N Y Y select /*+yang */ count(:"SYS_B_0") from t_cur_sh where id<:>11 rows selected.
IS_OBSOLETE = N
IS_BIND_SENSITIVE=N --不敏感，如果是设置为N，这个游标将被废弃，不再可用。
IS_SHAREABLE=Y  

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22664653/viewspace-690985/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22664653/viewspace-690985/