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/，如需轉載，請註明出處，否則將追究法律責任。