繫結變數窺測的演變
為了減少帶繫結變數的sql的解析時間，oracle 9i引入的繫結變數窺測的功能。也就是在同一個SQL的變數被賦於不同值時採用同一個遊標，這樣雖然節省了sql的解析時間，但有時所採用的執行計劃並不是最優的。這個功能可以透過一個隱含引數"_optim_peek_user_binds"開啟或關閉。
繫結變數窺測存在的問題
下面實驗一下，先準備一個測試表
SQL> create table t as select 1 n, 'aaa' c from dual CONNECT BY level <= 1000;
Table created.
SQL> insert into t select 2,'bbb' from dual CONNECT BY level <= 10;
10 rows created.
SQL> commit;
Commit complete.
SQL> select count(1) from t where n=1;
COUNT(1)
----------
1000
SQL> select count(1) from t where n=2;
COUNT(1)
----------
10
SQL> create index in_t on t (n);
Index created.
第一個欄位為1有1000條記錄，第2個欄位為2有10條記錄，在上面建立一個索引
SQL> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
SQL> select column_name,density,num_buckets,histogram from dba_tab_col_statistics where table_name='T';
COLUMN_NAME DENSITY NUM_BUCKETS HISTOGRAM
------------------------------ ---------- ----------- ---------------
C .5 1 NONE
N .00049505 2 FREQUENCY
SQL> vari n number;
SQL> exec :n :=1;
PL/SQL procedure successfully completed.
SQL> select count(c) from t where n=:n;
COUNT(C)
----------
1000
SQL> select * from table(dbms_xplan.display_cursor(null,null,'all'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 2f973m14fq0dg, child number 0
-------------------------------------
select count(c) from t where n=:n
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 2 | TABLE ACCESS FULL| T | 1000 | 7000 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
2 - filter("N"=:N)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT("C")[22]
2 - "C"[CHARACTER,3]
31 rows selected.
從上面看到當變數n為1時走的是全表掃描，這樣當然是正確的,因為要讀取99％表中的記錄；如果變數為2，只讀取1％的記錄應該走索引吧，測試一下：
SQL> exec :n :=2;
PL/SQL procedure successfully completed.
SQL> select count(c) from t where n=:n;
COUNT(C)
----------
10
SQL> select * from table(dbms_xplan.display_cursor('2f973m14fq0dg',null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2f973m14fq0dg, child number 0
-------------------------------------
select count(c) from t where n=:n
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | TABLE ACCESS FULL| T | 1000 | 7000 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.6')
DB_VERSION('11.1.0.6')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T"@"SEL$1")
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :N (NUMBER): 1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("N"=:N)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT("C")[22]
2 - "C"[CHARACTER,3]
50 rows selected.
結果還是全表掃描，而且注意執行計劃裡有“Peeked Binds (identified by position): ”一項n的值為1,但n明明是2
SQL> print :n
N
----------
2
這就是繫結變數窺測的的功能是起作用，我們可以看到n=2時用的sql_id和child number和前面執行的n=1時的一樣，oracle的最佳化器自動取了前面一次的執行計劃。為了解決這個問題，我們可以flush shared_pool，把上次生成的解析後的sql去掉，但這樣做會造成整個shared pool中的sql全部失效，在生產的高峰做這樣的事情可能會引起災難性的後果，在oracle 10.2.0.4後的版本提供了一個過程dbms_shared_pool.purge可以只讓一條指定的sql失效（但在10.2.0.4的版本上並不生效，參考 metalink Doc ID: 751876.1 10.2.0.4預設不開啟,要靠event 5614566或者補丁5614566來啟用），我們可以在11G上試驗一下：
SQL> select address,hash_value,executions,parse_calls from v$sql where sql_TEXT like 'select count(c) from t where n=:n';
ADDRESS HASH_VALUE EXECUTIONS PARSE_CALLS
---------------- ---------- ---------- -----------
000000009F89FAC8 1223360943 2 2
SQL> exec dbms_shared_pool.purge('000000009F89FAC8,1223360943','a');
PL/SQL procedure successfully completed.
SQL> select count(*) from v$sql where sql_TEXT like 'select count(c) from t where n=:n';
COUNT(*)
----------
0
我們也可以透過修改隱含引數_optim_peek_user_binds來遮蔽繫結變數窺測這項功能：
SQL> Alter session set "_optim_peek_user_binds"=false;
Session altered.
SQL> select count(c) from t where n=:n;
COUNT(C)
----------
10
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2f973m14fq0dg, child number 1
-------------------------------------
select count(c) from t where n=:n
Plan hash value: 2140154646
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 505 | 3535 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IN_T | 505 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T@SEL$1
3 - SEL$1 / T@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.6')
DB_VERSION('11.1.0.6')
OPT_PARAM('_optim_peek_user_binds' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."N"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("N"=:N)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT("C")[22]
2 - "C"[CHARACTER,3]
3 - "T".ROWID[ROWID,10]
49 rows selected.
我們看到這次走了索引，sql_id還是一樣，但child number從0變成了1,生成了一個新的執行計劃。
我們也可以用outline來固定這條sql的執行計劃，如可以規定所有這樣的sql都必需走全表掃描。可以採用下面兩種方法生成這樣的outline。
- 可以用現在走索引的執行計劃生成一個outline，然後直接修改OUTLN的相關表將執行計劃改成走全表掃描。這就要求對outline的相關語句非常熟悉才能做到，或者可以參考前面dbms_xplan.display_cursor中的Outline Data部分來修改相關的表，注意format要設定成advanced才會有outline部分的生成；
- 也可以用當前sql生成一個走索引的執行計劃，再加hint生成另一個全表掃描的執行，然後將這兩個執行計劃換一下即可。
第二種方法比較好操作，我們就用第二種方法。
SQL> alter session set "_optim_peek_user_binds"=true;
Session altered.
SQL> create outline line_t on select count(c) from t where n=:n;
Outline created.
SQL> create outline full_t on select /*+ full(t) */ count(c) from t where n=:n;
Outline created.
SQL> select ol_name,hint_text from OUTLN.OL$HINTS;
OL_NAME HINT_TEXT
----------- ------------------------------
LINE_T INDEX_RS_ASC(@"SEL$1" "T"@"SEL
$1" ("T"."N"))
LINE_T OUTLINE_LEAF(@"SEL$1")
LINE_T ALL_ROWS
LINE_T DB_VERSION('11.1.0.6')
LINE_T OPTIMIZER_FEATURES_ENABLE('11.
1.0.6')
LINE_T IGNORE_OPTIM_EMBEDDED_HINTS
FULL_T FULL(@"SEL$1" "T"@"SEL$1")
FULL_T OUTLINE_LEAF(@"SEL$1")
FULL_T ALL_ROWS
FULL_T DB_VERSION('11.1.0.6')
FULL_T OPTIMIZER_FEATURES_ENABLE('11.
1.0.6')
FULL_T IGNORE_OPTIM_EMBEDDED_HINTS
12 rows selected.
看看生成的兩個outline已經達到了我們的要求，現在把它們換個個：
SQL> UPDATE OUTLN.OL$HINTS SET OL_NAME=DECODE(OL_NAME,'FULL_T','LINE_T','LINE_T','FULL_T') WHERE OL_NAME IN ('LINE_T','FULL_T');
12 rows updated.
SQL> commit;
Commit complete.
SQL> select ol_name,hint_text from OUTLN.OL$HINTS;
OL_NAME HINT_TEXT
----------- ------------------------------
FULL_T INDEX_RS_ASC(@"SEL$1" "T"@"SEL
$1" ("T"."N"))
FULL_T OUTLINE_LEAF(@"SEL$1")
FULL_T ALL_ROWS
FULL_T DB_VERSION('11.1.0.6')
FULL_T OPTIMIZER_FEATURES_ENABLE('11.
1.0.6')
FULL_T IGNORE_OPTIM_EMBEDDED_HINTS
LINE_T FULL(@"SEL$1" "T"@"SEL$1")
LINE_T OUTLINE_LEAF(@"SEL$1")
LINE_T ALL_ROWS
LINE_T DB_VERSION('11.1.0.6')
LINE_T OPTIMIZER_FEATURES_ENABLE('11.
1.0.6')
LINE_T IGNORE_OPTIM_EMBEDDED_HINTS
12 rows selected.
換好了，
SQL> ALTER SESSION SET USE_STORED_OUTLINES = TRUE;
Session altered.
SQL> alter system flush shared_pool;
System altered.
SQL> select count(c) from t where n=:n;
COUNT(C)
----------
10 SQL> SELECT * FROM table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2f973m14fq0dg, child number 0
-------------------------------------
select count(c) from t where n=:n
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | TABLE ACCESS FULL| T | 1000 | 7000 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("N"=:N)
Note
-----
- outline "LINE_T" used for this statement
23 rows selected.
現在我們可以看到這個sql走了LINE_T的outline，是全表掃描。
11G的適應性遊標共享
為了解決這個問題，Oracle 11g中引入了一個“適應性遊標共享（adaptive cursor sharing）”的新功能，它可以在重用遊標時識別是否有更好的執行計劃，如果有，會再生成一個新子游標。
我們在將n分別等於1,和2後，執行幾次這個sql，查詢一下游標的情況：
SQL> SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware, is_shareable
2 FROM v$sql
3 WHERE sql_text = 'select count(c) from t where n=:n'
4 ORDER BY child_number;
SQL_ID CHILD_NUMBER I I I
------------- ------------ - - -
2f973m14fq0dg 0 Y N N
2f973m14fq0dg 1 Y Y Y
2f973m14fq0dg 2 Y Y Y
可以看出這個sql對應了3個子遊標，其中0號子游標因為沒有使用繫結變數可知（is_bind_aware是N），而被設定成不再共享（is_shareable為N）。
SQL> select * from table(dbms_xplan.display_cursor('2f973m14fq0dg',1,'basic'));
PLAN_TABLE_OUTPUT
---------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select count(c) from t where n=:n
Plan hash value: 2966233522
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | TABLE ACCESS FULL| T |
-----------------------------------
14 rows selected.
SQL> select * from table(dbms_xplan.display_cursor('2f973m14fq0dg',2,'basic'));
PLAN_TABLE_OUTPUT
--------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select count(c) from t where n=:n
Plan hash value: 2140154646
---------------------------------------------
| Id | Operation | Name |
---------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | TABLE ACCESS BY INDEX ROWID| T |
| 3 | INDEX RANGE SCAN | IN_T |
---------------------------------------------
15 rows selected.
從上面的執行計劃可以看出，子游標1,2分別對應的是全表掃描和索引飯碗掃描，從下面的檢視也可以看出1號遊標要處理1001行，2號遊標處理21行，基本和實際的資料相符。
SQL> SELECT child_number, peeked, executions, rows_processed, buffer_gets FROM v$sql_cs_statistics WHERE sql_id = '2f973m14fq0dg'
2 ORDER BY child_number;
CHILD_NUMBER P EXECUTIONS ROWS_PROCESSED BUFFER_GETS
------------ - ---------- -------------- -----------
0 Y 1 21 11
1 Y 1 1001 15
2 Y 1 21 11
這和下面直方圖的統計也基本一致：
SQL> select endpoint_value,endpoint_number from user_tab_histograms where column_name='N';
ENDPOINT_VALUE ENDPOINT_NUMBER
-------------- ---------------
1 1000
2 1010
這個檢視顯示了子游標的執行次數和直方圖中桶的對應關係
SQL> SELECT child_number, bucket_id, count FROM v$sql_cs_histogram WHERE sql_id = '2f973m14fq0dg' ORDER BY child_number;
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
0 1 1
0 0 1
0 2 0
1 0 0
1 1 4
1 2 0
2 1 0
2 0 4
2 2 0
從上面可以看出oracle選擇哪個子游標的依據是統計資訊中的的直方圖的分佈，因此DBA在平時就要關注這些資訊，一旦出現低效的sql執行計劃時才能及時進行處理。
雖然我們不能完全依賴11G的這個新功能，但我們可以發現oracle是越來越聰明瞭。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/21601207/viewspace-680509/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 繫結變數窺測變數
- 繫結變數窺視測試案例變數
- 【SQL 調優】繫結變數窺測SQL變數
- 繫結變數之繫結變數窺探(Bind Peeking)變數
- Oracle 繫結變數窺探Oracle變數
- Oracle 變數繫結與變數窺視合集Oracle變數
- oracle繫結變數窺視(zt)Oracle變數
- 【原創】Oracle 變數繫結與變數窺視合集Oracle變數
- 10g繫結變數窺探變數
- 繫結變數的測試變數
- 繫結變數優缺點、使用、繫結變數窺探、 Oracle自適應共享遊標變數Oracle
- oracle bind value peeking繫結變數窺視Oracle變數
- oracle繫結變數的測試Oracle變數
- 繫結變數變數
- Oracle 繫結變數Oracle變數
- SQL使用繫結變數，測試例項。SQL變數
- 檢視繫結變數變數
- PLSQL使用繫結變數SQL變數
- Oracle之繫結變數Oracle變數
- 關於繫結變數變數
- 【優化】使用繫結變數 OR 不使用繫結變數，這不是問題！優化變數
- 使用繫結變數窺探後的cardinality和selectivity的計算方法變數
- oracle 11g 新特性之動態繫結變數窺視(二)Oracle變數
- oracle 11g 新特性之動態繫結變數窺視(一)Oracle變數
- 繫結變數的一個例子變數
- 繫結變數的使用範圍變數
- 查詢繫結變數的值變數
- 關於繫結變數的使用變數
- 【最佳化】使用繫結變數 OR 不使用繫結變數，這不是問題！變數
- ORACLE 繫結變數用法總結Oracle變數
- oracle 繫結變數（bind variable）Oracle變數
- 如何獲取繫結變數變數
- Oracle 繫結變數 詳解Oracle變數
- PL/SQL中繫結變數使用的簡單測試SQL變數
- 使用繫結變數的一點總結！變數
- 關於繫結變數的SQL繫結什麼值變數SQL
- 關於DSS中的繫結變數變數
- 檢視未繫結變數的sql變數SQL