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=>'b3h59kwfphm3j',（新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,'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
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行。