cursor_sharing=similar深度剖析

     在metalink ID 1169017.1文档中，提到建议客户不设置cursor_sharing=

similar，因为存在许多性能问题，该参数将在12g中废除该参数，具体给出的理由

如下：

1、对于语句中包含的范围查询（如between,
计不适合使用；

2、影响11g Adaptive Cursor sharing特性和CBO优化器

3、Similar可能产生的一个父游标， 多个子游标，其性能比多个父游标情况更加糟

糕（EXACT或 FORCE);

基于以上描述，在10g/11g进行以下的测试：

1、修改参数
   alter session set cursor_sharing=similar;

2、创建测试表：
 create table t1 as
    select rownum  id,
           rpad('x',30) big_value
    from all_objects
    where rownum<=10;

3、统计分析
begin
    dbms_stats.gather_table_stats(
        user,
        't1',
        cascade=>true,
        estimate_percent=>null,
        method_opt=> 'for all columns size 1');
      --method_opt=> 'for columns id size 10');
  end;
  /

3、进行等式测试
   select /*+ mytest */ count(*) from t1 where id=2;
   select /*+ mytest */ count(*) from t1 where id=3;
   select /*+ mytest */ count(*) from t1 where id=4;
   select /*+ mytest */ count(*) from t1 where id=5;
   select /*+ mytest */ count(*) from t1 where id=6;
   select /*+ mytest */ count(*) from t1 where id=7;
   select /*+ mytest */ count(*) from t1 where id=8;
   select /*+ mytest */ count(*) from t1 where id=9;
   select /*+ mytest */ count(*) from t1 where id=10;

4、查看各子游标情况
 select count(hash_value) copies,
             substrb(SQL_TEXT,1 ,80) SQL_TEXT
   from v$sql
   where substrb(sql_text,1,80) like '%mytest%'
   group by substrb(sql_text,1,80)
   order by copies asc;
 
COPIES SQL_TEXT
---------- --------------------------------------------------------------------------------
        1 select /*+ mytest */ count(*) from t1 where id=:"SYS_B_0"
         1    select /*+ mytest */ count(*) from t1 where id=:"SYS_B_0"
 
5、查看标志位状态
 select        
        hash_value, IS_OBSOLETE,
        IS_BIND_SENSITIVE,
        IS_SHAREABLE,
        substrb(SQL_TEXT,1 ,80) SQL_TEXT
  from v$sql
  where substrb(sql_text,1,80) like '%mytest%';

输出结果正常：
IS_OBSOLETE = N
IS_BIND_SENSITIVE=N
IS_SHAREABLE=Y


6、对范围查询测试（如between, < 等）
select /*+ mytest1 */ count(*) from t1 where id <2;
select /*+ mytest1*/ count(*) from t1 where id <3;
select /*+ mytest1*/ count(*) from t1 where id <4;
select /*+ mytest1*/ count(*) from t1 where id <5;
select /*+ mytest1*/ count(*) from t1 where id <6;
select /*+ mytest1*/ count(*) from t1 where id <7;
select /*+ mytest1*/ count(*) from t1 where id <8;
select /*+ mytest1*/ count(*) from t1 where id <9;
select /*+ mytest1*/ count(*) from t1 where id <10;

7、执行步骤4和步骤5检测
  步骤4的输出结果：
      COPIES SQL_TEXT
---------- --------------------------------------------------------------------------------
         1 select /*+ mytest1 */ count(*) from t1 where id <:>         8 select /*+ mytest1*/ count(*) from t1 where id <:>
   步骤5输出结果：
     IS_OBSOLETE = N
     IS_BIND_SENSITIVE=Y
     IS_SHAREABLE=Y

8、对t1.id进行直方图统计分析，重新测步骤3脚本，并查看变化
   begin
    dbms_stats.gather_table_stats(
        user,
        't1',
        cascade=>true,
        estimate_percent=>null,
        --method_opt=> 'for all columns size 1');
        method_opt=> 'for columns id size 10');
  end;
  /

9、重新执行步骤3测试，输出结果：
select /*+ mytest */ count(*) from t1 where id=2;
.....................
.....................

  COPIES SQL_TEXT
---------- --------------------------------------------------------------------------------
         1 select /*+ mytest */ count(*) from t1 where id=:"SYS_B_0"
         8    select /*+ mytest */ count(*) from t1 where id=:"SYS_B_0"

    IS_OBSOLETE = N
     IS_BIND_SENSITIVE=Y
     IS_SHAREABLE=Y
其结果发生了很大的变化

结论：在范围查询和直方图统计中不适合设置similar，并且我们应该从现在开始忘记该参数similar设置的存在。

文档参看：metalink ID 1169017.1 “ANNOUNCEMENT: Deprecating the cursor_sharing='SIMILAR' setting
 

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

转载于:http://blog.itpub.net/354732/viewspace-690405/