oracle++spm,【SPM】Oracle计划管理器SPM介绍及用例

> 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: NOAccepted: 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优化》