【SPM】Oracle计划管理器SPM介绍及用例

  • xysoul_云龙

    2021-03-26 09:27:49
  • Oracle
  • 原创

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

概述

Oracle11g之后推出的，SPM是一种主动稳定执行计划的手段，能够保证只有被验证过的执行计划才会被启用。

相关参数

-- 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指定执行计划

--查看是否加入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

具体参考最后例子

--手动生成初始执行计划所对应的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 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;
/

迁移基线

--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');

修改原始基线 例子

--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值

参考

请使用浏览器的分享功能分享到微信等