sql_plan_baseline

https://blog.csdn.net/u010719917/article/details/52002679

1、查看錯誤的SQL 所執行 的執行計劃，確認是否需要使用基線控制執行計劃
（前提，我們無法直接修改SQL，否則直接通過SQL改寫方式即可）

2、打開基線捕獲參數  alter session set optimizer_capture_sql_plan_baselines=TRUE  （關閉FALSE）

執行 SQL   執行2次，捕獲執行計劃，然后關閉alter session set optimizer_capture_sql_plan_baselines=FALSE；

查看基線視圖select * from dba_sql_plan_baselines 進行確認；

定制 正確的 SQL 計劃，強制加HINT ，然后執行生成新的SQL_ID ，新的執行計劃PLAN_ID

select * from table(dbms_xplan.display_cursor) 獲取SQL_PLAN ID和SQL_ID 

導入SQL基線到基線基表中（數據字典）  
declare  
 k1 pls_integer;
begin  
 k1:= DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
 sql_id=>'b3h69kwfphm3j',（新SQL的ID）
 sql_handle=>'SQL_7313a4bc2778b751',  
 plan_hash_value=>'2949544139');  （新的SQL計劃）
end;  
刪除原來SQL基線
declare  
 k1 pls_integer;    
 begin  
 k1:=DBMS_SPM.drop_SQL_PLAN_BASELINE(  
 sql_handle=>'SQL_7313a4bc2778b751',  
  plan_name=>'SQL_PLAN_764x4rhmrjdujd8a279cc');   
end;  
執行原來的SQL 進行驗證，是否優化器已經采用基線； 同理前臺的SQL 在執行過程中執行了新的執行計劃； 調整基線保留時間和基線空間占用率select * from dba_sql_management_config

-----------------------------------------------------

創建基線的幾種方式

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;

4.
    execute dbms_sqltune.create_sql_plan_baseline(task_name => 'TASK_4929', owner_name => 'HBJZT', plan_hash_value => 333597355);

四、基線的幾種狀態
 
一個SQL語句對應的基線，我將它們歸納為三種狀態
1.accepted（可接受），只有這種狀態的基線，優化器才會考慮此基線中的執行計劃
2.no-accepted（不可接受），這種狀態的基線，優化器在SQL語句解析期間不會考慮。這種狀態的基線必須通過演化和驗證通過后，轉變為accepted狀態后，才會被優化器考慮使用
3.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包來修改基線的一些屬性，主要有如下幾個屬性
1.ENABLED ：設置該屬性的值為NO告訴Oracle 11g臨時禁用某個計劃，一個SQL計劃必須同時標記為ENABLED和ACCEPTED，否則CBO將忽略它
2.FIXED：設置為YES，那個計劃將是優化器唯一的選擇[最高優先級]，即使如果某個計劃可能擁有更低的成本。這讓DBA可以撤銷SMB的默認行為，對于轉換一個存儲概要進入一穩定的SQL計劃基線特別有用，注意當一個新計劃被添加到被標記為FIXED的SQL計劃基線，該新計劃不能被利用除非它申明為FIXED狀態
3.AUTOPURG：設置這個屬性的值為NO告訴Oracle 11g無限期保留它，從而不用擔心SMB的自動清除機制
4.plan_name : 改變SQL plan 名字
5.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,'hh34: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,'hh34: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,'hh34: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
 k1 pls_integer;
 begin
 k1 := DBMS_SPM.drop_sql_plan_baseline ( sql_handle=>'SYS_SQL_11bcd50cd51504e9',plan_name=>'SQL_PLAN_13g6p1maja17934f41c8d');
 end;
 /
 
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行。