您好,登錄后才能下訂單哦!
本篇內(nèi)容主要講解“ACS與PL/SQL的工作情況分析”,感興趣的朋友不妨來(lái)看看。本文介紹的方法操作簡(jiǎn)單快捷,實(shí)用性強(qiáng)。下面就讓小編來(lái)帶大家學(xué)習(xí)“ACS與PL/SQL的工作情況分析”吧!
我們來(lái)看下ACS在PL/SQL里的工作情況,結(jié)果可能會(huì)令你非常失望。
首先構(gòu)造一個(gè)PL/SQL,里面使用到了我們?cè)诒菊轮袆?chuàng)建的表T:
SQL>var sql_id varchar2(255) SQL>alter system flush shared_pool; System altered. 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首先會(huì)執(zhí)行‘select count(object_id) into n from t where id > 100000 ’1次,然后會(huì)執(zhí)行‘select count(object_id) into n from t where id >1’ 9次,執(zhí)行完成后,我們來(lái)看看是否會(huì)使用到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 |
非常可惜,這個(gè)SQL并沒(méi)有產(chǎn)生多個(gè)子游標(biāo),雖然已經(jīng)識(shí)別到這個(gè)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已經(jīng)監(jiān)控到了處理行數(shù)的巨大改變,但是卻沒(méi)有生成新的游標(biāo)。
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) |
執(zhí)行計(jì)劃一直沿用的是第一次產(chǎn)生的執(zhí)行計(jì)劃,根據(jù)Peeked Binds (identified by position):的值為5000000可以推斷出來(lái)。我們嘗試在SQL里增加bind_aware hint看看,這個(gè)hint的作用在本章的最佳實(shí)踐小節(jié)中介紹,這里不再贅述。
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 |
雖然優(yōu)化器已經(jīng)標(biāo)記這個(gè)游標(biāo)is_bind_aware='Y'了,但是依然沒(méi)有新的游標(biāo)產(chǎn)生出來(lái)。在MOS上查找類似的問(wèn)題,會(huì)發(fā)現(xiàn)一個(gè)BUG:
Bug 8357294 : ADAPTIVE cursor SHARING DOESN'T WORK FOR STATIC SQL cursorS FROM PL/SQL
標(biāo)題的意思是由于BUG,ACS不能工作在PL/SQL的靜態(tài)游標(biāo)里。但是根據(jù)測(cè)試動(dòng)態(tài)游標(biāo)也不能工作。
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在設(shè)置為0后,ACS就可以正常工作了,經(jīng)過(guò)試驗(yàn)也如它所說(shuō)。
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已經(jīng)工作了,在v$sql_cs_histogram里也為新游標(biāo)產(chǎ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 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 |
到此,相信大家對(duì)“ACS與PL/SQL的工作情況分析”有了更深的了解,不妨來(lái)實(shí)際操作一番吧!這里是億速云網(wǎng)站,更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢,關(guān)注我們,繼續(xù)學(xué)習(xí)!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。