關於Oracle Outline使用

關於Oracle Outline使用

一、基本概述

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固定執行計畫時，一般都是選用此種方法
• 後面有一個簡單樣本，可以加深理解。

更多詳情見請繼續閱讀下一頁的精彩內容：

1 2 3 下一頁