最佳化器革命之- adaptive cursor sharing (三)

dbhelper發表於2014-11-27
請先閱讀最佳化器革命之- adaptive cursor sharing (一)
http://blog.itpub.net/22034023/viewspace-1218980/

請先閱讀最佳化器革命之- adaptive cursor sharing (二)
http://blog.itpub.net/22034023/viewspace-1222110/

上面兩篇文章論述了ACS的特性,我們來看下ACS在PL/SQL裡的工作情況,結果可能會令你非常失望:
首先構造一個PL/SQL,裡面使用到了我們在文章二中建立的表T:

variable sql_id varchar2(255)
alter system flush shared_pool;


declare
  x integer;
  n number;
begin
  for i in 1..10 loop
    if i = 1 then
      x := 500000;
    else
      x := 1;
    end if;
select count(object_id) into n from t where id > x;
    if i = 1 then
      select
              prev_sql_id
      into
              :sql_id
      from
              v$session
      where
              sid = sys_context('userenv', 'sid');
    end if;
  end loop;
end;
/
這段PL/SQL其實首先會執行select count(object_id) into n from t where id > 100000 1次,然後會執行select count(object_id) into n from t where id >1 9次,執行完成後,我們來看看是否會使用到ACS。

select
        sql_id
      , child_number
      , executions
      , parse_calls
      , buffer_gets
      , is_bind_sensitive
      , is_bind_aware
from
        v$sql
where
        sql_id = :sql_id;


SQL_ID                     CHILD_NUMBER EXECUTIONS PARSE_CALLS BUFFER_GETS IS IS
-------------------------- ------------ ---------- ----------- ----------- -- --
gp03v5aw085v3                         0         10           1      646405 Y  N

非常可惜,這個SQL並沒有產生多個子遊標,雖然已經識別到這個SQL為繫結敏感is_bind_sensitive='Y',但是is_bind_aware='N'。

 SELECT   hash_value, sql_id, child_number, bucket_id, COUNT
         FROM v$sql_cs_histogram
       WHERE sql_id='gp03v5aw085v3'
     ORDER BY sql_id, child_number;


HASH_VALUE SQL_ID                     CHILD_NUMBER  BUCKET_ID      COUNT
---------- -------------------------- ------------ ---------- ----------
3087275875 gp03v5aw085v3                         1          0          1
3087275875 gp03v5aw085v3                         1          2          9
3087275875 gp03v5aw085v3                         1          1          0
雖然v$sql_cs_histogram已經監控到了處理行數的巨大改變,但是卻沒有生成新的遊標。


select * from table(dbms_xplan.display_cursor(:sql_id,null,'+PEEKED_BINDS'));


Plan hash value: 2966233522


---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       | 14358 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    10 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |  4900K|    46M| 14358   (2)| 00:02:53 |
---------------------------------------------------------------------------


Peeked Binds (identified by position):
--------------------------------------


   1 - :B1 (NUMBER): 5000000

執行計劃一直沿用的是第一次產生的執行計劃,根據Peeked Binds (identified by position):的值為5000000可以推斷出來。

我們嘗試在SQL裡增加bind_aware hint看看,這個hint的作用在文章一已經介紹過,這裡不再贅述。
declare
  x integer;
  n number;
begin
  for i in 1..10 loop
    if i = 1 then
      x := 100000;
    else
      x := 1;
    end if;
select /*+ bind_aware */ count(object_id) into n from t where id > x;
    if i = 1 then
      select
              prev_sql_id
      into
              :sql_id
      from
              v$session
      where
              sid = sys_context('userenv', 'sid');
    end if;
  end loop;
end;
/


select
        sql_id
      , child_number
      , executions
      , parse_calls
      , buffer_gets
      , is_bind_sensitive
      , is_bind_aware
from
        v$sql
where
        sql_id = :sql_id;


SQL_ID                     CHILD_NUMBER EXECUTIONS PARSE_CALLS BUFFER_GETS IS IS
-------------------------- ------------ ---------- ----------- ----------- -- --
b5n17d4tk7cw9                         0         10           1      646240 Y  Y
雖然最佳化器已經標記這個遊標 is_bind_aware='Y'了,但是依然沒有新的遊標產生出來。

在MOS上查詢類似的問題,發現了一個BUG:
Bug 8357294 : ADAPTIVE CURSOR SHARING DOESN'T WORK FOR STATIC SQL CURSORS FROM PL/SQL
標題的意思是由於BUG,ACS不能工作在PL/SQL的靜態遊標裡。但是根據我的測試動態遊標也不能工作。
declare
  x integer;
  n number;
begin
  for i in 1..10 loop
    if i = 1 then
      x := 100000;
    else
      x := 1;
    end if;
    execute immediate 
'select count(object_id)  from t where id > :x' into n using x;
    if i = 1 then
      select
              prev_sql_id
      into
              :sql_id
      from
              v$session
      where
              sid = sys_context('userenv', 'sid');
    end if;
  end loop;
end;
/


select
        sql_id
      , child_number
      , executions
      , parse_calls
      , buffer_gets
      , is_bind_sensitive
      , is_bind_aware
from
        v$sql
where
        sql_id = :sql_id;
        
SQL_ID                     CHILD_NUMBER EXECUTIONS PARSE_CALLS BUFFER_GETS IS IS
-------------------------- ------------ ---------- ----------- ----------- -- --
6qwg6gauwbpm8                         0         10           1      646240 Y  N


文中提到了Session_Cached_Cursors在設定為0後,ACS就可以正常工作了,經過試驗也如它所說。
alter session set Session_Cached_Cursors=0;
variable sql_id varchar2(255)
alter system flush shared_pool;


declare
  x integer;
  n number;
begin
  for i in 1..10 loop
    if i = 1 then
      x := 5000000;
    else
      x := 1;
    end if;
select count(object_id) into n from t where id > x;
    if i = 1 then
      select
              prev_sql_id
      into
              :sql_id
      from
              v$session
      where
              sid = sys_context('userenv', 'sid');
    end if;
  end loop;
end;
/


select
        sql_id
      , child_number
      , executions
      , parse_calls
      , buffer_gets
      , is_bind_sensitive
      , is_bind_aware
from
        v$sql
where
        sql_id = :sql_id;


SQL_ID                     CHILD_NUMBER EXECUTIONS PARSE_CALLS BUFFER_GETS IS IS
-------------------------- ------------ ---------- ----------- ----------- -- --
gp03v5aw085v3                         0          2           3       76344 Y  N
gp03v5aw085v3                         1          8           7      516992 Y  Y

可以看到ACS已經工作了,在v$sql_cs_histogram裡也為新遊標產生了新的行。
 SELECT   hash_value, sql_id, child_number, bucket_id, COUNT
    FROM v$sql_cs_histogram
  WHERE sql_id='gp03v5aw085v3'
ORDER BY sql_id, child_number;


HASH_VALUE SQL_ID                     CHILD_NUMBER  BUCKET_ID      COUNT
---------- -------------------------- ------------ ---------- ----------
3087275875 gp03v5aw085v3                         0          1          0
3087275875 gp03v5aw085v3                         0          0          1
3087275875 gp03v5aw085v3                         0          2          1
3087275875 gp03v5aw085v3                         1          1          0
3087275875 gp03v5aw085v3                         1          0          0
3087275875 gp03v5aw085v3                         1          2          8

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/8494287/viewspace-1349527/,如需轉載,請註明出處,否則將追究法律責任。

相關文章