【OUTLINE】使用Oracle Outline技術暫時鎖定SQL的執行計劃
Oracle的技術可以在特殊情況下保證的穩定性。在極端情況下可以使用這個實現暫時執行計劃的目的。
主要使用場景如下：
短時間內無法完成的最佳化任務，此時可以使用outline暫時鎖定SQL執行計劃；
在CBO最佳化模式下，當統計資訊出現問題時，會導致執行計劃出現異常變化，此時可以使用outline暫時調整SQL執行計劃；
由於的bug導致SQL的執行計劃出現異常，使用outline鎖定執行計劃。
記錄一下關於outline的使用方法，供參考。
1.環境準備
1）建立使用者，並授予適當許可權，注意，使用者需要具有create any outline許可權
sys@ora10g> create user secooler identified by secooler default tablespace TBS_SECOOLER_D;
User created.
sys@ora10g> grant connect,resource to secooler;
Grant succeeded.
sys@ora10g> grant create any outline to secooler;
Grant succeeded.
2）在secooler使用者中建立表T
secooler@ora10g> create table t as select * from all_objects;
Table created.
secooler@ora10g> select count(*) from t;
COUNT(*)
----------
4548
2.建立outline
1）解鎖outln使用者
sys@asd> alter user outln identified by outln account unlock;
User altered.
2）建立一個outline，取名叫做t_outln1，指定它的category名字為CATEGORY_T
outln@ora10g> conn secooler/secooler
Connected.
secooler@ora10g> create outline t_outln1 for category CATEGORY_T on select * from t where OBJECT_ID=258;
Outline created.
3）此時outln使用者下的三張表OL$、OL$HINTS和OL$NODES中便會記錄與此次操作的相關資訊。執行計劃會記錄在OL$HINTS中。
secooler@ora10g> conn outln/outln
Connected.
outln@ora10g> select hint_text from ol$hints where ol_name = 'T_OUTLN1' order by hint#;
HINT_TEXT
--------------------------------------------------
FULL(@"SEL$1" "T"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
IGNORE_OPTIM_EMBEDDED_HINTS
4）比對一下對應的執行計劃
outln@ora10g> conn secooler/secooler
Connected.
secooler@ora10g> set autotrace traceonly ;
secooler@ora10g> select * from t where OBJECT_ID=258;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 256 | 36 (3)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 2 | 256 | 36 (3)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=258)
Note
-----
- dynamic sampling used for this statement
這裡記錄了一個全表掃描的的執行計劃。
5）關於建立的outline基本資訊也可以透過dba_outlines檢視進行查詢
secooler@ora10g> select NAME,OWNER,CATEGORY,SQL_TEXT from dba_outlines;
NAME OWNER CATEGORY
------------------------------ ------------- -------------------
SQL_TEXT
----------------------------------------------------------------
T_OUTLN1 SECOOLER CATEGORY_T
select * from t where OBJECT_ID=258
3.使用outline
1）為了對比，我們建立索引，改變SQL語句的執行計劃
（1）在T表的X欄位建立索引
secooler@ora10g> create index i_t on t(object_id);
Index created.
（2）檢視此時SQL的執行計劃
secooler@ora10g> set autotrace traceonly explain;
secooler@ora10g> select * from t where OBJECT_ID=258;
Execution Plan
----------------------------------------------------------
Plan hash value: 2928007915
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 128 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 128 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=258)
Note
-----
- dynamic sampling used for this statement
從執行計劃上可以看到此時該SQL使用到了索引，沒有進行全表掃面。
2）強制SQL使用ontline中記錄的執行計劃
（1）設定系統使用category為CATEGORY_T的outline
secooler@ora10g> alter system set use_stored_outlines=CATEGORY_T;
System altered.
（2）再次檢視SQL語句的執行計劃
secooler@ora10g> select * from t where OBJECT_ID=258;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 124 | 15872 | 36 (3)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 124 | 15872 | 36 (3)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=258)
Note
-----
- outline "T_OUTLN1" used for this statement
從最後的註釋上我們已經可以看到，此時SQL語句執行過程中使用的是outln中記錄的執行計劃。SQL在獲取資料的時候走的是全表掃描。
3）消除ontline對SQL語句的影響
（1）第一種方法是調整use_stored_outlines引數為false
secooler@ora10g> alter system set use_stored_outlines=false;
System altered.
secooler@ora10g> select * from t where OBJECT_ID=258;
Execution Plan
----------------------------------------------------------
Plan hash value: 2928007915
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 128 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 128 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=258)
Note
-----
- dynamic sampling used for this statement
（2）第二種方法是停用具體的outline
這種方法可以在use_stored_outlines引數起作用的前提下停用具體的outline。
secooler@ora10g> alter system set use_stored_outlines=CATEGORY_T;
System altered.
secooler@ora10g> select * from t where OBJECT_ID=258;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 124 | 15872 | 36 (3)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 124 | 15872 | 36 (3)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=258)
Note
-----
- outline "T_OUTLN1" used for this statement
secooler@ora10g> alter outline t_outln1 disable;
Outline altered.
secooler@ora10g> select * from t where OBJECT_ID=258;
Execution Plan
--------------------------------------------------
Plan hash value: 2928007915
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 128 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 128 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=258)
Note
-----
- dynamic sampling used for this statement
此時ontline T_OUTLN1已經被停用。
4.清除outline
我們在環境下可以使用dbms_outln.drop_by_cat完成清空具體category的目的。
secooler@ora10g> conn outln/outln
Connected.
outln@ora10g> select hint_text from ol$hints where ol_name = 'T_OUTLN1' order by hint#;
HINT_TEXT
--------------------------------------------------
FULL(@"SEL$1" "T"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
IGNORE_OPTIM_EMBEDDED_HINTS
outln@ora10g> exec dbms_outln.drop_by_cat('CATEGORY_T');
PL/SQL procedure successfully completed.
outln@ora10g> select hint_text from ol$hints where ol_name = 'T_OUTLN1' order by hint#;
no rows selected
5.關於USE_STORED_OUTLINES引數的說明
USE_STORED_OUTLINES引數不像一般的引數可以在引數檔案中進行設定，但我們可以使用常規的方法對其進行修改。
ALTER SESSION SET USE_STORED_OUTLINES = TRUE | FALSE | category;
ALTER SYSTEM SET USE_STORED_OUTLINES = TRUE | FALSE | category;
6.小結
透過文中的描述，大家應該對Oracle Outline技術有一個比較詳細的瞭解。在CBO最佳化模式下，很有可能遇到執行計劃不穩定及不準確的情況。在這種場景下我們可以考慮使用這項技術暫時規避執行計劃變化帶來的效能問題。
主要使用場景如下：
短時間內無法完成的最佳化任務，此時可以使用outline暫時鎖定SQL執行計劃；
在CBO最佳化模式下，當統計資訊出現問題時，會導致執行計劃出現異常變化，此時可以使用outline暫時調整SQL執行計劃；
由於的bug導致SQL的執行計劃出現異常，使用outline鎖定執行計劃。
記錄一下關於outline的使用方法，供參考。
1.環境準備
1）建立使用者，並授予適當許可權，注意，使用者需要具有create any outline許可權
sys@ora10g> create user secooler identified by secooler default tablespace TBS_SECOOLER_D;
User created.
sys@ora10g> grant connect,resource to secooler;
Grant succeeded.
sys@ora10g> grant create any outline to secooler;
Grant succeeded.
2）在secooler使用者中建立表T
secooler@ora10g> create table t as select * from all_objects;
Table created.
secooler@ora10g> select count(*) from t;
COUNT(*)
----------
4548
2.建立outline
1）解鎖outln使用者
sys@asd> alter user outln identified by outln account unlock;
User altered.
2）建立一個outline，取名叫做t_outln1，指定它的category名字為CATEGORY_T
outln@ora10g> conn secooler/secooler
Connected.
secooler@ora10g> create outline t_outln1 for category CATEGORY_T on select * from t where OBJECT_ID=258;
Outline created.
3）此時outln使用者下的三張表OL$、OL$HINTS和OL$NODES中便會記錄與此次操作的相關資訊。執行計劃會記錄在OL$HINTS中。
secooler@ora10g> conn outln/outln
Connected.
outln@ora10g> select hint_text from ol$hints where ol_name = 'T_OUTLN1' order by hint#;
HINT_TEXT
--------------------------------------------------
FULL(@"SEL$1" "T"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
IGNORE_OPTIM_EMBEDDED_HINTS
4）比對一下對應的執行計劃
outln@ora10g> conn secooler/secooler
Connected.
secooler@ora10g> set autotrace traceonly ;
secooler@ora10g> select * from t where OBJECT_ID=258;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 256 | 36 (3)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 2 | 256 | 36 (3)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=258)
Note
-----
- dynamic sampling used for this statement
這裡記錄了一個全表掃描的的執行計劃。
5）關於建立的outline基本資訊也可以透過dba_outlines檢視進行查詢
secooler@ora10g> select NAME,OWNER,CATEGORY,SQL_TEXT from dba_outlines;
NAME OWNER CATEGORY
------------------------------ ------------- -------------------
SQL_TEXT
----------------------------------------------------------------
T_OUTLN1 SECOOLER CATEGORY_T
select * from t where OBJECT_ID=258
3.使用outline
1）為了對比，我們建立索引，改變SQL語句的執行計劃
（1）在T表的X欄位建立索引
secooler@ora10g> create index i_t on t(object_id);
Index created.
（2）檢視此時SQL的執行計劃
secooler@ora10g> set autotrace traceonly explain;
secooler@ora10g> select * from t where OBJECT_ID=258;
Execution Plan
----------------------------------------------------------
Plan hash value: 2928007915
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 128 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 128 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=258)
Note
-----
- dynamic sampling used for this statement
從執行計劃上可以看到此時該SQL使用到了索引，沒有進行全表掃面。
2）強制SQL使用ontline中記錄的執行計劃
（1）設定系統使用category為CATEGORY_T的outline
secooler@ora10g> alter system set use_stored_outlines=CATEGORY_T;
System altered.
（2）再次檢視SQL語句的執行計劃
secooler@ora10g> select * from t where OBJECT_ID=258;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 124 | 15872 | 36 (3)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 124 | 15872 | 36 (3)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=258)
Note
-----
- outline "T_OUTLN1" used for this statement
從最後的註釋上我們已經可以看到，此時SQL語句執行過程中使用的是outln中記錄的執行計劃。SQL在獲取資料的時候走的是全表掃描。
3）消除ontline對SQL語句的影響
（1）第一種方法是調整use_stored_outlines引數為false
secooler@ora10g> alter system set use_stored_outlines=false;
System altered.
secooler@ora10g> select * from t where OBJECT_ID=258;
Execution Plan
----------------------------------------------------------
Plan hash value: 2928007915
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 128 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 128 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=258)
Note
-----
- dynamic sampling used for this statement
（2）第二種方法是停用具體的outline
這種方法可以在use_stored_outlines引數起作用的前提下停用具體的outline。
secooler@ora10g> alter system set use_stored_outlines=CATEGORY_T;
System altered.
secooler@ora10g> select * from t where OBJECT_ID=258;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 124 | 15872 | 36 (3)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 124 | 15872 | 36 (3)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=258)
Note
-----
- outline "T_OUTLN1" used for this statement
secooler@ora10g> alter outline t_outln1 disable;
Outline altered.
secooler@ora10g> select * from t where OBJECT_ID=258;
Execution Plan
--------------------------------------------------
Plan hash value: 2928007915
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 128 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 128 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=258)
Note
-----
- dynamic sampling used for this statement
此時ontline T_OUTLN1已經被停用。
4.清除outline
我們在環境下可以使用dbms_outln.drop_by_cat完成清空具體category的目的。
secooler@ora10g> conn outln/outln
Connected.
outln@ora10g> select hint_text from ol$hints where ol_name = 'T_OUTLN1' order by hint#;
HINT_TEXT
--------------------------------------------------
FULL(@"SEL$1" "T"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
IGNORE_OPTIM_EMBEDDED_HINTS
outln@ora10g> exec dbms_outln.drop_by_cat('CATEGORY_T');
PL/SQL procedure successfully completed.
outln@ora10g> select hint_text from ol$hints where ol_name = 'T_OUTLN1' order by hint#;
no rows selected
5.關於USE_STORED_OUTLINES引數的說明
USE_STORED_OUTLINES引數不像一般的引數可以在引數檔案中進行設定，但我們可以使用常規的方法對其進行修改。
ALTER SESSION SET USE_STORED_OUTLINES = TRUE | FALSE | category;
ALTER SYSTEM SET USE_STORED_OUTLINES = TRUE | FALSE | category;
6.小結
透過文中的描述，大家應該對Oracle Outline技術有一個比較詳細的瞭解。在CBO最佳化模式下，很有可能遇到執行計劃不穩定及不準確的情況。在這種場景下我們可以考慮使用這項技術暫時規避執行計劃變化帶來的效能問題。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/11411056/viewspace-734335/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle使用outline固定執行計劃事例Oracle
- 使用OUTLINE固定執行計劃
- 【OUTLINE】環境不滿足OUTLINE記錄的執行計劃時會選擇其他執行計劃
- 執行計劃大剛OUTLINE的管理
- 用outline修改固定執行計劃
- zt_sql baseline_sql profile_sql outline修改固定執行計劃SQL
- dbms_outln.create_outline在10.2.0.5中建立outline所包含的執行計劃並不正確
- 關於Oracle Outline使用Oracle
- 使用sql profile實現outline的功能SQL
- Oracle Outline的使用及注意事項Oracle
- Oracle sql執行計劃OracleSQL
- 通過鎖定表的統計資訊來穩定sql的執行計劃SQL
- 轉：Oracle Outline的使用及注意事項Oracle
- outline:0與outline:none區別None
- oracle 執行計劃設定Oracle
- Oracle中檢視已執行sql的執行計劃OracleSQL
- Oracle 檢視SQL的執行計劃OracleSQL
- 【TUNE_ORACLE】定製化執行計劃SQL參考OracleSQL
- oracle執行計劃的使用(EXPLAIN)OracleAI
- ubuntu上使用cron執行定時任務計劃Ubuntu
- Oracle SQL Profile固定執行計劃的方法OracleSQL
- oracle中跟蹤sql執行計劃的方法OracleSQL
- SQL的執行計劃SQL
- 使用sql profile固定執行計劃SQL
- Oracle 獲取SQL執行計劃方法OracleSQL
- Oracle手動固定SQL執行計劃OracleSQL
- oracle儲存提綱（stored outline）Oracle
- Oracle檢視正在執行的SQL以及執行計劃分析OracleSQL
- sql 執行計劃SQL
- sql最佳化：使用儲存提綱穩定sql執行計劃SQL
- CSS outline-style 屬性： CSS3 outline-CSSS3
- Oracle SQL執行計劃歷史資訊表DBA_HIST_SQL_PLAN的使用OracleSQL
- 使用PL/SQL檢視執行計劃SQL
- 把Oracle的SQL執行計劃 授權給普通使用者OracleSQL
- 使用SQL PROFILE 給出合理的執行計劃SQL
- 決定一個SQL執行效率的是執行計劃, 而不是SQL的寫法SQL
- SQL Server執行計劃SQLServer
- SQL執行計劃分析SQL