dbms_outln.create_outline在10.2.0.5中建立outline所包含的執行計劃並不正確
Oracle 10g中想要固定執行計劃只能使用outline,sql profile不能起固定sql執行的效果，但是在Oracle 10.2.0.5中使用dbms_outln.create_outline透過使用共享池中的遊標來建立outline,發現建立的outline與遊標中的執行計劃並不一致，而在oracle 10.2.0.4與oracle 11.2.0.4中是透過遊標來建立的outline與cursor的實際執行計劃是一致的。這應該是BUG.
Oracle 10.2.0.5中的測試如下:
定義繫結變數
SQL> var x varchar2(20) SQL> exec :x:='Kabab'; PL/SQL procedure successfully completed.
執行查詢
SQL> select * from t1 where t_meal=:x;
T_ID T_MEAL
---------- --------------------
79999 Kabab
檢視實際的執行計劃
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 7runhd24kgqsf, child number 0
-------------------------------------
select * from t1 where t_meal=:x
Plan hash value: 141743202
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 11 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T1_T_MEAL | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T_MEAL"=:X)
19 rows selected.
可以看到執行計劃使用的是索引範圍掃描
查詢SQL語句的SQL_ID.hash_value,child_number
SQL> select hash_value, child_number, sql_text,sql_id from v$sql where sql_text like 'select * from t1%'; HASH_VALUE CHILD_NUMBER SQL_TEXT SQL_ID ---------- ------------ -------------------------------------------------------------------------------- ------------- 2301090574 0 select * from t1 where t_meal=:x 7runhd24kgqsf
使用遊標來建立outline
SQL> exec dbms_outln.create_outline(2301090574,0); PL/SQL procedure successfully completed. SQL> select name,owner,category,used from dba_outlines; NAME OWNER CATEGORY USED ------------------------------ ------------------------------ ----------------------------- ------ SYS_OUTLINE_16060116155127504 JY DEFAULT UNUSED
查詢outline的hint資訊，可以看到沒有index hint而是full這說明是全表掃描
SQL> select * from dba_outline_hints where owner='JY' and name='SYS_OUTLINE_16060116155127504';
NAME OWNER NODE STAGE JOIN_POS HINT
------------------------------ ------------------------------ ---------- ---------- ---------- -------------------------------------------------------------------------------
SYS_OUTLINE_16060116155127504 JY 1 1 1 FULL(@"SEL$1" "T1"@"SEL$1")
SYS_OUTLINE_16060116155127504 JY 1 1 0 OUTLINE_LEAF(@"SEL$1")
SYS_OUTLINE_16060116155127504 JY 1 1 0 ALL_ROWS
SYS_OUTLINE_16060116155127504 JY 1 1 0 OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
SYS_OUTLINE_16060116155127504 JY 1 1 0 IGNORE_OPTIM_EMBEDDED_HINTS
啟用outline,並重新執行sql語句
SQL> alter session set use_stored_outlines=true;
Session altered.
SQL> select * from t1 where t_meal=:x
2 ;
T_ID T_MEAL
---------- --------------------
79999 Kabab
查詢使用了outline的執行計劃發現卻是全表掃描，並不是遊標中的索引範圍掃描
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 0c2v6n4c0sj6v, child number 0
-------------------------------------
select * from t1 where t_meal=:x
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 47 (100)| |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 11 | 47 (5)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T_MEAL"=:X)
Note
-----
- outline "SYS_OUTLINE_16060116155127504" used for this statement
22 rows selected.
在oracle 10.2.0.5中如果是使用自動建立outline，那麼outline所包含的執行計劃與遊標中的執行計劃是一致的，測試如下:
在會話級啟用自動為查詢語句建立outline
SQL> alter session set create_stored_outlines=true; Session altered.
執行查詢
SQL> select * from t1 where t_meal=:x;
T_ID T_MEAL
---------- --------------------
79999 Kabab
禁用自動建立outline
SQL> alter session set create_stored_outlines=false; Session altered.
檢視語句的執行計劃，使用了索引範圍掃描
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 7runhd24kgqsf, child number 1
-------------------------------------
select * from t1 where t_meal=:x
Plan hash value: 141743202
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 11 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T1_T_MEAL | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T_MEAL"=:X)
19 rows selected.
查詢自動建立outline是否成功
SQL> select name,owner,category,used from dba_outlines;
NAME OWNER CATEGORY USED
------------------------------ ------------------------------ ------------------------------ ------
SYS_OUTLINE_16060117095505105 JY DEFAULT UNUSED
SQL> select * from dba_outline_hints where owner='JY' and name='SYS_OUTLINE_16060117095505105';
NAME OWNER NODE STAGE JOIN_POS HINT
------------------------------ ------------------------------ ---------- ---------- ---------- --------------------------------------------------------------------------------
SYS_OUTLINE_16060117095505105 JY 1 1 1 INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."T_MEAL"))
SYS_OUTLINE_16060117095505105 JY 1 1 0 OUTLINE_LEAF(@"SEL$1")
SYS_OUTLINE_16060117095505105 JY 1 1 0 ALL_ROWS
SYS_OUTLINE_16060117095505105 JY 1 1 0 OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
SYS_OUTLINE_16060117095505105 JY 1 1 0 IGNORE_OPTIM_EMBEDDED_HINTS
啟用outline
SQL> alter session set use_stored_outlines=true; Session altered.
重新執行查詢
SQL> select * from t1 where t_meal=:x;
T_ID T_MEAL
---------- --------------------
79999 Kabab
檢視使用outline的執行計劃使用了索引範圍掃描
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 0c2v6n4c0sj6v, child number 0
-------------------------------------
select * from t1 where t_meal=:x
Plan hash value: 141743202
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 11 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T1_T_MEAL | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T_MEAL"=:X)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note
-----
- outline "SYS_OUTLINE_16060117095505105" used for this statement
23 rows selected.
Oracle 10.2.0.4中的測試如下:
定義繫結變數
SQL> var x varchar2(20) SQL> exec :x:='1'; PL/SQL procedure successfully completed.
執行查詢
SQL> select * from t1 where c1=:x; C1 -------------------- 1
檢視語句的執行計劃
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 0m63029gwn10n, child number 0
-------------------------------------
select * from t1 where c1=:x
Plan hash value: 1629967410
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | INDEX RANGE SCAN| IDX_T1 | 1 | 4 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("C1"=:X)
18 rows selected.
查詢語句的hash_value與sql_id
SQL> select hash_value, child_number, sql_text,sql_id from v$sql where sql_text like 'select * from t1%'; HASH_VALUE CHILD_NUMBER SQL_TEXT SQL_ID ---------- ------------ -------------------------------------------------------------------------------- ------------- 1607074836 0 select * from t1 where c1=:x 0m63029gwn10n
使用遊標來建立outline
SQL> exec dbms_outln.create_outline(1607074836,0); PL/SQL procedure successfully completed.
檢視outline是否建立成功
SQL> select name,owner,category,used from dba_outlines; NAME OWNER CATEGORY USED ------------------------------ ------------------------------ ------------------------------ ------ SYS_OUTLINE_16060115381869401 INSUR_CHANGDE DEFAULT UNUSED
查詢outline的hint可以看到有index hint，這說明使用了索引
SQL> select * from dba_outline_hints where name='SYS_OUTLINE_16060115381869401';
NAME OWNER NODE STAGE JOIN_POS HINT
------------------------------ ------------------------------ ---------- ---------- ---------- --------------------------------------------------------------------------------
SYS_OUTLINE_16060115381869401 INSUR_CHANGDE 1 1 1 INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."C1"))
SYS_OUTLINE_16060115381869401 INSUR_CHANGDE 1 1 0 OUTLINE_LEAF(@"SEL$1")
SYS_OUTLINE_16060115381869401 INSUR_CHANGDE 1 1 0 ALL_ROWS
SYS_OUTLINE_16060115381869401 INSUR_CHANGDE 1 1 0 OPT_PARAM('optimizer_index_caching' 90)
SYS_OUTLINE_16060115381869401 INSUR_CHANGDE 1 1 0 OPT_PARAM('optimizer_index_cost_adj' 20)
SYS_OUTLINE_16060115381869401 INSUR_CHANGDE 1 1 0 OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
SYS_OUTLINE_16060115381869401 INSUR_CHANGDE 1 1 0 IGNORE_OPTIM_EMBEDDED_HINTS
7 rows selected.
啟用outline並重新執行sql語句
SQL> alter session set use_stored_outlines=true; Session altered. SQL> select * from t1 where c1=:x; C1 -------------------- 1
查詢使用outline後的執行計劃，確實是使用的索引範圍掃描與遊標中的執行計劃一致
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 0m63029gwn10n, child number 1
-------------------------------------
select * from t1 where c1=:x
Plan hash value: 1629967410
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | INDEX RANGE SCAN| IDX_T1 | 1 | 4 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("C1"=:X)
Note
-----
- outline "SYS_OUTLINE_16060115381869401" used for this statement
22 rows selected.
Oracle 11.2.0.4的測試如下:
定義繫結變數
SQL> var x number SQL> exec :x:=1 PL/SQL procedure successfully completed.
執行查詢
SQL> select * from t1 where c1=:x;
C1
----------
1
檢視執行計劃
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 0m63029gwn10n, child number 0
-------------------------------------
select * from t1 where c1=:x
Plan hash value: 1369807930
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | INDEX RANGE SCAN| IDX_T1 | 1 | 4 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("C1"=:X)
18 rows selected.
查詢語句的hash_value,child_number,sql_id
SQL> select hash_value, child_number, sql_text,sql_id from v$sql where sql_text like 'select * from t1%'; HASH_VALUE CHILD_NUMBER SQL_TEXT SQL_ID ---------- ------------ -------------------------------------------------------------------------------- ------------- 1607074836 0 select * from t1 where c1=:x 0m63029gwn10n
使用遊標來建立outline
SQL> exec dbms_outln.create_outline(1607074836,0); PL/SQL procedure successfully completed.
檢視outline是否建立成功
SQL> select name,owner,category,used from dba_outlines; NAME OWNER CATEGORY USED ------------------------------ ------------------------------ ------------------------------ ------ SYS_OUTLINE_16060115345355101 JY DEFAULT UNUSED
查詢outline的hint資訊可以看到index hint資訊這說明使用了索引
SQL> select * from dba_outline_hints where owner='JY' and name='SYS_OUTLINE_16060115345355101';
NAME OWNER NODE STAGE JOIN_POS HINT
------------------------------ ------------------------------ ---------- ---------- ---------- --------------------------------------------------------------------------------
SYS_OUTLINE_16060115345355101 JY 1 1 1 INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."C1"))
SYS_OUTLINE_16060115345355101 JY 1 1 0 OUTLINE_LEAF(@"SEL$1")
SYS_OUTLINE_16060115345355101 JY 1 1 0 ALL_ROWS
SYS_OUTLINE_16060115345355101 JY 1 1 0 DB_VERSION('11.2.0.4')
SYS_OUTLINE_16060115345355101 JY 1 1 0 OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
SYS_OUTLINE_16060115345355101 JY 1 1 0 IGNORE_OPTIM_EMBEDDED_HINTS
6 rows selected.
啟用outline並重新執行SQL語句
SQL> alter session set use_stored_outlines=true;
Session altered.
SQL> select * from t1 where c1=:x;
C1
----------
1
查詢使用outline後的執行計劃使用了索引，與遊標中的執行計劃一致
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 0m63029gwn10n, child number 1
-------------------------------------
select * from t1 where c1=:x
Plan hash value: 1369807930
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | INDEX RANGE SCAN| IDX_T1 | 1 | 4 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("C1"=:X)
Note
-----
- outline "SYS_OUTLINE_16060115345355101" used for this statement
22 rows selected.
從測試結果來看，要在10.2.0.5中建立outline固定執行計劃不要使用dbms_outln.create_outline這種方法，因為這種方法生成了outline所包含的執行計劃並不正確。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26015009/viewspace-2112372/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 程式中使用繫結變數，執行計劃不正確變數
- 統計資訊不正確導致執行計劃的錯誤選擇
- 使用OUTLINE固定執行計劃
- Oracle 表連線 篩選欄位執行計劃不正確Oracle
- Linux計劃任務crontab執行指令碼不正確的問題Linux指令碼
- 執行計劃大剛OUTLINE的管理
- 用outline修改固定執行計劃
- 【OUTLINE】環境不滿足OUTLINE記錄的執行計劃時會選擇其他執行計劃
- oracle使用outline固定執行計劃事例Oracle
- oracle sqlprofile 固定執行計劃，並遷移執行計劃OracleSQL
- 【OUTLINE】使用Oracle Outline技術暫時鎖定SQL的執行計劃OracleSQL
- 看執行計劃是否正確
- 怎樣得到準確的執行計劃
- 在Oracle中，如何得到真實的執行計劃？Oracle
- Runtime.exec執行dos命令不正確
- 建立索引調整sql的執行計劃索引SQL
- 編輯計劃任務並執行
- 看懂Oracle中的執行計劃Oracle
- set autotrace on 產生不準確的執行計劃
- 在Windows環境下使用NodeJS的fast-glob不正確執行的問題WindowsNodeJSAST
- oracle中執行計劃中的cardinalityOracle
- SQLSERVER中得到執行計劃的方式SQLServer
- MySQL中in（常量列表）的執行計劃MySql
- 在Web應用程式中執行計劃任務（多執行緒） (轉)Web執行緒
- sql執行計劃變更和刪除快取中執行計劃的方法SQL快取
- 執行計劃-1：獲取執行計劃
- 如何清除某條SQL在庫快取中的執行計劃SQL快取
- Master Exam中答案不正確的題AST
- 年假計算居然不正確
- 分析執行計劃優化SQLORACLE的執行計劃(轉)優化SQLOracle
- oracle中開啟執行計劃Oracle
- 執行計劃中cost, card的含義
- laravel建立定時任務並在windows下執行LaravelWindows
- 利用SSIS在SQL Azure中執行計劃任務（下）KCSQL
- 利用SSIS在SQL Azure中執行計劃任務（上）DJSQL
- 【執行計劃】Oracle獲取執行計劃的幾種方法Oracle
- 【Oracle】-【索引-HINT，執行計劃】-帶HINT的索引執行計劃Oracle索引
- 分析執行計劃最佳化SQLORACLE的執行計劃(轉)SQLOracle