轉：Oracle Outline的使用及注意事項

概述
Oracle Outline是用來保持SQL執行計劃（execution plan）的一個工具。我們可以透過outline工具防止SQL執行計劃在資料庫環境變更（如統計資訊，部分引數等）而引起變化。
Outline的主要使用在以下情況：
1.
為避免在升級後某些sql出現嚴重效能下降而且在短時間內不能最佳化的情況，
我們可以使用outline的功能將原生產庫中的sql執行計劃實施在新的資料庫上。
2.
為避免SQL的執行計劃在統計資料不準確的情況（如未能及時收集表或索引的統計資訊）下導致變化從而引起的效能降低。
3.
避免大規模分佈實施的應用出現資料庫版本、配置等區別引起的最佳化器產生不同的執行計劃。
4.
某些Bug引起最佳化器生成較差的執行計劃。在bug修復前我們可以使用outline來強制SQL的執行計劃的正確。
Outline的機制是將所需要的執行計劃的hint儲存在outline的表中。當執行SQL時，Oracle會與outline中的SQL比較，如果該SQL有儲存的outline，則透過儲存的hint生成執行計劃。
Outline的使用注意事項
Outline的使用需要注意以下事項。
1．
Outln使用者是一個非常重要的系統使用者，其重要性跟sys，system一樣。在任何情況下都不建議使用者刪除outln，否則會引起資料庫錯誤。
2．
最佳化器透過Outline生成執行計劃前提是outline內所有hint都有效的。如：索引沒有建立的前提下，索引的hint是失效的，導致該SQL的outline計劃不會被使用。
3．
引數Cursor_sharing=force時不能使用outline。
4．
literial sql的共享程度不高，Outline針對繫結變數的sql較好。針對literial sql的情況，需要每條sql都生成outline。
5．
建立outline需要有create any outline的許可權。
6．
要注意從CBO的角度來看，資料庫表和索引的統計資訊是隨著資料量的變化而不斷改變的。固定的執行計劃在某些時段並不一定是最優的執行計劃。所以outline的使用是要根據具體情況來決定的。
Outline使用舉例
本文舉例說明如何使用outline，並且將outline的內容從8i遷移到10g的資料庫上使用。
操作步驟以scott使用者為例說明。
8i，10g中在scott使用者下建立測試表以說明outline的使用.
Login as scott
Create table t_test（col1 varchar2(2)）;
1．
確定8i生產庫的db，listener處於關閉的狀態。
2．
啟動8i生產庫instance.
3．
8i庫使用system使用者登陸，賦create any outline許可權給sql執行使用者。
Grant create any outline to scott;
4．
8i庫使用scott使用者登陸。
Create outline t_ol1 for category special on select * from t_test where col1=’00’;
T_ol1àoutline name
(注意每個outline都需要使用唯一的名字，不能重複)
Specialàoutline所屬的類（category）
Select * from t_test where col1=’00’;à需要儲存outline的sql
5．
10g，8i庫Unlock並修改outlin使用者口令。注意，outln使用者的口令可以修改但是outln使用者不能刪除。
Alter user outln identified by outln account unlock;
6．
在8i庫使用outln使用者，匯出outline資料。
Exp outln/outln tables=ol/$ ol/$hints file=ol.dmp log=ol_exp.log
將export的資料複製到10g庫所在機器
7．
在10g庫使用outln使用者匯入outline資料
imp outln/outln file=ol.dmp ignore=y log=ol_imp.log
8．
在10g庫使用sys使用者更新ouline的signature
connect sys/manager
exec dbms_outln.update_signatures;
啟用stored outline
alter system set use_stored_outlines=special;
à指定outline category
9．
檢測outline是否被使用
connect scott/tiger
create index I_test on t_test (col1);
à建立索引，以改變執行計劃
explain plan for select * from t_test where col1=’00’;
@?/rdbms/admin/utlxplp
PLAN_TABLE_OUTPUT
Plan hash value: 4036493941
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | select STATEMENT | | 1 | 3 | 1200 (4) | 00:00:17 |
|*1 |TABLE ACCESS FULL | T_TEST | 1 | 3 | 1200 (4) | 00:00:17 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("COL1"='00')
Note
-----
- outline "OL1" used for this statement
à
注意執行計劃指出online已經使用
17 rows selected.
說明outline已經啟用。
如果沒有outline的情況下應該使用索引,執行計劃如下。
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 614253159
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | select STATEMENT | | 1 | 3 | 3 (0) | 00:00:01 |
|* 1 | INDEX RANGE SCAN| I_TEST | 1 | 3 | 3 (0) | 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("COL1"='00')
Outline維護
停止db使用outline功能：
alter system set use_stored_outlines=false;
disable/enable具體outline：
alter outline ol_name disable;
alter outline ol_name enable;
刪除outline category：
9i, 10g: exec dbms_outln.drop_by_cat(‘category_name’);
8i: exec outln_pkg.drop_by_cat(‘category_name’);
outline相關檢視
dba_outlines
檢查outline是否存在
select
name, category, owner from dba_outlines;
dba_outline_hints
該檢視列出outline的hints內容