Oracle自適應共享遊標
自適應遊標共享Adaptive Cursor Sharing或擴充套件的遊標共享(Extended Cursor Sharing)是Oracle 11g的新特性之一，主要用於解決以前版
本中由於繫結變數窺探導致SQL語句無法獲得最佳執行計劃的缺陷，即能夠對效率低下的遊標(子游標)進行自動識別而選擇最佳的執行計劃。本
文詳細描述了自適應遊標共享並給出示例。
有關繫結變數窺探請參考：Oracle 繫結變數窺探
本中由於繫結變數窺探導致SQL語句無法獲得最佳執行計劃的缺陷，即能夠對效率低下的遊標(子游標)進行自動識別而選擇最佳的執行計劃。本
文詳細描述了自適應遊標共享並給出示例。
有關繫結變數窺探請參考：Oracle 繫結變數窺探
一、示例自適應遊標共享
1、建立演示環境
- SQL> select * from v$version where rownum<2;
- BANNER
- --------------------------------------------------------------------------------
- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
- SQL> create table t(id,owner,object_id) as
- 2 select rownum,owner,object_id from all_objects where rownum<=1000;
- SQL> alter table t add constraint t_pk primary key(id);
- SQL> exec dbms_stats.gather_table_stats('SCOTT','T',cascade=>true);
- SQL> select count(id),count(distinct id),min(id),max(id) from t;
- COUNT(ID) COUNT(DISTINCTID) MIN(ID) MAX(ID)
- ---------- ----------------- ---------- ----------
- 1000 1000 1 1000
2、使用繫結變數執行SQL語句並獲得首次執行情況
- SQL> var v_id number;
- SQL> exec :v_id:=9;
- SQL> set linesize 180
- SQL> select sum(object_id) from t where id<:v_id>
- SUM(OBJECT_ID)
- --------------
- 2078
- SQL> select * from table(dbms_xplan.display_cursor(null,null,'typical -predicate'));
- SQL_ID 7qcp6urqh7d2j, child number 0
- -------------------------------------
- select sum(object_id) from t where id<:v_id>-->變數值為9時，使用了正確的執行計劃，且預估的行數也準確
- Plan hash value: 4270555908
- -------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 3 (100)| |
- | 1 | SORT AGGREGATE | | 1 | 8 | | |
- | 2 | TABLE ACCESS BY INDEX ROWID| T | 8 | 64 | 3 (0)| 00:00:01 |
- | 3 | INDEX RANGE SCAN | T_PK | 8 | | 2 (0)| 00:00:01 |
- -------------------------------------------------------------------------------------
- SQL> col SQL_TEXT format a45 -->下面的語句獲得自適應遊標共享的3個欄位值
- SQL> select sql_id,sql_text,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable
- 2 from v$sql where sql_text like '%select sum(object_id) from t where%' and sql_text not like '%from v$sql%';
- SQL_ID SQL_TEXT CHILD_NUMBER EXECUTIONS I I I
- ------------- --------------------------------------------- ------------ ---------- - - -
- 7qcp6urqh7d2j select sum(object_id) from t where id<:v_id>
3、自適應遊標共享的外在體現
自適應遊標共享主要透過三個欄位來得以體現，即is_bind_sensitive,is_bind_aware,is_shareable。(注:此三個欄位僅在Oracle 11g
中存在)。透過上面從v$sql(v$sqlarea中不存在is_shareable)的查詢可知，三個欄位分別被賦予了不同的值，代表了不同的含義。
is_bind_sensitive(繫結是否敏感)
表示該子游標中是否使用了繫結變數要素，且採用bind peeking方法進行執行計劃生成。如果執行計劃依賴於窺探到的值，此處為Y，
否則為N。
is_bind_aware(繫結是否可知)
表示該子游標是否使用了extended cursor sharing技術，是則為Y，否則為N，如為N，則該遊標將廢棄，不再可用。
is_shareable(是否可共享)
表示該子游標可否被下次軟解析是否可共享使用。可共享則為Y，否則為N，表示該子游標失去了共享價值，按LRU演算法淘汰。
由於該SQL語句為首次執行，因此從v$sql查詢的結果中得知
is_bind_sensitive 為Y值(首次執行，執行了bind peeking)
is_bind_aware 為N值(首次執行，不被extended cursor sharing支援)
is_shareable 為Y值(執行計劃可共享)
4、重新賦值後觀察遊標共享情況
- SQL> exec :v_id:=900;
- SQL> select sum(object_id) from t where id<:v_id>
- SUM(OBJECT_ID)
- --------------
- 1826561
- SQL> select * from table(dbms_xplan.display_cursor(null,null,'typical -predicate'));
- SQL_ID 7qcp6urqh7d2j, child number 0
- -------------------------------------
- select sum(object_id) from t where id<:v_id>-->此次執行的變數值為900，執行計劃位上次變數為9的執行計劃
- -->此時為非正確的執行計劃，等同於Oracle 9i,10g中的情形
- Plan hash value: 4270555908
- -------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 3 (100)| |
- | 1 | SORT AGGREGATE | | 1 | 8 | | |
- | 2 | TABLE ACCESS BY INDEX ROWID| T | 8 | 64 | 3 (0)| 00:00:01 |
- | 3 | INDEX RANGE SCAN | T_PK | 8 | | 2 (0)| 00:00:01 |
- -------------------------------------------------------------------------------------
- -->自適應遊標共享的3個欄位值並未發生任何變化
- SQL> select sql_id,sql_text,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable
- 2 from v$sql where sql_text like '%select sum(object_id) from t where%' and sql_text not like '%from v$sql%';
- SQL_ID SQL_TEXT CHILD_NUMBER EXECUTIONS I I I
- ------------- --------------------------------------------- ------------ ---------- - - -
- 7qcp6urqh7d2j select sum(object_id) from t where id<:v_id>
- SQL> select sum(object_id) from t where id<:v_id>-->再次執行變數為900值的SQL語句
- SUM(OBJECT_ID)
- --------------
- 1826561
- SQL> select * from table(dbms_xplan.display_cursor(null,null,'typical -predicate'));
- SQL_ID 7qcp6urqh7d2j, child number 1
- -------------------------------------
- select sum(object_id) from t where id<:v_id>-->此時執行計劃較上一次發生了變化，使用了全表掃描,Rows接近於實際值
- -->自適應遊標共享特性得以體現
- Plan hash value: 2966233522
- ---------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ---------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 3 (100)| |
- | 1 | SORT AGGREGATE | | 1 | 8 | | |
- | 2 | TABLE ACCESS FULL| T | 900 | 7200 | 3 (0)| 00:00:01 |
- ---------------------------------------------------------------------------
- -->自適應遊標共享特性的幾個值發生了變化，生成了新的子游標，其子游標號為1
- SQL> select sql_id,sql_text,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable
- 2 from v$sql where sql_text like '%select sum(object_id) from t where%' and sql_text not like '%from v$sql%';
- SQL_ID SQL_TEXT CHILD_NUMBER EXECUTIONS I I I
- ------------- --------------------------------------------- ------------ ---------- - - -
- 7qcp6urqh7d2j select sum(object_id) from t where id<:v_id>
- 7qcp6urqh7d2j select sum(object_id) from t where id<:v_id>
- SQL> exec :v_id:=800 -->為變數賦於不同的值
- SQL> select sum(object_id) from t where id<:v_id>-->利用新的變數值執行SQL語句
- SUM(OBJECT_ID)
- --------------
- 1548431
- SQL> select sql_id,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable
- 2 from v$sql where sql_text like '%select sum(object_id) from t where%' and sql_text not like '%from v$sql%';
- SQL_ID CHILD_NUMBER EXECUTIONS I I I
- ------------- ------------ ---------- - - -
- 7qcp6urqh7d2j 0 2 Y N Y
- 7qcp6urqh7d2j 1 1 Y Y N
- 7qcp6urqh7d2j 2 1 Y Y Y -->生成了新的子游標號為2
- SQL> exec :v_id:=500; -->為變數賦於新值
- SQL> select sum(object_id) from t where id<:v_id>-->利用新的變數值執行SQL語句
- SUM(OBJECT_ID)
- --------------
- 826694
- /**************************************************/
- /* Author: Robinson Cheng */
- /* Blog: http://blog.csdn.net/robinson_0612 */
- /* MSN: robinson_0612@hotmail.com */
- /* QQ: 645746311 */
- /**************************************************/
- SQL> select sql_id,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable
- 2 from v$sql where sql_text like '%select sum(object_id) from t where%' and sql_text not like '%from v$sql%';
- SQL_ID CHILD_NUMBER EXECUTIONS I I I
- ------------- ------------ ---------- - - -
- 7qcp6urqh7d2j 0 2 Y N Y
- 7qcp6urqh7d2j 1 1 Y Y N
- 7qcp6urqh7d2j 2 1 Y Y N -->注意看子游標1,2的is_shareable值為N，表示不可共享
- 7qcp6urqh7d2j 3 1 Y Y Y -->生成了新的子游標號為3，
- -->檢視最終該SQL語句的不同子游標的所有執行計劃
- SQL> select * from table(dbms_xplan.display_cursor('7qcp6urqh7d2j',null,'typical -predicate'));
- PLAN_TABLE_OUTPUT
- ------------------------------------------------------------------------------------------------------------------
- SQL_ID 7qcp6urqh7d2j, child number 0
- -------------------------------------
- select sum(object_id) from t where id<:v_id>-->0號子游標為索引範圍掃描
- Plan hash value: 4270555908
- -------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 3 (100)| |
- | 1 | SORT AGGREGATE | | 1 | 8 | | |
- | 2 | TABLE ACCESS BY INDEX ROWID| T | 8 | 64 | 3 (0)| 00:00:01 |
- | 3 | INDEX RANGE SCAN | T_PK | 8 | | 2 (0)| 00:00:01 |
- -------------------------------------------------------------------------------------
- SQL_ID 7qcp6urqh7d2j, child number 1
- -------------------------------------
- select sum(object_id) from t where id<:v_id>-->1號子游標為全表掃描，其預估的行數接近實際影響行數的值為900
- Plan hash value: 2966233522
- ---------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ---------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 3 (100)| |
- | 1 | SORT AGGREGATE | | 1 | 8 | | |
- | 2 | TABLE ACCESS FULL| T | 900 | 7200 | 3 (0)| 00:00:01 |
- ---------------------------------------------------------------------------
- SQL_ID 7qcp6urqh7d2j, child number 2
- -------------------------------------
- select sum(object_id) from t where id<:v_id>-->2號子游標為全表掃描，但其預估的行數接近實際影響行數的值為800
- Plan hash value: 2966233522
- ---------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ---------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 3 (100)| |
- | 1 | SORT AGGREGATE | | 1 | 8 | | |
- | 2 | TABLE ACCESS FULL| T | 800 | 6400 | 3 (0)| 00:00:01 |
- ---------------------------------------------------------------------------
- SQL_ID 7qcp6urqh7d2j, child number 3
- -------------------------------------
- select sum(object_id) from t where id<:v_id>-->3號子游標為全表掃描，但其預估的行數等於實際影響行數的值499
- Plan hash value: 2966233522
- ---------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ---------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 3 (100)| |
- | 1 | SORT AGGREGATE | | 1 | 8 | | |
- | 2 | TABLE ACCESS FULL| T | 499 | 3992 | 3 (0)| 00:00:01 |
- ---------------------------------------------------------------------------
二、自適應遊標共享的幾個相關檢視
1、v$sql_cs_statistics
用於監控自適應遊標共享的相關統計資訊.下面的查詢中列出了每個子游標的peeking情況,以及執行次數,預處理行數,BUFFER_GETS等
- SQL> select child_number,bind_set_hash_value,peeked,executions,rows_processed,buffer_gets
- 2 from v$sql_cs_statistics where sql_id='7qcp6urqh7d2j'
- 3 order by 1;
- CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS
- ------------ ------------------- - ---------- -------------- -----------
- 0 1706589901 Y 1 17 69 -->17行，索引範圍掃描
- 1 3116944019 Y 1 900 5 -->900行，全表掃描
- 2 1328865654 Y 1 800 5 -->800行，全表掃描
- 3 1624350242 Y 1 500 5 -->500行，全表掃描
2、v$sql_cs_selectivity
顯示每個子游標的遊標的選擇性範圍。下面的查詢中列出了謂詞，選擇性範圍，列上的選擇性的值
- SQL> select child_number,predicate,range_id,low,high from v$sql_cs_selectivity
- 2 where sql_id='7qcp6urqh7d2j' order by 1;
- CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH
- ------------ ------------------ ---------- ---------- ----------
-
1
-
2
-
3
3、v$sql_cs_histogram
用於決定一個查詢是否允許自適應遊標共享，以直方圖形式儲存
- SQL> select CHILD_NUMBER,BUCKET_ID,COUNT from v$sql_cs_histogram where SQL_ID='7qcp6urqh7d2j'
- 2 order by 1;
- CHILD_NUMBER BUCKET_ID COUNT
- ------------ ---------- ----------
- 0 1 1
- 0 0 1
- 0 2 0
- 1 1 0
- 1 0 1
- 1 2 0
- 2 1 0
- 2 0 1
- 2 2 0
- 3 1 0
- 3 0 1
- 3 2 0
三、總結
1、自適應遊標共享在SQL語句首次執行時(使用繫結變數)，進行窺探，並記錄窺探結果，如果後續有相同的的SQL語句執行，則對窺探結果
進行比較以判斷是否需要生成新的執行計劃。此即為繫結變數是否敏感。
2、繫結變數的可知性用於判斷當前的遊標是否為可擴充套件性遊標共享，當不可知時，則遊標被廢棄。
3、自適應遊標共享的實質是在Oracle 10g以前的基礎上實現了多次繫結變數窺探，增加了獲取最佳執行計劃選擇的機率。
4、儘管使用自適應遊標共享特性，但並不能保證每次執行SQL語句一定按最佳計劃執行，本次演示中SQL語句的第二次執行並非最佳執行計劃。
5、自適應遊標共享也不能保證兩次執行相同SQL語句一定按相同的執行計劃執行，因為自適應遊標共享會不斷嘗試peeking。
四、延伸參考
Oracle 繫結變數窺探
Oracle自適應共享遊標
繫結變數及其優缺點
父遊標、子游標及共享遊標
dbms_xplan之display_cursor函式的使用
dbms_xplan之display函式的使用
執行計劃中各欄位各模組描述
-->>轉載於:http://blog.csdn.net/leshami/article/details/6923670
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29119536/viewspace-1243966/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 11.1 自適應遊標Oracle
- Oracle - 共享遊標、父子游標、硬軟解析Oracle
- Oracle遊標Oracle
- 自適應案例
- 程式碼生成器，自適應mysql、oracle資料庫MySqlOracle資料庫
- textarea 高度自適應
- 圖片自適應
- JFrame自適應大小
- 自適應辛普森法
- vue前端自適應佈局，一步到位所有自適應Vue前端
- css自適應佈局CSS
- web自適應尺寸方法Web
- 自適應介面設計
- Android 螢幕自適應Android
- html iframe高度自適應HTML
- LMS自適應濾波
- Oracle顯示遊標的使用及遊標for迴圈Oracle
- 青蛙吃蚊子小遊戲原始碼_自適應手機端遊戲原始碼
- textarea文域高度自適應
- textarea高度自適應詳解
- 元素自適應水平垂直居中
- 小程式Swiper高度自適應
- jQuery textarea框高度自適應jQuery
- Android 8.0 自適應圖示Android
- c# 窗體自適應C#
- 思路：element 表格元件自適應元件
- gridview自動適應列寬View
- iframe 跨域高度自適應跨域
- 自適應CFAR檢測器
- 前端:WebP自適應實踐前端Web
- Oracle開發基礎-遊標Oracle
- 自動載入的iframe高度自適應
- 響應式圖片(自適應圖片)
- QTableWidget行高列寬自適應QT
- input文字框寬度自適應
- CSS佈局 --- 自適應佈局CSS
- web 報表工具如何自適應Web
- Widget小元件如何自適應高度元件
- WPF控制元件介面自適應控制元件