【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
- Oracle sql執行計劃OracleSQL
- 通過鎖定表的統計資訊來穩定sql的執行計劃SQL
- 【TUNE_ORACLE】定製化執行計劃SQL參考OracleSQL
- Oracle SQL Profile固定執行計劃的方法OracleSQL
- CSS outline-style 屬性： CSS3 outline-CSSS3
- CSS outline輪廓CSS
- outline初體驗
- 利用hints控制outline
- 在繫結變數下使用outline變數
- Oracle資料庫關於SQL的執行計劃(轉)Oracle資料庫SQL
- Oracle檢視sql_id 的歷史執行計劃OracleSQL
- Oracle執行計劃Explain Plan 如何使用OracleAI
- .Oracle固定執行計劃之SQL PROFILE概要檔案OracleSQL
- 【執行計劃】Oracle獲取執行計劃的幾種方法Oracle
- oracle 固定執行計劃Oracle
- outline優化一例優化
- Outline for Mac(筆記軟體)Mac筆記
- oracle查詢sql執行耗時、執行時間、sql_idOracleSQL
- CSS 不規則的輪廓-outlineCSS
- 獲取oracle sql語句詳細些執行計劃OracleSQL
- 如何檢視SQL的執行計劃SQL
- 在MySQL中使用explain查詢SQL的執行計劃MySqlAI
- Oracle檢視執行計劃的命令Oracle
- Oracle優化案例-從執行計劃定位SQL問題（三）Oracle優化SQL
- 【併發技術02】傳統執行緒技術中的定時器技術執行緒定時器
- 根據google的outline科學上網Go
- Oracle DB 相關常用sql彙總7【手工繫結sql執行計劃】OracleSQL
- Oracle調優之看懂Oracle執行計劃Oracle
- Oracle-繫結執行計劃Oracle
- Oracle利用coe_load_sql_profile指令碼繫結執行計劃OracleSQL指令碼
- 【SQL_PLAN】Oracle 透過檢視sql_plan 格式化執行計劃SQLOracle
- [ORACLE] SQL執行OracleSQL
- 超實用的筆記軟體：Outline for Mac筆記Mac
- MySQL——通過EXPLAIN分析SQL的執行計劃MySqlAI
- 生產環境使用10053分析Oracle的執行計劃Oracle
- Outline for Mac(筆記本編輯軟體)Mac筆記
- 邊框(Border) 和 輪廓(Outline) 屬性
- 好用的mac筆記軟體推薦：Outline for MacMac筆記