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行。
新聞標題：sql_plan_baseline
瀏覽地址：http://m.newbst.com/article2/pepcoc.html
成都網站建設公司_創新互聯，為您提供App開發、網站制作、網站設計公司、面包屑導航、App設計、外貿建站
聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創新互聯
- 你適合做網賺嗎做網站也不是那么容易 2021-11-20
- 上海做網站：什么是網站的正面目標和負面目標？ 2016-09-05
- 做網站該選擇模板網站還是定制網站開發 2022-09-24
- 【網站建設】新手做網站推廣應該知道什么內容 2021-09-13
- 廣州做網站優化的6個技巧 2022-07-01
- 無錫網站建設公司解說個人做網站有什么影響嗎 2022-06-03
- 做網站優化時友情鏈接交換需要注意哪些方面？ 2013-08-15
- 成都做網站優化時都了解遵守什么規則_成都創新互聯 2021-08-24
- 企業為什么要做網站？網站對企業有什么優勢？ 2022-05-03
- 豐臺做網站,豐臺網站建設,豐臺網站制作公司 2021-02-03
- 公司做網站推廣的注意事項有哪些？ 2022-12-18
- 公司為什么要做網站? 2015-02-21