關於Oracle Outline使用

pxbibm發表於2015-04-30
Oracle
一、基本概述
Oracle Outline，中文也稱為儲存大綱，是最早的基於提示來控制SQL執行計劃的機制，也是9i以及之前版本唯一可以用來穩定和控制SQL執行計劃的工具。
outline是一個hints（提示）的集合，更具體的講，outline可以鎖定一個給定SQL的執行計劃，保持其執行計劃穩定，不管資料庫環境如何變更（如統計資訊，部分引數等）
注意：
  1. 從10g以後，oracle連續釋出了sql profile和sql baseline來實現SQL執行計劃的控制，並且outline這個工具基本已經被Oracle廢棄並且不在維護，但是不管怎麼說，在10g以及11g版本都還是可以使用，而且這個特性也一直使用的很好。
  2. 10g以後建議使用sql profile或者sql baseline
  3. 由於目前outline現在已經很少使用，此文也儘量介紹實用的一部分
二、執行機制
Outline將執行計劃的hint集合儲存在outline的表中（資料字典）。當執行SQL解析時，Oracle會與outline中的SQL比較，如果該SQL有儲存的outline，則透過儲存的hint集合生成指定執行計劃。
注意：
  1. SQL解析時，使用SQL文字卻匹配資料字典outline儲存的文字，此處匹配的方式為去掉SQL空格，忽略SQL大小寫區別後，進行的比較。
  2. 例如,select * from dual 和SELECT * FROM dual這兩個語句將使用同樣的outline。
三、使用場景
  1. 為避免在升級後某些SQL出現嚴重效能下降而且在短時間內不能最佳化的情況，我們可以使用outline的功能將原生產庫中的sql執行計劃實施在新的資料庫上。
  2. 為避免SQL的執行計劃在統計資料不準確的情況（如未能及時收集表或索引的統計資訊）下導致變化從而引起的效能降低。
  3. 為避免容易因為Bind Peeking導致SQL執行計劃變差從而引起的效能降低。
  4. 避免大規模分佈實施的應用出現資料庫版本、配置等區別引起的最佳化器產生不同的執行計劃。
  5. 某些Bug引起最佳化器生成較差的執行計劃。在bug修復前我們可以使用outline來強制SQL的執行計劃的正確。
  6. 早期最佳化器版本從rule轉換為cbo模式時，過渡期間用來維護業務穩定（執行計劃穩定）
注意
任何一個資料庫中，大部分的SQL語句執行計劃應該是透過最佳化器自動生成，並且高效執行，而只有極少部分，需要透過各種工具（outine、sql profile）來鎖定執行計劃
四、注意事項
  1. outline存在在outln使用者中，Outln使用者是一個非常重要的系統使用者，其重要性跟sys，system一樣。在任何情況下都不建議使用者刪除outln，否則會引起資料庫錯誤。
  2. 最佳化器透過Outline生成執行計劃前提是outline內所有hint都有效的。
  3. 只有設定use_stored_outlines引數後才能啟用outline。
  4. 使用字面值的sql的共享程度不高（沒有使用繫結變數），Outline針對繫結變數的sql較好。針對使用字面值的sql的情況，需要每條sql都生成outline。
  5. 建立outline需要create any outline or execute_catelog_role許可權 。
  6. 要注意從CBO的角度來看，資料庫表和索引的統計資訊是隨著資料量的變化而不斷改變的。固定的執行計劃在某些時段並不一定是最優的執行計劃。所以outline的使用是要根據具體情況來決定的。
  7. 第一次應用Outline （alter system ）這個操作是會產生Library cache pin的，需謹慎。
  8. 10.2.0.4 outline bug 6455659
  9. use_stored_outlines引數重啟後失效，需要重新設定
  10. 當outline依賴的物件被刪除時，outline並不會自動刪除
五、outline相關的檢視
  • 兩個基本檢視：dba_outlines,dba_outline_hints
  • 三個底層表：ol$、ol$hints、ol$nodes
六、使用outline
一、建立outline
建立outline的方法有三種，下面我們一一簡單介紹
1、給會話甚至整個系統執行的每一條SQL語句都建立outline，可以設定如下引數，分別針對會話級和系統級
ALTER SYSTEM SET create_stored_outlines=TRUE;
ALTER SESSION SET create_stored_outlines=TRUE;
注意：基本上沒有任何一個資料庫會這麼做，因此這種方式我們不做測試；
2、手工透過CREATE OUTLINE方式來建立給定SQL語句的outline，如下
CREATE or replace OUTLINE outline_dh_test FOR CATEGORY test on select * from dh_stat where id=11;
or
CREATE or replace OUTLINE outline_dh_test1 on select * from dh_stat where id=11;
示例：
 
SQL> CREATE or replace OUTLINE outline_dh_test FOR CATEGORY test on select * from dh_stat where id=11;
Outline created.
SQL> set linesize 200 pagesize 999
SQL> set long 30
SQL> set long 50
SQL> select name,owner,category,used,sql_text from dba_outlines;
NAME OWNER CATEGORY USED SQL_TEXT
------------------------------ ------------------------------ ------------------------------ ------
OUTLINE_DH_TEST DBMON TEST UNUSED select * from dh_stat where id=11
SQL> select name,hint from dba_outline_hints;
NAME HINT
------------------------------ --------------------------------------------------
OUTLINE_DH_TEST FULL(@"SEL$1" "DH_STAT"@"SEL$1")
OUTLINE_DH_TEST OUTLINE_LEAF(@"SEL$1")
OUTLINE_DH_TEST ALL_ROWS
OUTLINE_DH_TEST OPT_PARAM('_optimizer_use_feedback' 'false')
OUTLINE_DH_TEST OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'fa
OUTLINE_DH_TEST OPT_PARAM('_optimizer_extended_cursor_sharing_rel'
OUTLINE_DH_TEST OPT_PARAM('_bloom_pruning_enabled' 'false')
OUTLINE_DH_TEST OPT_PARAM('_gby_hash_aggregation_enabled' 'false')
OUTLINE_DH_TEST OPT_PARAM('_optimizer_extended_cursor_sharing' 'no
OUTLINE_DH_TEST OPT_PARAM('_bloom_filter_enabled' 'false')
OUTLINE_DH_TEST OPT_PARAM('_optimizer_null_aware_antijoin' 'false'
OUTLINE_DH_TEST OPT_PARAM('_optim_peek_user_binds' 'false')
OUTLINE_DH_TEST DB_VERSION('11.2.0.1')
OUTLINE_DH_TEST OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
OUTLINE_DH_TEST IGNORE_OPTIM_EMBEDDED_HINTS
15 rows selected.
注意：
  • 不指定outline類別是，預設為default，而且此處建立時，不能指定為default類別（會報錯）。
  • 這個方法不是很方便，因為必須將整個SQL文字作為語句的一部分，可能導致語句無法共享等問題，因此很少使用這種方法
 
3、從10g起，可以透過引用共享池中已經存在的SQL語句來建立outline
exec DBMS_OUTLN.create_outline(hash_value=>1752921103,child_number => 0,category=>'test');
注意
  • 這種方法不能指定outline的具體名字，由系統自動生成，可以透過alter outline SYS_OUTLINE_14061114223605901 rename to dh_test2修改
  • 不指定類別的話預設為default，而且此處建立時，不能指定為default類別（會報錯）。
  • 我們使用outline固定執行計劃時，一般都是選用此種方法
  • 後面有一個簡單示例，可以加深理解。


二、修改outline
1、更改outline名稱
alter outline SYS_OUTLINE_14061114223605901 rename to dh_test2
2、更改outline類別
exec dbms_outln.update_by_cat(oldcat=>'DDD',newcat=>'DEFAULT');
3、重建outline
alter outline DH_TEST2 rebuild;
三、啟用outline
Oracle優化器只會考慮啟用的outline，這意味著如果建立的outline沒有被啟用，Oracle根本不會使用它，一個outline必須滿足如下兩個條件才能被啟用：
1、outline必須處於可用狀態（建立時預設就是可用，一般不會有問題），修改方法,alter outlineDH_TEST2 disable;
2、類別必須透過初始化引數use_stored_ouotlines在會話或者系統級啟用，可以設定為“TRUE/FALSE/類別名”三種，其中TRUE表示啟用default類別
alter session set use_stored_outlines=TRUE;
四、移動outline
只能透過將資料字典中儲存的hint資料複製到另一個資料庫的資料字典，除此之外沒有其它辦法。不過還好這個方法也非常簡單，因為outline相關的hint資料都儲存在outln使用者下的三張表中：ol$、ol$hints、ol$nodes。可以用下面的命令來匯入和匯出可用的outline
exp tables=(outln.ol$,outln.ol$hints,outln.ol$nodes) file=xxx.dmp
五、編輯outline，後續會提供兩種方法
當最佳化器無法為給定的SQL生成高效的執行計劃時，可以透過編輯outline來實現，可以理解為修改outline中的hint
1、使用原語句建Outline
2、檢視Outline執行計劃
Select HINT_TEXT,USER_TABLE_NAME,JOIN_PRED,cardinality,bytes,cost
from OUTLN.OL$HINTS
where ol_name = 'OLXXXXX_ORIG'
3、在SQL上加hint，執行語句（注意語句結構不能改變，不能增加或刪除查詢塊什麼的）
4、檢視加上hint的SQL語句，執行計劃是否與我們期望的一致
5、得到期望的執行計劃後，將兩個outline的執行計劃進行互換，即完成outline的編輯！
UPDATE OUTLN.OL$HINTS
SET OL_NAME = DECODE(OL_NAME,
'OLXXXXX_MOD',
'OLXXXXX_ORIG',
'OLXXXXX_ORIG',
'OLXXXXX_MOD')
WHERE OL_NAME IN ('OLXXXXX_MOD', 'OLXXXXX_ORIG');
6、啟用outline
六、刪除outline
可以用如下命令分別刪除指定的outline或者某個類別下的所有outline
drop outline dh_test1;
dbms_outln.drop_by_cat(cat=>'test');
 
示例一（引用使用共享池中的SQL來建立outline）
SQL> create table dh_stat as select rownum id ,object_name name ,object_type type from dba_objects;
SQL> create index ind_1 on dh_stat(id) compute statistics;
SQL> alter system flush shared_pool;
系統已更改。
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'dbmon',TABNAME=>'dh_stat',ESTIMATE_PERCENT=>30,METHOD_OPT=>'FOR ALL COLUMNS SIZE 1',NO_INVALIDATE=>FALSE,CASCADE=>TRUE,DEGREE => 1);
PL/SQL 過程已成功完成。
SQL> col name format a15
SQL> col name format a30
SQL> col sql_text format a55
給執行的語句新增一個獨特的註釋，方便後續查詢語句
SQL> select /* outlinetest1 */ * from dh_stat where id=771;
ID NAME TYPE
---------- ------------------------------ ---------------
771 APPLY$_CONF_HDLR_COLUMNS_UNQ1 INDEX
SQL> select sql_text,sql_id,hash_value,child_number from v$sql a where sql_text like '%outlinetest1%' and sql_text not like '%v$sql%';
SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER
------------------------------------------------------- ------------- ---------- ------------
select /* outlinetest1 */ * from dh_stat where id=771 053nzgm4f6rdr 3370343863 0
SQL> select * from table(dbms_xplan.display_cursor('053nzgm4f6rdr','',''));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 053nzgm4f6rdr, child number 0
-------------------------------------
select /* outlinetest1 */ * from dh_stat where id=771
Plan hash value: 2780970545
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| DH_STAT | 1 | 38 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_1 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=771)
已選擇19行。

SQL> exec DBMS_OUTLN.create_outline(hash_value=>3370343863,child_number => 0,category=>'TEST');
PL/SQL 過程已成功完成。
SQL> col category format a10
SQL> select name,category,used,sql_text from dba_outlines;

NAME CATEGORY USED SQL_TEXT
------------------------------ ---------- ------ -------------------------------------------------------
SYS_OUTLINE_14061209594622403 TEST UNUSED select /* outlinetest1 */ * from dh_stat whereid=771
可以看到，outline確實已經生成
SQL> select /* outlinetest1 */ * from dh_stat where id=771;

ID NAME TYPE
---------- ------------------------------ ---------------
771 APPLY$_CONF_HDLR_COLUMNS_UNQ1 INDEX
SQL> select name,category,used,sql_text from dba_outlines;

NAME CATEGORY USED SQL_TEXT
------------------------------ ---------- ------ -------------------------------------------------------
SYS_OUTLINE_14061209594622403 TEST UNUSED select /* outlinetest1 */ * from dh_stat whereid=771
此處outline的USED狀態沒有改變，因為我們沒有啟用TEST類別的outline，再次申明，outline必須透過use_stored_outlines引數啟用後，最佳化器才會使用outline！
SQL> select * from table(dbms_xplan.display_cursor('053nzgm4f6rdr','',''));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 053nzgm4f6rdr, child number 0
-------------------------------------
select /* outlinetest1 */ * from dh_stat where id=771
Plan hash value: 2780970545
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| DH_STAT | 1 | 38 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_1 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=771)
已選擇19行。
下面這一步啟用TEST類別的OUTLINE，也可以在系統級啟用OUTLINE
SQL> alter session set use_stored_outlines=TEST;
會話已更改。
SQL> select /* outlinetest1 */ * from dh_stat where id=771;
ID NAME TYPE
---------- ------------------------------ ---------------
771 APPLY$_CONF_HDLR_COLUMNS_UNQ1 INDEX
SQL> select name,category,used,sql_text from dba_outlines;

NAME CATEGORY USED SQL_TEXT
------------------------------ ---------- ------ -------------------------------------------------------
SYS_OUTLINE_14061209594622403 TEST USED select /* outlinetest1 */ * from dh_stat where id=771
再次查詢OUTLINE的使用狀態，可以看到，我們剛新建的outline確實已經被使用過，透過下面的執行計劃，更加可以證實這一點
SQL> select * from table(dbms_xplan.display_cursor('053nzgm4f6rdr','',''));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 053nzgm4f6rdr, child number 0
-------------------------------------
select /* outlinetest1 */ * from dh_stat where id=771
Plan hash value: 2780970545
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| DH_STAT | 1 | 38 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_1 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=771)
SQL_ID 053nzgm4f6rdr, child number 1
-------------------------------------
select /* outlinetest1 */ * from dh_stat where id=771
Plan hash value: 2780970545

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| DH_STAT | 1 | 38 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_1 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=771)
Note
-----
- outline "SYS_OUTLINE_14061209594622403" used for this statement
已選擇42行。

 

示例二、編輯outline的兩種方法示例
1、第一種是直接修改字典裡的hint，這裡就不測試了。
2、透過更換兩個outline名稱，來完成修改
--這個實驗緊接著“示例一（引用使用共享池中的SQL來建立outline）"
SQL> exec dbms_outln.clear_used(name=>'SYS_OUTLINE_14061209594622403');
PL/SQL 過程已成功完成。
---透過上面的過程，將outline的使用記錄清理掉，USED狀態再次轉變為UNSED，方便我們測試
SQL> select name,category,used,sql_text from dba_outlines;
NAME CATEGORY USED SQL_TEXT
------------------------------ ---------- ------ -------------------------------------------------------
SYS_OUTLINE_14061209594622403 TEST UNUSED select /* outlinetest1 */ * from dh_stat whereid=771
SQL> select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=771;
ID NAME TYPE
---------- ------------------------------ ---------------
771 APPLY$_CONF_HDLR_COLUMNS_UNQ1 INDEX
SQL> select sql_text,sql_id,hash_value,child_number from v$sql a where sql_text like '%outlinetest2%' and sql_text not like '%v$sql%';
SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER
------------------------------------------------------- ------------- ---------- ------------
select /* outlinetest2 */ /*+ full(dh_stat) */ * from d 3fcq9c8xu4a92 1000483106 0
h_stat where id=771
SQL> alter session set use_stored_outlines=true;
會話已更改。
SQL> select * from table(dbms_xplan.display_cursor('3fcq9c8xu4a92','',''));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3fcq9c8xu4a92, child number 0
-------------------------------------
select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where
id=771
Plan hash value: 1845196118
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 124 (100)| |
|* 1 | TABLE ACCESS FULL| DH_STAT | 1 | 38 | 124 (1)| 00:00:02 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=771)
已選擇19行。

SQL> exec DBMS_OUTLN.create_outline(hash_value=>1000483106,child_number => 0,category=>'TEST');
PL/SQL 過程已成功完成。
SQL> select name,category,used,sql_text from dba_outlines;

NAME CATEGORY USED SQL_TEXT
------------------------------ ---------- ------ -------------------------------------------------------
SYS_OUTLINE_14061210153067004 TEST UNUSED select /* outlinetest2 */ /*+ full(dh_stat) */ * from d
h_stat where id=771
SYS_OUTLINE_14061209594622403 TEST UNUSED select /* outlinetest1 */ * from dh_stat whereid=771
可以看到，我們新建的outline，確實已經成功
SQL> alter session set use_stored_outlines=TEST;
會話已更改。
SQL> select /* outlinetest1 */ * from dh_stat where id=771;
ID NAME TYPE
---------- ------------------------------ ---------------
771 APPLY$_CONF_HDLR_COLUMNS_UNQ1 INDEX
SQL> select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=771;
ID NAME TYPE
---------- ------------------------------ ---------------
771 APPLY$_CONF_HDLR_COLUMNS_UNQ1 INDEX
SQL> select name,category,used,sql_text from dba_outlines;
NAME CATEGORY USED SQL_TEXT
------------------------------ ---------- ------ -------------------------------------------------------
SYS_OUTLINE_14061210153067004 TEST USED select /* outlinetest2 */ /*+ full(dh_stat) */ * from d
h_stat where id=771
SYS_OUTLINE_14061209594622403 TEST USED select /* outlinetest1 */ * from dh_stat where id=771
可以看到，兩個outline都已經標記為已經使用過
SQL> select * from table(dbms_xplan.display_cursor('3fcq9c8xu4a92','',''));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3fcq9c8xu4a92, child number 0
-------------------------------------
select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where
id=771
Plan hash value: 1845196118
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 124 (100)| |
|* 1 | TABLE ACCESS FULL| DH_STAT | 1 | 38 | 124 (1)| 00:00:02 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=771)
Note
-----
- outline "SYS_OUTLINE_14061210153067004" used for this statement
已選擇23行。

SQL> select * from table(dbms_xplan.display_cursor('053nzgm4f6rdr','',''));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
....此處為了排版，省略了一個child number 0 的執行計劃！........
SQL_ID 053nzgm4f6rdr, child number 1
-------------------------------------
select /* outlinetest1 */ * from dh_stat where id=771
Plan hash value: 2780970545
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| DH_STAT | 1 | 38 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_1 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=771)
Note
-----
- outline "SYS_OUTLINE_14061209594622403" used for this statement
已選擇42行。
SQL> alter session set use_stored_outlines=TRUE;
會話已更改。
透過如下方式，我們調換兩個outline裡面的hints
SQL> UPDATE OUTLN.OL$HINTS
2 SET OL_NAME = DECODE(OL_NAME,
3 'SYS_OUTLINE_14061210153067004',
4 'SYS_OUTLINE_14061209594622403',
5 'SYS_OUTLINE_14061209594622403',
6 'SYS_OUTLINE_14061210153067004')
7 WHERE OL_NAME IN ('SYS_OUTLINE_14061210153067004', 'SYS_OUTLINE_14061209594622403');
已更新12行。
SQL> commit;
提交完成。
SQL> col hint_text format a50
SQL> select hint#,hint_text from outln.ol$hints a where ol_name='SYS_OUTLINE_14061209594622403';
HINT# HINT_TEXT
---------- --------------------------------------------------
1 FULL(@"SEL$1" "DH_STAT"@"SEL$1")
2 OUTLINE_LEAF(@"SEL$1")
3 ALL_ROWS
4 DB_VERSION('11.2.0.1')
5 OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
6 IGNORE_OPTIM_EMBEDDED_HINTS
已選擇6行。
果然和我們預期的一樣，outline裡面的執行計劃已經調換
SQL> select /* outlinetest1 */ * from dh_stat where id=771;

ID NAME TYPE
---------- ------------------------------ ---------------
771 APPLY$_CONF_HDLR_COLUMNS_UNQ1 INDEX
SQL> select * from table(dbms_xplan.display_cursor('053nzgm4f6rdr','',''));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 053nzgm4f6rdr, child number 0
-------------------------------------
select /* outlinetest1 */ * from dh_stat where id=771
Plan hash value: 2780970545
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| DH_STAT | 1 | 38 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_1 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=771)
SQL_ID 053nzgm4f6rdr, child number 1
-------------------------------------
select /* outlinetest1 */ * from dh_stat where id=771
Plan hash value: 2780970545
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| DH_STAT | 1 | 38 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_1 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=771)
Note
-----
- outline "SYS_OUTLINE_14061209594622403" used for this statement
SQL_ID 053nzgm4f6rdr, child number 2
-------------------------------------
select /* outlinetest1 */ * from dh_stat where id=771

Plan hash value: 2780970545

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| DH_STAT | 1 | 38 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_1 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=771)
已選擇61行。
啟用outline
SQL> alter session set use_stored_outlines=TEST;
會話已更改。

SQL> exec dbms_outln.clear_used(name=>'SYS_OUTLINE_14061209594622403');
PL/SQL 過程已成功完成。

SQL> select name,category,used,sql_text from dba_outlines;

NAME CATEGORY USED SQL_TEXT
------------------------------ ---------- ------ -------------------------------------------------------
SYS_OUTLINE_14061210153067004 TEST USED select /* outlinetest2 */ /*+ full(dh_stat) */ * from d
h_stat where id=771
SYS_OUTLINE_14061209594622403 TEST UNUSED select /* outlinetest1 */ * from dh_stat whereid=771

SQL> select /* outlinetest1 */ * from dh_stat where id=771;

ID NAME TYPE
---------- ------------------------------ ---------------
771 APPLY$_CONF_HDLR_COLUMNS_UNQ1 INDEX

SQL> select name,category,used,sql_text from dba_outlines;

NAME CATEGORY USED SQL_TEXT
------------------------------ ---------- ------ -------------------------------------------------------
SYS_OUTLINE_14061210153067004 TEST USED select /* outlinetest2 */ /*+ full(dh_stat) */ * from d
h_stat where id=771
SYS_OUTLINE_14061209594622403 TEST USED select /* outlinetest1 */ * from dh_stat where id=771

SQL> select * from table(dbms_xplan.display_cursor('053nzgm4f6rdr','',''));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
SQL_ID 053nzgm4f6rdr, child number 0
-------------------------------------
select /* outlinetest1 */ * from dh_stat where id=771
Plan hash value: 2780970545
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| DH_STAT | 1 | 38 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_1 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=771)
SQL_ID 053nzgm4f6rdr, child number 1
-------------------------------------
select /* outlinetest1 */ * from dh_stat where id=771
Plan hash value: 2780970545
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| DH_STAT | 1 | 38 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_1 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=771)
Note
-----
- outline "SYS_OUTLINE_14061209594622403" used for this statement
SQL_ID 053nzgm4f6rdr, child number 2
-------------------------------------
select /* outlinetest1 */ * from dh_stat where id=771
Plan hash value: 2780970545
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| DH_STAT | 1 | 38 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_1 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=771)
SQL_ID 053nzgm4f6rdr, child number 3
-------------------------------------
select /* outlinetest1 */ * from dh_stat where id=771
Plan hash value: 2780970545
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| DH_STAT | 1 | 38 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_1 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=771)
Note
-----
- outline "SYS_OUTLINE_14061209594622403" used for this statement
已選擇84行。
可以看到，雖然OUTLINE已經被使用，但是並不是我們預期的那樣。想要得到我們預期，透過全表掃描來實現，我在測試中是透過下面的方法實現
SQL> alter session set use_stored_outlines=dd;
會話已更改。
SQL> exec dbms_outln.clear_used(name=>'SYS_OUTLINE_14061209594622403');
PL/SQL 過程已成功完成。
SQL> select /* outlinetest1 */ * from dh_stat where id=771;
ID NAME TYPE
---------- ------------------------------ ---------------
771 APPLY$_CONF_HDLR_COLUMNS_UNQ1 INDEX
SQL> select name,category,used,sql_text from dba_outlines;
 
NAME CATEGORY USED SQL_TEXT
------------------------------ ---------- ------ -------------------------------------------------------
SYS_OUTLINE_14061210153067004 TEST USED select /* outlinetest2 */ /*+ full(dh_stat) */ * from d
h_stat where id=771
SYS_OUTLINE_14061209594622403 TEST UNUSED select /* outlinetest1 */ * from dh_stat where id=771
SQL> alter session set use_stored_outlines=TRUE;
會話已更改。
SQL> select /* outlinetest1 */ * from dh_stat where id=771;
ID NAME TYPE
---------- ------------------------------ ---------------
771 APPLY$_CONF_HDLR_COLUMNS_UNQ1 INDEX
SQL> select name,category,used,sql_text from dba_outlines;
 
NAME CATEGORY USED SQL_TEXT
------------------------------ ---------- ------ -------------------------------------------------------
SYS_OUTLINE_14061210153067004 TEST USED select /* outlinetest2 */ /*+ full(dh_stat) */ * from d
h_stat where id=771
SYS_OUTLINE_14061209594622403 TEST UNUSED select /* outlinetest1 */ * from dh_stat where id=771
SQL> exec dbms_outln.update_by_cat(oldcat=>'TEST',newcat=>'DEFAULT');
PL/SQL 過程已成功完成。
SQL> select /* outlinetest1 */ * from dh_stat where id=771;
ID NAME TYPE
---------- ------------------------------ ---------------
771 APPLY$_CONF_HDLR_COLUMNS_UNQ1 INDEX
SQL> select name,category,used,sql_text from dba_outlines;
NAME CATEGORY USED SQL_TEXT
------------------------------ ---------- ------ -------------------------------------------------------
SYS_OUTLINE_14061210153067004 DEFAULT USED select /* outlinetest2 */ /*+ full(dh_stat) */ * from d
h_stat where id=771
SYS_OUTLINE_14061209594622403 DEFAULT USED select /* outlinetest1 */ * from dh_stat where id=771
SQL> select * from table(dbms_xplan.display_cursor('053nzgm4f6rdr','',''));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------
....為了排版，省略了前面的4個子執行計劃.........
SQL_ID 053nzgm4f6rdr, child number 4
-------------------------------------
select /* outlinetest1 */ * from dh_stat where id=771
Plan hash value: 1845196118
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 124 (100)| |
|* 1 | TABLE ACCESS FULL| DH_STAT | 1 | 38 | 124 (1)| 00:00:02 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
-------------------------------------------------
1 - filter("ID"=771)
Note
-----
- outline "SYS_OUTLINE_14061209594622403" used for this statement
已選擇102行。
可以看到，這次outline已經和我們預期的一樣生效，並且是透過全表掃描來實現
而且透過多次實驗證明，必須修改一下outline的類別名或者將語句刷出共享池才能實現，因此，我們先將outline建立為一個私有的類別，等驗證完畢且符合預期後，再正式釋出出來，這樣也不會需要修改資料庫當前已有引數。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12798004/viewspace-1613305/，如需轉載，請註明出處，否則將追究法律責任。

相關文章