共计 7032 个字符，预计需要花费 18 分钟才能阅读完成。
本篇内容主要讲解“ACS 与 PL/SQL 的工作情况分析”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让丸趣 TV 小编来带大家学习“ACS 与 PL/SQL 的工作情况分析”吧!
ACS 与 PL/SQL
我们来看下 ACS 在 PL/SQL 里的工作情况，结果可能会令你非常失望。
首先构造一个 PL/SQL，里面使用到了我们在本章中创建的表 T：
SQL var sql_id varchar2(255)
SQL alter system flush shared_pool;
System altered.
SQL declare
2 x integer;
3 n number;
4 begin
5 for i in 1..10 loop
6 if i = 1 then
7 x := 500000;
8 else
9 x := 1;
10 end if;
11 select count(object_id) into n from t where id x;
12 end loop;
13 end;
14 /
PL/SQL procedure successfully completed.
这段 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。
SQL select
2 sql_id
3 , child_number
4 , executions
5 , parse_calls
6 , buffer_gets
7 , is_bind_sensitive
8 , is_bind_aware
9 from
10 v$sql
11 where
12 sql_id = gp03v5aw085v3
SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS BUFFER_GETS IS IS
————— ———— ———- ———– ———– — —
gp03v5aw085v3 0 10 1 646875 Y N
非常可惜，这个 SQL 并没有产生多个子游标，虽然已经识别到这个 SQL 为绑定敏感 is_bind_sensitive= Y，但是 is_bind_aware= N。
SQL SELECT hash_value, sql_id, child_number, bucket_id, COUNT
2 FROM v$sql_cs_histogram
3 WHERE sql_id= gp03v5aw085v3
4 ORDER BY sql_id, child_number;
HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
———- ————— ———— ———- ———-
3087275875 gp03v5aw085v3 0 0 1
3087275875 gp03v5aw085v3 0 2 9
3087275875 gp03v5aw085v3 0 1 0
虽然 v$sql_cs_histogram 已经监控到了处理行数的巨大改变，但是却没有生成新的游标。
SQL select * from table(dbms_xplan.display_cursor(:sql_id,null, +PEEKED_BINDS
PLAN_TABLE_OUTPUT
—————————————————————————————-
SQL_ID gp03v5aw085v3, child number 0
————————————-
SELECT COUNT(OBJECT_ID) FROM T WHERE ID :B1
Plan hash value: 3694077449
————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————-
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 10 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I | 1 | | 3 (0)| 00:00:01 |
————————————————————————————-
Peeked Binds (identified by position):
————————————–
1 – :B1 (NUMBER): 5000000
Predicate Information (identified by operation id):
—————————————————
3 – access(ID :B1)
执行计划一直沿用的是第一次产生的执行计划，根据 Peeked Binds (identified by position): 的值为 5000000 可以推断出来。我们尝试在 SQL 里增加 bind_aware hint 看看，这个 hint 的作用在本章的最佳实践小节中介绍，这里不再赘述。
SQL declare
2 x integer;
3 n number;
4 begin
5 for i in 1..10 loop
6 if i = 1 then
7 x := 5000000;
8 else
9 x := 1;
10 end if;
11 select /*+ bind_aware */count(object_id) into n from t where id x;
12 end loop;
13 end;
14 /
PL/SQL procedure successfully completed.
SQL select
2 sql_id
3 , child_number
4 , executions
5 , parse_calls
6 , buffer_gets
7 , is_bind_sensitive
8 , is_bind_aware
9 from
10 v$sql
11 where
12 sql_id = 5542a2rzny69v
SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS BUFFER_GETS IS IS
————— ———— ———- ———– ———– — —
5542a2rzny69v 0 10 1 687396 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 的静态游标里。但是根据测试动态游标也不能工作。
SQL declare
2 x integer;
3 n number;
4 begin
5 for i in 1..10 loop
6 if i = 1 then
7 x := 5000000;
8 else
9 x := 1;
10 end if;
11 execute immediate
12 select count(object_id) from t where id :x into n using x;
13 end loop;
14 end;
15 /
PL/SQL procedure successfully completed.
SQL select
2 sql_id
3 , child_number
4 , executions
5 , parse_calls
6 , buffer_gets
7 , is_bind_sensitive
8 , is_bind_aware
9 from
10 v$sql
11 where
12 sql_id = 6qwg6gauwbpm8
SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS BUFFER_GETS IS IS
————— ———— ———- ———– ———– — —
6qwg6gauwbpm8 0 10 1 687580 Y N
文中提到了 Session_Cached_Cursors 在设置为 0 后，ACS 就可以正常工作了，经过试验也如它所说。
SQL alter session set Session_Cached_Cursors=0;
Session altered.
SQL alter system flush shared_pool;
System altered.
SQL declare
2 x integer;
3 n number;
4 begin
5 for i in 1..10 loop
6 if i = 1 then
7 x := 5000000;
8 else
9 x := 1;
10 end if;
11 select count(object_id) into n from t where id x;
12 end loop;
13 end;
14 /
PL/SQL procedure successfully completed.
SQL select
2 sql_id
3 , child_number
4 , executions
5 , parse_calls
6 , buffer_gets
7 , is_bind_sensitive
8 , is_bind_aware
9 from
10 v$sql
11 where
12 sql_id = gp03v5aw085v3
SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS BUFFER_GETS IS IS
————— ———— ———- ———– ———– — —
gp03v5aw085v3 0 2 3 76405 Y N
gp03v5aw085v3 1 8 7 517480 Y Y
SQL select * from table(dbms_xplan.display_cursor( gp03v5aw085v3 ,null));
PLAN_TABLE_OUTPUT
——————————————————————————————-
SQL_ID gp03v5aw085v3, child number 0
————————————-
SELECT COUNT(OBJECT_ID) FROM T WHERE ID :B1
Plan hash value: 3694077449
————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————-
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 10 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I | 1 | | 3 (0)| 00:00:01 |
————————————————————————————-
Predicate Information (identified by operation id):
—————————————————
3 – access(ID :B1)
SQL_ID gp03v5aw085v3, child number 1
————————————-
SELECT COUNT(OBJECT_ID) FROM T WHERE ID :B1
Plan hash value: 2966233522
—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | | | 14373 (100)| |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
|* 2 | TABLE ACCESS FULL| T | 4999K| 47M| 14373 (2)| 00:02:53 |
—————————————————————————
Predicate Information (identified by operation id):
—————————————————
2 – filter(ID :B1)
可以看到 ACS 已经工作了，在 v$sql_cs_histogram 里也为新游标产生了新的行。
SQL SELECT hash_value, sql_id, child_number, bucket_id, COUNT
2 FROM v$sql_cs_histogram
3 WHERE sql_id= gp03v5aw085v3
4 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
到此，相信大家对“ACS 与 PL/SQL 的工作情况分析”有了更深的了解，不妨来实际操作一番吧！这里是丸趣 TV 网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
