ACS與PL/SQL

前面的章節論述了ACS的特性，我們來看下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

-------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------

| 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

-------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------

| 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

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 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

Adaptive Cursor Sharing（第四篇）

ACS與PL/SQL

相關文章