優化器革命之- adaptive cursor sharing （三）

請先閱讀優化器革命之- 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/22034023/viewspace-1222146/，如需轉載，請註明出處，否則將追究法律責任。

優化器革命之- adaptive cursor sharing （三）

相關文章