一、基礎概念 Oracle 11g開始，提供了一種新的固定執行計劃的方法，即SQL plan baseline，中文名SQL執行計劃基線（簡稱基線），可以認為是OUTLINE（大綱）或者SQL PROFILE的改進版本，基本上它的主要作用可以歸納為如下兩個： 1、穩定給定SQL語句的執行計劃，防止執行環境或對象統計信息等等因子的改變對SQL語句的執行計劃產生影響！ 2、減少數據庫中出現SQL語句性能退化的概率，理論上不允許一條語句切換到一個比已經執行過的執行計劃慢很多的新的執行計劃上！ 注意： 1、從Oracle的發展角度來看，估計這種方法是Oracle發展和改進的方向，如今outline已經被廢棄，sql profile估計在後續的發行版本中也難有改進，因此，對於從11g開始接觸Oracle的朋友來說，一定要對sql計劃基線有所了解，因為這是以後的主流！ 2、SQL執行計劃基線保存在數據字典中，查詢優化器會自動判斷使用他們。
二、工作機制 從Oracle 11g開始，由於基線的存在，一條語句的解析過程大概如下： SQL語句被硬解析後，CBO（優化器）會產生很多個的執行計劃，CBO從中選擇一個成本最低執行計劃。基於SQL語句的文本形成一個哈希值（signature)，通過這個哈希值來檢查數據字典中是否存在同樣的基線。如果基線存在，優化器會對剛剛產生的執行計劃和保存在SQL plan baseline中的執行計劃進行比較。如果基線中有與CBO剛產生的執行計劃的匹配的SQL執行計劃存在，並且被標記為可接受（‘accepted’），則這個CBO生成的執行計劃被啟用。如果基線中沒有匹配的SQ執行計劃存在，CBO評估基線中被標記為‘accepted’的的多個執行計劃，並選擇其中cost最低的執行計劃。（注意，一個語句的基線可以有多個執行計劃被保存，這是與其他Outline和SQL profiel都不同的地方）如果剛剛硬解析過程中CBO選擇的執行計劃比保存在基線中的執行計劃COST都低，這個新生成的執行計劃被標記為‘not-accepted’並保存在基線中。直到這個執行計劃被演化且驗證後才會被考慮使用，即標記為accepted(演化和驗證，可以簡單理解為Oracle確認這個執行計劃可以帶來更好的性能)。 Oracle 就是通過上面這種方式來確保SQL語句的性能不會退化（即第一部分中我歸納的第二個主要作用），稱為“執行計劃保守選擇策略”
三、基線的一些特點 簡單歸納如下幾個 通過OPTIMIZER_USE_SQL_PLAN_BASELINE來控制Oracle是否使用基線，默認值為TRUE，即會自動使用基線。11g中默認是不會自動創建基線與OUTLINE和SQL Profile不同，基線中不存在分類的概念與OUTLINE和SQL Profile不同，每個SQL語句可以有多個基線。Oracle根據制定的規則來判斷具體是否哪個基線基線針對RAC中所有的實例都生效基線有兩個表示，一個為sql_handle，可以理解為表示語句文本的唯一標識，一個為sql_plan_name可以理解為執行計劃的唯一標識不能像sql profile一樣通過force_matching屬性將字面值不一樣的SQL語句使用一個基線應用多個語句。 三、創建基線的幾種方式 1、自動捕獲基線，通過將optimizer_cature_sql_plan_baselines設置為true，優化器為重復執行兩次以上的SQL語句生成並保存基線（可以系統級或會話級修改） 2、從SQL調優集合中加載，通過使用包dbms_spm.load_plans_from_sqlset來從SQL調優集合中加載基線 DECLARE l_plans_loaded PLS_INTEGER; BEGIN l_plans_loaded := DBMS_SPM.load_plans_from_sqlset( sqlset_name => 'my_sqlset'); END; / 3、從庫緩存中加載，通過包dbms_spm.load_plans_from_cursor_cache函數為一條已經在游標緩存中的語句創建基線 DECLARE l_plans_loaded PLS_INTEGER; BEGIN l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(sql_id => '1fkh93md0802n',plan_hash_value=>null); END; / 備注：可以有多種方式加載，例如可以根據sql文本進行模糊匹配、SQL語句解析的用戶名等等方式，具體見文檔
四、基線的幾種狀態 一個SQL語句對應的基線，我將它們歸納為三種狀態 accepted（可接受），只有這種狀態的基線，優化器才會考慮此基線中的執行計劃no-accepted（不可接受），這種狀態的基線，優化器在SQL語句解析期間不會考慮。這種狀態的基線必須通過演化和驗證通過後，轉變為accepted狀態後，才會被優化器考慮使用fixed為yes（固定），這種狀態的基線固有最高優先級！比其他兩類基線都要優先考慮
五、查看基線 1、基本視圖：dba_sql_plan_baselines、dba_sql_management_config 2、底層視圖：sqlobj$data 、 sqlobj$ （保存具體的hint），如下查看基線中保存的執行計劃語句： select extractvalue(value(d), '/hint') as outline_hints from xmltable('/outline_data/hint' passing ( select xmltype(comp_data) as xmlval from sqlobj$data sod, sqlobj$ so where so.signature = sod.signature and so.plan_id = sod.plan_id and comp_data is not null and name like '&baseline_plan_name' ) ) d; 3、通過函數來查看基線的詳細信息： select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SYS_SQL_11bcd50cd51504e9',plan_name=>'SQL_PLAN_13g6p1maja1790cce5f0e'));
六、演化基線 為了驗證基線中一個處於不可接受狀態的執行計劃是否比一個處於可接受狀態的執行計劃具有更高的效率，必須通過演化來驗證，需要讓優化器以不同的執行計劃來執行這條SQL語句，觀察不可接受狀態的執行計劃基線是否會帶來更好的性能，如果性能確實更高，這個不可接受狀態的基線將會轉換為可接受狀態。演化的方式有兩種： 1、手工執行運行 SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_xxxxxxxxxxxxx') From dual; 還有time_limit/verify/commit幾個參數，可以參考文檔 2、調優包實現基線的自動演化，可以理解為，啟動一個調度任務，周期性的檢查是否有不可接受狀態的基線可以被演化
七、修改基線 可以通過dbms_spm.alter_sql_plan_baseline包來修改基線的一些屬性，主要有如下幾個屬性 ENABLED ：設置該屬性的值為NO告訴Oracle 11g臨時禁用某個計劃，一個SQL計劃必須同時標記為ENABLED和ACCEPTED，否則CBO將忽略它FIXED：設置為YES，那個計劃將是優化器唯一的選擇[最高優先級]，即使如果某個計劃可能擁有更低的成本。這讓DBA可以撤銷SMB的默認行為，對於轉換一個存儲概要進入一穩定的SQL計劃基線特別有用，注意當一個新計劃被添加到被標記為FIXED的SQL計劃基線，該新計劃不能被利用除非它申明為FIXED狀態AUTOPURG：設置這個屬性的值為NO告訴Oracle 11g無限期保留它，從而不用擔心SMB的自動清除機制plan_name : 改變SQL plan 名字description : 改變SQL plan描述 語法： SET SERVEROUTPUT ON DECLARE v_text PLS_INTEGER; BEGIN v_text := DBMS_SPM.alter_sql_plan_baseline(sql_handle => 'SYS_SQL_xxxxxx',plan_name => 'SYS_SQL_PLAN_xxxxxxxxx', attribute_name => 'fixed',attribute_value => 'YES'); DBMS_OUTPUT.put_line('Plans Altered: ' || v_text ); END; /
八、遷移基線 dbms_spm提供了多個過程來在數據庫之間遷移SQL計劃基線 create_stgtab_baseline創建一個計劃基線保存表pack_stgtab_baseline將基線從數據字典復制到第一步的表中unpack_stgtab_baseline將基線從保存表中復制到遷移數據庫的數據字典中 大概過程如下： 1、創建一張保存數據字典中基線表內容的用戶表 exec dbms_spm.create_stgtab_baseline(table_name => 'BASELINE_TEST',table_owner => 'SCOTT',tablespace_name =>''); 2、將數據字典中基線表的內容 插入到 第一步創建的用戶表中 exec :i := dbms_spm.pack_stgtab_baseline(table_name => 'BASELINE_TEST', table_owner => 'SCOTT'); 備注：可以支持多種方式插入,例如包含特定字符的SQL相關的基線，sql_handle來精確識別一個基線，具體見文檔 3、通過遷移工具遷移用戶表 exp/imp or expdp/impdp 4、將遷移過來的用戶表中保存的基線內容 插入到當前庫的數據字典中，從而實現遷移 exec :i := dbms_spm.unpack_stgtab_baseline(table_name => 'BASELINE_TEST',table_owner => 'SCOTT'); 備注：可以支持多種方式，與步驟2一樣，具體見文檔
九、刪除基線 可以通過dbms_SPM.drop_sql_plan_baseline包來手工刪除數據字典裡的基線為使用的基線，fixed為no的基線，將在一定的保留期後自動刪除(可查看dba_sql_management_config視圖） 手工刪除方法如下 SET SERVEROUTPUT ON DECLARE v_text PLS_INTEGER; BEGIN v_text := DBMS_SPM.drop_sql_plan_baseline(sql_handle => 'SYS_SQL_7b76323ad90440b9',plan_name => NULL); DBMS_OUTPUT.put_line(v_text); END; /
十、將一個SQL語句固定為我們期望的執行計劃 我一般通過如下幾步實現（僅供參考） 1、為這個SQL語句創建基線 2、給這個SQL語句添加hint賴宇星，確保SQL語句添加hint後的執行計劃與我們期望一樣 3、將第2步產生的執行計劃，添加到第一步創建的基線中（注意，前面已經說過，一個SQL語句可以有多個基線！） 4、刪除基線中第1步創建的那個執行計劃（這樣，我們就可以確保基線中只有我們期望的執行計劃，即保存第2步SQL語句的執行計劃） 5、驗證是否生效後續有示例，加深理解！
十一、示例（將一個SQL語句固定為我們期望的執行計劃） 首先運行兩個結構相同的語句，下面的實驗通過SQL計劃基線，將一個語句的執行計劃通過另一個語句的執行計劃來固定 SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines; 未選定行 SQL> alter system flush shared_pool; 系統已更改。 SQL> select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=711; ID NAME TYPE ---------- ------------------------------ --------------- 711 I_STREAMS_PROCESS_PARAMS1 INDEX SQL> select /* outlinetest3 */ /*+ index(dh_stat) */ * from dh_stat where id=711; ID NAME TYPE ---------- ------------------------------ --------------- 711 I_STREAMS_PROCESS_PARAMS1 INDEX
SQL> select sql_text,sql_id,hash_value,child_number,plan_hash_value,to_char(LAST_ACTIVE_TIME,'hh24:mi:ss') time 2 from v$sql a where sql_text like '%outlinetest%' and sql_text not like '%v$sql%'; SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE TIME ------------------------------------------------------- ------------- ---------- ------------ --------------- -------- select /* outlinetest2 */ /*+ full(dh_stat) */ * from d 4vaj9fgjysy9c 3823925548 0 1845196118 12:27:31 h_stat where id=711 select /* outlinetest3 */ /*+ index(dh_stat) */ * from fm35jcmypb3qu 4250242778 0 2780970545 12:27:41 dh_stat where id=711
SQL> select * from table(dbms_xplan.display_cursor('4vaj9fgjysy9c','','')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 4vaj9fgjysy9c, child number 0 ------------------------------------- select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=711 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"=711) 已選擇19行。
SQL> select * from table(dbms_xplan.display_cursor('fm35jcmypb3qu','','')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID fm35jcmypb3qu, child number 0 ------------------------------------- select /* outlinetest3 */ /*+ index(dh_stat) */ * from dh_stat where id=711 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"=711) 已選擇20行。
SQL> DECLARE 2 k1 pls_integer; 3 begin 4 k1 := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE ( 5 sql_id=>'4vaj9fgjysy9c', 6 plan_hash_value=>1845196118 7 ); 8 end; 9 / PL/SQL 過程已成功完成。
SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME SQL_TEXT ACC ------------------------------ ------------------------------ ------------------------------------------------------- --- SYS_SQL_11bcd50cd51504e9 SQL_PLAN_13g6p1maja17934f41c8d select /* outlinetest2 */ /*+ full(dh_stat) */ * from d YES h_sta
剛生產sql plan baseline的時候，第一次查詢，無法找到執行計劃，直到第二次執行的時候，才能看到，如下 SQL> select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=711; ID NAME TYPE ---------- ------------------------------ --------------- 711 I_STREAMS_PROCESS_PARAMS1 INDEX SQL> select * from table(dbms_xplan.display_cursor('4vaj9fgjysy9c','','')); PLAN_TABLE_OUTPUT --------------------------------------------------------- SQL_ID: 4vaj9fgjysy9c cannot be found SQL> select sql_text,sql_id,hash_value,child_number,plan_hash_value,to_char(LAST_ACTIVE_TIME,'hh24:mi:ss') time 2 from v$sql a where sql_text like '%outlinetest%' and sql_text not like '%v$sql%'; SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE TIME ------------------------------------------------------- ------------- ---------- ------------ --------------- -------- select /* outlinetest3 */ /*+ index(dh_stat) */ * from fm35jcmypb3qu 4250242778 0 2780970545 12:27:41 dh_stat where id=711
SQL> select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=711; ID NAME TYPE ---------- ------------------------------ --------------- 711 I_STREAMS_PROCESS_PARAMS1 INDEX SQL> select sql_text,sql_id,hash_value,child_number,plan_hash_value,to_char(LAST_ACTIVE_TIME,'hh24:mi:ss') time 2 from v$sql a where sql_text like '%outlinetest%' and sql_text not like '%v$sql%';
SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE TIME ------------------------------------------------------- ------------- ---------- ------------ --------------- -------- select /* outlinetest2 */ /*+ full(dh_stat) */ * from d 4vaj9fgjysy9c 3823925548 0 1845196118 12:30:54 h_stat where id=711 select /* outlinetest3 */ /*+ index(dh_stat) */ * from fm35jcmypb3qu 4250242778 0 2780970545 12:27:41 dh_stat where id=711
SQL> select * from table(dbms_xplan.display_cursor('4vaj9fgjysy9c','','')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 4vaj9fgjysy9c, child number 0 ------------------------------------- select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=711
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"=711) Note ----- - SQL plan baseline SQL_PLAN_13g6p1maja17934f41c8d used for this statement 已選擇23行。
將符合我們預期的執行計劃的加載到第一次生成的sql baseline中！ SQL> DECLARE 2 k1 pls_integer; 3 begin 4 k1 := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE ( 5 sql_id=>'fm35jcmypb3qu', 6 plan_hash_value=>2780970545,sql_handle=>'SYS_SQL_11bcd50cd51504e9' 7 ); 8 end; 9 /
PL/SQL 過程已成功完成。
可以看到，SYS_SQL_11bcd50cd51504e9下目前有兩個plan_name SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME SQL_TEXT ACC ------------------------------ ------------------------------ ------------------------------------------------------- --- SYS_SQL_11bcd50cd51504e9 SQL_PLAN_13g6p1maja1790cce5f0e select /* outlinetest2 */ /*+ full(dh_stat) */ * from d YES h_sta
SYS_SQL_11bcd50cd51504e9 SQL_PLAN_13g6p1maja17934f41c8d select /* outlinetest2 */ /*+ full(dh_stat) */ * from d YES h_sta 刪除第一個plan_name，即將我們不需要的執行計劃版本去除掉！ SQL> DECLARE 2 k1 pls_integer; 3 begin 4 k1 := DBMS_SPM.drop_sql_plan_baseline ( sql_handle=>'SYS_SQL_11bcd50cd51504e9',plan_name=>'SQL_PLAN_13g6p1maja17934f41c8d'); 5 end; 6 /
PL/SQL 過程已成功完成。
通過下面的一部分測試，我們可以看到，新的SQL計劃基線已經正常生效，及時語句中包含full提示，執行計劃也走索引定位數據 SQL> select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=711; ID NAME TYPE ---------- ------------------------------ --------------- 711 I_STREAMS_PROCESS_PARAMS1 INDEX
SQL> select * from table(dbms_xplan.display_cursor('4vaj9fgjysy9c','','')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 4vaj9fgjysy9c, child number 1 ------------------------------------- select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=711 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"=711) Note ----- - SQL plan baseline SQL_PLAN_13g6p1maja1790cce5f0e used for this statement 已選擇24行。
可以通過dba_sql_plan_baselines來顯示可用的SQL計劃基線的一般信息，也可以通過如下這種方式顯示執行SQL計劃基線的詳細信息！ select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SYS_SQL_11bcd50cd51504e9',plan_name=>'SQL_PLAN_13g6p1maja1790cce5f0e'));
PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------------------- SQL handle: SYS_SQL_11bcd50cd51504e9 SQL text: select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=711 --------------------------------------------------------------------------------
-------------------------------------------------------------------------------- Plan name: SQL_PLAN_13g6p1maja1790cce5f0e Plan id: 214851342 Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD --------------------------------------------------------------------------------
Plan hash value: 2780970545
--------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 | | 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"=711) 已選擇26行。
查看SQL計劃基線中保存的hint提示集合 SQL> conn /as sysdba 已連接。 SQL> select 2 extractvalue(value(d), '/hint') as outline_hints 3 from 4 xmltable('/outline_data/hint' 5 passing ( 6 select 7 xmltype(comp_data) as xmlval 8 from 9 sqlobj$data sod, sqlobj$ so 10 where so.signature = sod.signature 11 and so.plan_id = sod.plan_id 12 and comp_data is not null 13 and name like '&baseline_plan_name' 14 ) 15 ) d; 輸入 baseline_plan_name 的值: SQL_PLAN_13g6p1maja1790cce5f0e 原值 13: and name like '&baseline_plan_name' 新值 13: and name like 'SQL_PLAN_13g6p1maja1790cce5f0e'
OUTLINE_HINTS ----------------------------------------------------------------------------------------------------------------------------------------------- IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.1') DB_VERSION('11.2.0.1') ALL_ROWS OUTLINE_LEAF(@"SEL$1") INDEX_RS_ASC(@"SEL$1" "DH_STAT"@"SEL$1" ("DH_STAT"."ID"))
已選擇6行。
