请先阅读优化器革命之- 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博客

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

最新文章