【OUTLINE】使用OracleOutline技術(shù)暫時鎖定SQL的執(zhí)行計劃
Oracle的Outline技術(shù)可以在特殊情況下保證執(zhí)行計劃的穩(wěn)定性。在極端情況下可以使用此項技術(shù)實現(xiàn)暫時鎖定執(zhí)行計劃的目的。
主要使用場景如下：
①短時間內(nèi)無法完成SQL的優(yōu)化任務(wù)，此時可以使用outline暫時鎖定SQL執(zhí)行計劃；
②在CBO優(yōu)化模式下，當(dāng)統(tǒng)計信息出現(xiàn)問題時，會導(dǎo)致執(zhí)行計劃出現(xiàn)異常變化，此時可以使用outline暫時調(diào)整SQL執(zhí)行計劃；
③由于數(shù)據(jù)庫的bug導(dǎo)致SQL的執(zhí)行計劃出現(xiàn)異常，使用outline鎖定執(zhí)行計劃。
記錄一下關(guān)于outline的使用方法，供參考。
1.初始化環(huán)境
1）創(chuàng)建用戶secooler，并授予適當(dāng)權(quán)限，注意，用戶需要具有create any outline權(quán)限
SYS@PROD> create user secooler identified by secooler;
User created.
SYS@PROD> grant connect,resource to secooler;
Grant succeeded.
SYS@PROD> grant create any outline,alter any outline to secooler;
Grant succeeded.
2）在secooler用戶中創(chuàng)建表T
SYS@PROD> conn secooler/secooler
Connected.
SECOOLER@PROD> create table t as select * from all_objects;
Table created.
SECOOLER@PROD> select count(*) from t;
COUNT(*)
----------
4448
2.創(chuàng)建outline
1）解鎖outln用戶
SECOOLER@PROD> conn / as sysdba
Connected.
SYS@PROD> alter user outln identified by outln account unlock;
User altered.
2）創(chuàng)建一個outline，取名叫做t_outln1，指定它的category名字為CATEGORY_T
SYS@PROD> conn secooler/secooler
Connected.
SECOOLER@PROD> 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中便會記錄與此次操作的相關(guān)信息。執(zhí)行計劃會記錄在OL$HINTS中。
SECOOLER@PROD> conn outln/outln
Connected.
OUTLN@PROD> 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.1')
IGNORE_OPTIM_EMBEDDED_HINTS
4）比對一下對應(yīng)的執(zhí)行計劃
OUTLN@PROD> conn secooler/secooler
Connected.
SECOOLER@PROD> set autotrace traceonly explain;
SECOOLER@PROD> select * from t where OBJECT_ID=258;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 128 | 19 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 128 | 19 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=258)
Note
-----
- dynamic sampling used for this statement
這里記錄了一個全表掃描的執(zhí)行計劃。
5）關(guān)于創(chuàng)建的outline基本信息也可以通過dba_outlines視圖進行查詢
SECOOLER@PROD> conn / as sysdba
Connected.
SYS@PROD> 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）為了對比，我們創(chuàng)建索引，改變SQL語句的執(zhí)行計劃
（1）在T表的X字段創(chuàng)建索引
SYS@PROD> conn secooler/secooler
Connected.
SECOOLER@PROD> create index i_t on t(object_id);
Index created.
（2）查看此時SQL的執(zhí)行計劃
SECOOLER@PROD> set lines 200
SECOOLER@PROD> set autotrace traceonly explain;
SECOOLER@PROD> 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
從執(zhí)行計劃上可以看到此時該SQL使用到了索引，沒有進行全表掃面。
2）強制SQL使用ontline中記錄的執(zhí)行計劃
（1）設(shè)置會話使用category為CATEGORY_T的outline
SECOOLER@PROD> alter session set use_stored_outlines=CATEGORY_T;
Session altered.
（2）再次查看SQL語句的執(zhí)行計劃
SECOOLER@PROD> select * from t where OBJECT_ID=258;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 51 | 6528 | 19 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 51 | 6528 | 19 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=258)
Note
-----
- outline "T_OUTLN1" used for this statement
從最后的注釋上我們已經(jīng)可以看到，此時SQL語句執(zhí)行過程中使用的是outln中記錄的執(zhí)行計劃。SQL在獲取數(shù)據(jù)的時候走的是全表掃描。
3）消除ontline對SQL語句的影響
（1）第一種方法是調(diào)整use_stored_outlines參數(shù)為false
SECOOLER@PROD> alter session set use_stored_outlines=false;
Session altered.
SECOOLER@PROD> 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參數(shù)起作用的前提下停用具體的outline。
SECOOLER@PROD> alter session set use_stored_outlines=CATEGORY_T;
Session altered.
SECOOLER@PROD> select * from t where OBJECT_ID=258;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 51 | 6528 | 19 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 51 | 6528 | 19 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=258)
Note
-----
- outline "T_OUTLN1" used for this statement
SECOOLER@PROD> alter outline t_outln1 disable;
Outline altered.
SECOOLER@PROD> 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已經(jīng)被停用。
4.清除outline
我們在10g環(huán)境下可以使用dbms_outln.drop_by_cat完成清空具體category的目的。
SECOOLER@PROD> conn outln/outln
Connected.
OUTLN@PROD> 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.1')
IGNORE_OPTIM_EMBEDDED_HINTS
OUTLN@PROD> exec dbms_outln.drop_by_cat('CATEGORY_T');
PL/SQL procedure successfully completed.
OUTLN@PROD> select hint_text from ol$hints where ol_name = 'T_OUTLN1' order by hint#;
no rows selected
5.關(guān)于USE_STORED_OUTLINES參數(shù)的說明
USE_STORED_OUTLINES參數(shù)不像一般的參數(shù)可以在參數(shù)文件中進行設(shè)定，但我們可以使用常規(guī)的方法對其進行修改。
ALTER SESSION SET USE_STORED_OUTLINES = TRUE | FALSE | category;
ALTER SYSTEM SET USE_STORED_OUTLINES = TRUE | FALSE | category;
6.小結(jié)
通過文中的描述，大家應(yīng)該對Oracle Outline技術(shù)有一個比較詳細(xì)的了解。在CBO優(yōu)化模式下，很有可能遇到執(zhí)行計劃不穩(wěn)定及不準(zhǔn)確的情況。在這種場景下我們可以考慮使用這項技術(shù)暫時規(guī)避執(zhí)行計劃變化帶來的性能問題。
Good luck.
secooler
10.09.08
-- The End --
主要使用場景如下：
①短時間內(nèi)無法完成SQL的優(yōu)化任務(wù)，此時可以使用outline暫時鎖定SQL執(zhí)行計劃；
②在CBO優(yōu)化模式下，當(dāng)統(tǒng)計信息出現(xiàn)問題時，會導(dǎo)致執(zhí)行計劃出現(xiàn)異常變化，此時可以使用outline暫時調(diào)整SQL執(zhí)行計劃；
③由于數(shù)據(jù)庫的bug導(dǎo)致SQL的執(zhí)行計劃出現(xiàn)異常，使用outline鎖定執(zhí)行計劃。
記錄一下關(guān)于outline的使用方法，供參考。
1.初始化環(huán)境
1）創(chuàng)建用戶secooler，并授予適當(dāng)權(quán)限，注意，用戶需要具有create any outline權(quán)限
SYS@PROD> create user secooler identified by secooler;
User created.
SYS@PROD> grant connect,resource to secooler;
Grant succeeded.
SYS@PROD> grant create any outline,alter any outline to secooler;
Grant succeeded.
2）在secooler用戶中創(chuàng)建表T
SYS@PROD> conn secooler/secooler
Connected.
SECOOLER@PROD> create table t as select * from all_objects;
Table created.
SECOOLER@PROD> select count(*) from t;
COUNT(*)
----------
4448
2.創(chuàng)建outline
1）解鎖outln用戶
SECOOLER@PROD> conn / as sysdba
Connected.
SYS@PROD> alter user outln identified by outln account unlock;
User altered.
2）創(chuàng)建一個outline，取名叫做t_outln1，指定它的category名字為CATEGORY_T
SYS@PROD> conn secooler/secooler
Connected.
SECOOLER@PROD> 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中便會記錄與此次操作的相關(guān)信息。執(zhí)行計劃會記錄在OL$HINTS中。
SECOOLER@PROD> conn outln/outln
Connected.
OUTLN@PROD> 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.1')
IGNORE_OPTIM_EMBEDDED_HINTS
4）比對一下對應(yīng)的執(zhí)行計劃
OUTLN@PROD> conn secooler/secooler
Connected.
SECOOLER@PROD> set autotrace traceonly explain;
SECOOLER@PROD> select * from t where OBJECT_ID=258;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 128 | 19 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 128 | 19 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=258)
Note
-----
- dynamic sampling used for this statement
這里記錄了一個全表掃描的執(zhí)行計劃。
5）關(guān)于創(chuàng)建的outline基本信息也可以通過dba_outlines視圖進行查詢
SECOOLER@PROD> conn / as sysdba
Connected.
SYS@PROD> 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）為了對比，我們創(chuàng)建索引，改變SQL語句的執(zhí)行計劃
（1）在T表的X字段創(chuàng)建索引
SYS@PROD> conn secooler/secooler
Connected.
SECOOLER@PROD> create index i_t on t(object_id);
Index created.
（2）查看此時SQL的執(zhí)行計劃
SECOOLER@PROD> set lines 200
SECOOLER@PROD> set autotrace traceonly explain;
SECOOLER@PROD> 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
從執(zhí)行計劃上可以看到此時該SQL使用到了索引，沒有進行全表掃面。
2）強制SQL使用ontline中記錄的執(zhí)行計劃
（1）設(shè)置會話使用category為CATEGORY_T的outline
SECOOLER@PROD> alter session set use_stored_outlines=CATEGORY_T;
Session altered.
（2）再次查看SQL語句的執(zhí)行計劃
SECOOLER@PROD> select * from t where OBJECT_ID=258;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 51 | 6528 | 19 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 51 | 6528 | 19 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=258)
Note
-----
- outline "T_OUTLN1" used for this statement
從最后的注釋上我們已經(jīng)可以看到，此時SQL語句執(zhí)行過程中使用的是outln中記錄的執(zhí)行計劃。SQL在獲取數(shù)據(jù)的時候走的是全表掃描。
3）消除ontline對SQL語句的影響
（1）第一種方法是調(diào)整use_stored_outlines參數(shù)為false
SECOOLER@PROD> alter session set use_stored_outlines=false;
Session altered.
SECOOLER@PROD> 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參數(shù)起作用的前提下停用具體的outline。
SECOOLER@PROD> alter session set use_stored_outlines=CATEGORY_T;
Session altered.
SECOOLER@PROD> select * from t where OBJECT_ID=258;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 51 | 6528 | 19 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 51 | 6528 | 19 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=258)
Note
-----
- outline "T_OUTLN1" used for this statement
SECOOLER@PROD> alter outline t_outln1 disable;
Outline altered.
SECOOLER@PROD> 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已經(jīng)被停用。
4.清除outline
我們在10g環(huán)境下可以使用dbms_outln.drop_by_cat完成清空具體category的目的。
SECOOLER@PROD> conn outln/outln
Connected.
OUTLN@PROD> 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.1')
IGNORE_OPTIM_EMBEDDED_HINTS
OUTLN@PROD> exec dbms_outln.drop_by_cat('CATEGORY_T');
PL/SQL procedure successfully completed.
OUTLN@PROD> select hint_text from ol$hints where ol_name = 'T_OUTLN1' order by hint#;
no rows selected
5.關(guān)于USE_STORED_OUTLINES參數(shù)的說明
USE_STORED_OUTLINES參數(shù)不像一般的參數(shù)可以在參數(shù)文件中進行設(shè)定，但我們可以使用常規(guī)的方法對其進行修改。
ALTER SESSION SET USE_STORED_OUTLINES = TRUE | FALSE | category;
ALTER SYSTEM SET USE_STORED_OUTLINES = TRUE | FALSE | category;
6.小結(jié)
通過文中的描述，大家應(yīng)該對Oracle Outline技術(shù)有一個比較詳細(xì)的了解。在CBO優(yōu)化模式下，很有可能遇到執(zhí)行計劃不穩(wěn)定及不準(zhǔn)確的情況。在這種場景下我們可以考慮使用這項技術(shù)暫時規(guī)避執(zhí)行計劃變化帶來的性能問題。
Good luck.
secooler
10.09.08
-- The End --
文章題目：【OUTLINE】使用OracleOutline技術(shù)暫時鎖定SQL的執(zhí)行計劃
文章URL：http://chinadenli.net/article16/gigcdg.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站建設(shè)、自適應(yīng)網(wǎng)站、靜態(tài)網(wǎng)站、域名注冊、網(wǎng)站導(dǎo)航、定制開發(fā)
聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)
- 爆紅的ChatGPT，誰會丟掉飯碗？ 2023-02-20
- 火爆的ChatGPT，來聊聊它的熱門話題 2023-02-20
- ChatGPT的發(fā)展歷程 2023-02-20
- ChatGPT的應(yīng)用ChatGPT對社會的利弊影響 2023-02-20
- ChatGPT是什么 2023-02-20
- 馬云回國，首談ChatGPT。又是新一個風(fēng)口？ 2023-05-28
- ChatGPT是什么?ChatGPT是聊天機器人嗎? 2023-05-05
- 怎樣利用chatGPT快速賺錢？ 2023-05-05