> Oracle SPM(Sql Plan Management)  --by Firsouler 2021/03/25

#### 概述
   Oracle11g之後推出的，SPM是一種主動穩定執行計劃的手段，能夠保證只有被驗證過的執行計劃才會被啟用。

相關引數

``` sql
-- SPM預設開啟,自動捕獲預設關閉
SQL> show parameter sql_plan

NAME				     TYPE			      VALUE
------------------------------------ -------------------------------- -----------
optimizer_capture_sql_plan_baselines boolean			      FALSE
optimizer_use_sql_plan_baselines     boolean			      TRUE

--檢視基線檢視
set lines 200
select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines
where sql_text like '%';

--根據sql_handle檢視執行計劃
select * from table(dbms_xplan.display_sql_plan_baseline('SQL_4e255b34b4cf2fd9'));
```

如果一個SQL有多個執行計劃，該SQL會有多個SPB，可以從DBA_SQL_PLAN_BASELINES中檢視所有的SPB。其所對應的執行計劃是否被啟用，只有ENABLED和ACCEPTED的值均為"YES"才行。如果有多個，選擇成本較少的。

Oracle有兩種方式可以產生SQL Plan Baseline

- 自動捕獲
- 手工生成/批次匯入

#### 自動捕獲

引數 optimizer_capture_sql_plan_baselines用於控制是否開啟自動捕獲SQL Plan Baselines。 自動捕獲，相同sql且執行計劃相同執行兩次才會放入SPB中，且ENABLED/ACCEPTED均為"YES",如果再次執行該sql，執行計劃變了，新執行計劃會增加到dba_sql_plan_baselines中，但ACCEPTED的值是"NO"，因此該執行計劃不會被啟用。

修改SPB執行計劃屬性，來幫助sql指定執行計劃

``` sql
--檢視是否加入spb
select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines
where sql_text like 'select object_id%';

--修改，先將no置為yes
exec :temp :=dbms_spm.evlove_sql_plan_baseline(sql_handle=>'SQL_SQL_ajlx809xxxx',plan_name=>'',verify=>'NO',commit=>'YES');
--將原有YES改為no
exec :temp :=dbms_spm.evlove_sql_plan_baseline(sql_handle=>'SQL_SQL_ajlx809xxxx',plan_name=>'',attribute_name='ENABLED',attribute_value=>'NO');
```

#### 手動生成SPB

> 具體參考最後例子

``` sql
--手動生成初始執行計劃所對應的SPB
exec :temp := dbms_spm.load_plans_from_cursor_cache
(
    sql_id=>'原sql',
    plan_hash_value=>''
);

--透過hint，多次執行，找出理想執行計劃，加入到spb
exec :temp :=dbms_spm.load_plans_from_cursor_cache
(
    sql_id=>'新sql_id',
    plan_hash_value=>'新hash',
    sql_handle=>'原sql鎖產生的spb中的sql_handle'
);

--刪除初始的原SQL的spb
exec :temp :=dbms_spm.drop_sql_plan_baseline
(
    sql_handle=>'',
    plan_name=>''
);
```

#### 批次匯入

**Oralce提供六種方式把計劃匯入到sql plan baseline中:**
- 1.從 SQL Tuning Set STS 匯入：DBMS_SPM.LOAD_PLANS_FROM_SQLSET
- 2.從Cursor Cache中裝載：DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE
- 3.從Stored Outlines中匯入： DBMS_SPM.MIGRATE_STORED_OUTLINE
- 4.從記憶體中存在的計劃中匯入：DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE
- 5.從staging table表中匯入：dbms_spm.create_stgtab_baseline
- 6.遷移，從其他資料庫遷入，具體參考

eg:

``` sql
--從SQL Tuning Set中裝載:
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(sqlset_name => 'tset1');
END;
/
--從Cursor Cache中裝載
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '7qqnad1j615m7');
END;
/
```

#### 遷移基線

``` sql
--1.建立儲存表
exec :temp := dbms_spm.create_stgtab_baseline(table_name => 'BASELINE_TABLE',table_owner => 'MYTEST',tablespace_name =>'');

--2、將基線載入到表 "BASELINE_TABLE"
exec :temp := dbms_spm.pack_stgtab_baseline(table_name => 'BASELINE_TABLE', table_owner => 'MYTEST');

--3、匯出匯入
expdp/impdp

--4、新庫插入基線
exec :temp := dbms_spm.unpack_stgtab_baseline(table_name => 'BASELINE_TABLE',table_owner => 'MYTEST');

```

#### 修改原始基線 例子
``` sql
--1.建立環境
SQL> create table c2 as select * from dba_objects;

Table created.

SQL> create index idx_c2 on c2(object_id);

Index created.
--收集統計資訊
 exec dbms_stats.gather_table_stats(ownname=>'MYTEST',tabname=>'C2',
 estimate_percent=>100,cascade=>TRUE);

--2.執行sql 走全表掃描
select /*+ no_index(c2 idx_c2) */ object_name,object_id from mytest.c2 where object_id=4;

--檢視基線 因為沒開啟自動捕獲，所以沒有加入到spb
set lines 200
select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines
where sql_text like 'select /*+ no_index(%';

--3.手動加入
set serverouput on
var temp number
exec :temp := dbms_spm.load_plans_from_cursor_cache(sql_id=>'02dp0ka5ysk0h', plan_hash_value=>3416629809);

--檢視
SQL_HANDLE		       PLAN_NAME		      ORIGIN	     ENA ACC SQL_TEXT
------------------------------ ------------------------------ -------------- --- --- --------------------------------------------------------------------------------
SQL_4e255b34b4cf2fd9	       SQL_PLAN_4w9av6kucybyt2c7305c9 MANUAL-LOAD    YES YES select /*+ no_index(c2 idx_c2) */ object_name,object_id from mytest.c2 where obj

--4.走索引方式，檢視執行計劃
alter session set statistics_level=all;
select /*+ index(c2 idx_c2) */ object_name,object_id from mytest.c2 where object_id=4;
select * from table(dbms_xplan.display_cursor(null,null,'allstats +alias +outline'));

--5.更改
var temp number
exec :temp := dbms_spm.load_plans_from_cursor_cache(sql_id=>'65khvmhg2wb6n',plan_hash_value=>1328050837,sql_handle=>'SQL_4e255b34b4cf2fd9');

--再次查詢
SQL_HANDLE		       PLAN_NAME		      ORIGIN	     ENA ACC SQL_TEXT
------------------------------ ------------------------------ -------------- --- --- --------------------------------------------------------------------------------
SQL_4e255b34b4cf2fd9	       SQL_PLAN_4w9av6kucybyt2c7305c9 MANUAL-LOAD    YES YES select /*+ no_index(c2 idx_c2) */ object_name,object_id from mytest.c2 where obj
SQL_4e255b34b4cf2fd9	       SQL_PLAN_4w9av6kucybytbdbf52e0 MANUAL-LOAD    YES YES select /*+ no_index(c2 idx_c2) */ object_name,object_id from mytest.c2 where obj

--6.刪除舊的
var temp number
exec :temp :=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_4e255b34b4cf2fd9',plan_name=>'SQL_PLAN_4w9av6kucybyt2c7305c9');

--7.再次執行步驟2，雖然加入了hint，但會選擇走索引，如下所示
SQL> select * from table(dbms_xplan.display_sql_plan_baseline('SQL_4e255b34b4cf2fd9'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------
SQL handle: SQL_4e255b34b4cf2fd9
SQL text: select /*+ no_index(c2 idx_c2) */ object_name,object_id from mytest.c2
	  where object_id=:"SYS_B_0"
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_4w9av6kucybytbdbf52e0	  Plan id: 3183432416
Enabled: YES	 Fixed: NO	Accepted: YES	  Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 1328050837

--------------------------------------------------------------------------------------
| Id  | Operation		    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |	     |	   1 |	  30 |	   2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| C2     |	   1 |	  30 |	   2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN	    | IDX_C2 |	   1 |	     |	   1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID"=TO_NUMBER(:SYS_B_0))

26 rows selected.

```

> 注意 PLAN_HASH_VALUE/HASH_VALUE區別，前者為執行計劃HASH值，後者是SQL遊標HASH值

#### 參考
- https://blog.csdn.net/oradh/article/details/30456915
- http://blog.itpub.net/16648/viewspace-967230/
- 《基於Oracle的SQL最佳化》

【SPM】Oracle計劃管理器SPM介紹及用例

相關文章