最佳化器革命之- 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
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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 最佳化器革命之- adaptive cursor sharing (四)APT
- 優化器革命之- adaptive cursor sharing (三)優化APT
- 優化器革命之- adaptive cursor sharing (二)優化APT
- Adaptive Cursor SharingAPT
- Adaptive Cursor Sharing(第三篇)APT
- oracle11g中SQL最佳化新特性之Adaptive Cursor Sharing (ACS)OracleSQLAPT
- Adaptive Cursor Sharing(第二篇)APT
- Adaptive Cursor Sharing(第四篇)APT
- Adaptive Cursor Sharing(第五篇)APT
- Adaptive Cursor Sharing (第一篇)APT
- Adaptive Cursor Sharing: Worked Example (Doc ID 836256.1)APT
- 11gR2 新特性之(一)Adaptive Cursor Sharing(ACS)APT
- 11G Adaptive Cursor Sharing(ACS)的研究APT
- 11GR1 新特性 adaptive cursor sharingAPT
- 11G Adaptive Cursor Sharing(ACS)的研究 (轉)APT
- 驗證11g的adaptive cursor sharing功能APT
- 繫結變數之自適應遊標共享(Adaptive Cursor Sharing)變數APT
- Oracle自適應共享遊標——Adaptive Cursor Sharing(上)OracleAPT
- Oracle自適應共享遊標——Adaptive Cursor Sharing(下)OracleAPT
- [20120327]Adaptive Cursor Sharing 的問題APT
- 揭秘自適應遊標共享技術(Adaptive Cursor Sharing)APT
- Oracle 11g SQL效能的新特性(一)- Adaptive Cursor SharingOracleSQLAPT
- 揭祕自適應遊標共享技術(Adaptive Cursor Sharing)APT
- [20221227]Adaptive Cursor Sharing & 直方圖.txtAPT直方圖
- 11G Adaptive Cursor Sharing(ACS)自適應遊標測試APT
- 繫結變數的正確使用,bind peeking,Adaptive Cursor Sharing變數APT
- 【DBA】Oracle 11g 針對SQL效能的新特性(一)- Adaptive Cursor SharingOracleSQLAPT
- V$SQL_遊標、adaptive cursor sharing、bind peeking、直方圖的概念SQLAPT直方圖
- [20120220]Adaptive Cursor Sharing 與hints.txtAPT
- CURSOR_SHARING=SIMILARMILA
- Postgresql的CURSOR SHARINGSQL
- Cursor_sharing,Histogram,Analyze之間的關係Histogram
- oracle cursor_sharing [轉]Oracle
- Cursor_sharing,Histogram,Analyze之間的關係(轉)Histogram
- 關於 cursor_sharing = similarMILA
- oracle引數-cursor_sharingOracle
- cursor_sharing=similar深度剖析MILA
- cursor_sharing : exact , force , similarMILA