oracle 11g 新特性之動態繫結變數窺視(二)
2. 11g之後的動態繫結變數窺視
而從11g開始,這個尷尬的問題開始得到了改善。因此從11g開始,引入了所謂的自適應遊標共享(Adaptive Cursor Sharing)。該特性是一個非常複雜的技術,用來平衡遊標共享和SQL優化這兩個矛盾的目標。11g裡不會盲目的共享遊標,而是會去檢視每個繫結變數,併為不同的繫結變數來產生不同的執行計劃。而oracle這麼做的前提是,使用多個執行計劃的所帶來的收益,要比產生多個執行計劃所引起的CPU開銷要更大。
使用自適應遊標共享時,會遵循下面的步驟:
1) 一條新的SQL語句第一次傳入shared pool時,還是和以前一樣,進行硬解析。而且進行繫結變數窺視,計算where條件各個列的selectivity,同時如果繫結變數所在的列上存在直方圖的話,也會去參考該直方圖來計算selectivity。該遊標會被標記為是一個繫結敏感的遊標(bind-sensitive cursor)。同時,oracle還會保留包含繫結變數的where條件的其他資訊,比如selectivity等。Oracle會為該謂詞的selectivity維持一個範圍,oracle叫做立方體(cube)。只要傳入的繫結變數所產生的selectivity落在該範圍裡面,也就是落在該cube裡面,就不產生新的執行計劃,而直接拿該cube所對應的執行計劃來用。
2) 下次再次執行相同的SQL時,傳入了新的繫結變數,假設使用新的繫結變數的謂詞的selectivity落在已經存在的cube範圍裡,於是這次SQL的執行會使用該cube所對應的執行計劃。
3) 相同的查詢再次執行時,假設所使用的新的繫結變數導致這時候的selectivity不再落在已經存在的cube裡了,於是也就找不到對應的執行計劃。於是系統會進行一個硬解析,這將產生第二個新的執行計劃。而且新的selectivity以及對應的cube也會儲存下來。也就是說,這時,我們分別有兩個cube以及兩個執行計劃。
4) 相同的查詢再次執行時,假設所使用的新的繫結變數導致這時候的selectivity不落在現存的兩個cube中的任何一個,所以系統又會進行硬解析。假設這時硬解析所產生的執行計劃與第一次產生執行計劃一樣,也就是說,在第一次評估selectivity的cube時過於保守,導致cube過小,進而導致了這一次的不必要的硬解析。於是,oracle會將第一次產生的cube與這次產生的cube合併成一個新的更大的cube。那麼,下次再次進行軟解析的時候,如果selectivity落在新的cube裡,則會使用第一次所產生的執行計劃。
我們從這裡可以看到,11g對這個問題的處理非常精彩。這樣做的結果是,系統開始執行時,CPU消耗可能會比較嚴重,但是隨著系統不斷執行,cube的不斷合併從而不斷擴大,於是系統的CPU消耗會不斷下降,同時執行計劃也會更加的合理。
我們來做個試驗進行驗證。我們採用11g新引入的執行計劃管理特性來驗證該特性。
與10g中的測試一樣,建立一個資料分佈不均勻的表,在資料分佈不均勻的列上建立索引,並收集統計資訊,收集時注意要收集直方圖,從而讓CBO知道該列上的資料分佈不均勻。
hr@ora11g > create table t1 as select object_id as id,object_name from dba_objects;
hr@ora11g > select count(*) from t1;
COUNT(*)
----------
12064
hr@ora11g > update t1 set id=1 where rownum<=10000;
hr@ora11g > commit;
hr@ora11g > create index idx_t1 on t1(id);
hr@ora11g > begin
2 dbms_stats.gather_table_stats(
3 user,
4 't1',
5 cascade => true,
6 method_opt => 'for columns id size 254'
7 );
8 end;
9 /
我們找到表t1裡最大的id,然後以該id作為第一個繫結變數傳入,可以想象,該繫結變數將導致走索引。
hr@ora11g > select max(id) from t1;
MAX(ID)
----------
12462
我們將optimizer_capture_plan_baselines設定為true,從而讓oracle自動獲取plan baseline。
hr@ora11g > alter system set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true;
hr@ora11g > alter system flush shared_pool;
hr@ora11g > var v_id number;
hr@ora11g > exec :v_id := 12462;
hr@ora11g > select * from t1 where id=:v_id;
hr@ora11g > select * from t1 where id=:v_id;
我們執行兩遍select * from t1 where id=:v_id,從而讓oracle捕獲plan baseline。我們知道id為12462的記錄只有一條,因此該SQL應該使用索引掃描。然後我們再為繫結變數傳入1,我們知道id為1的記錄有一萬條,所以較好的執行計劃不應該走已經生成的執行計劃,而應該走全表掃描。
hr@ora11g > exec :v_id := 1;
hr@ora11g > set autotrace traceonly stat;
--之所以設定stat是為了讓該sql實際執行,但不要返回所有記錄,
hr@ora11g > select * from t1 where id=:v_id;
hr@ora11g > select sql_handle,plan_name,origin,enabled,accepted
2 from dba_sql_plan_baselines where sql_text like 'select * from t1%';
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC
----------------------- ----------------------------- -------------- --- ---
SYS_SQL_ea05bbed6f2f670c SYS_SQL_PLAN_6f2f670c844cb98a AUTO-CAPTURE YES YES
SYS_SQL_ea05bbed6f2f670c SYS_SQL_PLAN_6f2f670cdbd90e8e AUTO-CAPTURE YES NO
我們可以發現,現在該SQL語句存在兩個執行計劃了,其中第一個執行計劃,也就是accepted為YES的執行計劃為v_id等於12462得到的,而第二個執行計劃,也就是accepted為NO的是由v_id等於1得到的。第二個執行計劃還沒有被加入plan baseline,所以優化器不會使用該執行計劃。我們將第二個執行計劃的accepted改為YES,從而讓oracle考慮使用該計劃。
hr@ora11g > var cnt number;
hr@ora11g > begin
2 :cnt := dbms_spm.alter_sql_plan_baseline(
3 sql_handle => 'SYS_SQL_ea05bbed6f2f670c',
4 plan_name => 'SYS_SQL_PLAN_6f2f670cdbd90e8e',
5 attribute_name => 'ACCEPTED', attribute_value => 'YES');
6 end;
7 /
我們來看一下這兩個執行計劃分別是怎樣的。
注意:在這裡我們要驗證oracle會為不同繫結變數生成不同的執行計劃時,不能使用set autotrace traceonly exp stat等其他方式。因為set autotrace得出的執行計劃始終都是第一次生成的執行計劃。我們通過plan baseline從側面來驗證它。當然,我們也可以通過設定sql_trace=true從而將執行計劃轉儲出來進行驗證。
SQL> select * from table(dbms_xplan.display_sql_plan_baseline
2 ('SYS_SQL_ea05bbed6f2f670c','SYS_SQL_PLAN_6f2f670c844cb98a'));
......
--------------------------------------------------------------------------------
Plan hash value: 50753647
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 126 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 6 | 126 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T1 | 6 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
......
SQL> select * from table(dbms_xplan.display_sql_plan_baseline
2 ('SYS_SQL_ea05bbed6f2f670c','SYS_SQL_PLAN_6f2f670cdbd90e8e'));
......
--------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 126 | 16 (0) | 00:00:01 |
|* 1 | TABLE ACCESS FULL | T1 | 6 | 126 | 16 (0) | 00:00:01 |
--------------------------------------------------------------------------
......
很明顯,第一個是索引掃描,第二個是全表掃描。同樣,我們來看一下v$sql裡該sql語句有幾條記錄。
hr@ora11g > select sql_text,sql_id,child_number,plan_hash_value
2 from v$sql where sql_text like 'select * from t1 where%';
SQL_TEXT SQL_ID CHILD_NUMBER PLAN_HASH_VALUE
--------------------------------- ------------- ------------ ----------------
select * from t1 where id=:v_id 7y7tt6xyhas1g 0 50753647
可以看到,該SQL語句目前在記憶體裡只存在一個執行計劃,其plan hash value就等於我們在前面plan baseline裡看到的第一個走索引的執行計劃的hash value。我們把該執行計劃顯示出來進行確認。
hr@ora11g > select * from table(dbms_xplan.display_cursor('7y7tt6xyhas1g',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 7y7tt6xyhas1g, child number 0
-------------------------------------
select * from t1 where id=:v_id
Plan hash value: 50753647
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | | | 2 (100) | |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 21 | 2 (0) | 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T1 | 1 | | 1 (0) | 00:00:01 |
......
結果很明顯,正是走索引的執行計劃。然後我們繼續為幫定變數傳入1,多執行幾次。
hr@ora11g > exec :v_id := 1;
hr@ora11g > set autotrace traceonly stat;
hr@ora11g > select * from t1 where id=:v_id;
hr@ora11g > select * from t1 where id=:v_id;
hr@ora11g > select * from t1 where id=:v_id;
注意:這裡,我們之所以要多執行幾次,主要是因為如果只是執行一次或兩次,oracle能夠認識到你傳入的繫結變數落在了第一次的繫結變數(12462)所在的cube之外,但是oracle認為你可能只是偶爾執行該繫結變數,所以並不一定會使用另外那個全表掃描的執行計劃。多執行幾次以後,你會發現consistent gets突然從1390直線下降到了715,這時就說明oracle開始使用新的全表掃描的執行計劃了。
然後,這時我們再去檢視v$sql裡該sql語句有幾條記錄。
hr@ora11g > select sql_text,sql_id,child_number,plan_hash_value
2 from v$sql where sql_text like 'select * from t1 where%';
SQL_TEXT SQL_ID CHILD_NUMBER PLAN_HASH_VALUE
--------------------------------- ------------- ------------ ----------------
select * from t1 where
id=:v_id 7y7tt6xyhas
相關文章
- oracle 11g 新特性之動態繫結變數窺視(一)Oracle變數
- oracle繫結變數窺視(zt)Oracle變數
- Oracle 變數繫結與變數窺視合集Oracle變數
- 【原創】Oracle 變數繫結與變數窺視合集Oracle變數
- Oracle 繫結變數窺探Oracle變數
- oracle bind value peeking繫結變數窺視Oracle變數
- 繫結變數之繫結變數窺探(Bind Peeking)變數
- 繫結變數窺視測試案例變數
- 繫結變數窺測變數
- Oracle之繫結變數Oracle變數
- 繫結變數窺測的演變變數
- 繫結變數優缺點、使用、繫結變數窺探、 Oracle自適應共享遊標變數Oracle
- 10g繫結變數窺探變數
- 【SQL 調優】繫結變數窺測SQL變數
- oracle 11g 變數窺視和acs最佳實踐Oracle變數
- Vue select 繫結動態變數Vue變數
- SQL Server動態SQL,繫結變數SQLServer變數
- Oracle 繫結變數Oracle變數
- 檢視繫結變數變數
- MySQL高階特性——繫結變數MySql變數
- 繫結變數在靜態sql和動態sql中變數SQL
- ORACLE 繫結變數用法總結Oracle變數
- oracle 繫結變數在動態條件統計中的應用Oracle變數
- Oracle 11g 新特性之DRCPOracle
- Oracle 11g新特性之SecureFilesOracle
- 動態條件的繫結變數的解決變數
- oracle 繫結變數(bind variable)Oracle變數
- Oracle 繫結變數 詳解Oracle變數
- 【ORACLE】Oracle繫結變數知識梳理Oracle變數
- 11G R2 V$SQL_MONITOR檢視繫結變數SQL變數
- 繫結變數引數關閉之後,oracle會如何操作變數Oracle
- ORACLE 獲取繫結變數值Oracle變數
- oracle繫結變數的測試Oracle變數
- 繫結變數之基本概念變數
- 【sql調優】使用繫結變數(二)SQL變數
- 檢視未繫結變數的sql變數SQL
- vue動態(type可變)input繫結Vue
- 繫結變數變數