使用SPM固定执行计划

• 礼包

技术使用SPM固定执行计划 使用SPM固定执行计划使用SPM固定执行计划
2 YEARS AGOORACLEVIEWED56TIMES.数据库在运行过程中，会由于各种原因的变化，存在执行计划不稳定的情况

使用SPM修复执行计划

使用SPM固定执行计划

两年前，我看了56次。

在数据库运行过程中，由于各种原因，执行计划会不稳定。Oracle 11g开始由SQL计划管理进行管理，以稳定地执行计划。

总体实施方案不稳定的原因有几个(包括但不限于):

数据倾斜

不准确的统计信息

数据库升级

1. 绑定变量带来的问题

从ORACLE 9i开始，引入了绑定窥视的新特性。在数据偏斜的绑定变量中，当Oracle第一次解析SQL时，会将变量的真实值代入生成一个执行计划，以后所有相同的绑定变量SQL都会采用这个执行计划。在大多数情况下，这个特性可以通过与绑定变量相结合来减少SQL的解析时间，但是对于数据倾斜的SQL，它可能会产生极其糟糕的执行计划。

11g之初引入自适应游标共享(ACS)，为一个绑定变量生成多个子执行计划，以减少数据倾斜对执行计划的影响，达到动态调整执行计划的目的。

1.1 绑定变量相关视图

在v$sql中新增了IS_BIND_SENSITIVE和IS_BIND_AWARE字段：

一个

2

三

选择是敏感的，是可识别绑定的，sql_id，子编号

来自v$sql

其中sql _ id=' 1

如果游标中有绑定变量，数据库会根据传入的实际值判断不同的值是否会影响执行计划。如果是，光标将被标记为绑定敏感。v$sql视图中IS_BIND_SENSITIVE的值是Y.

SQL执行几次后，数据库会根据传入的实际值决定是否修改执行计划。如果是这样的话，那么游标是Bind-Aware的，并且IS_BIND_AWARE在v$sql视图中也被标记为Y。

V $ SQL _ CS _选择性视图显示了不同值的选择性。V$SQL_CS_STATISTICS视图显示了标记为绑定敏感和绑定感知的游标的一些统计信息，例如内存读取、CPU时间等。

一个

2

三

四

五

六

七

八

九

选择子编号，

绑定集散列值，

偷看了，

处决，

已处理的行数，

buffer _ gets，

cpu时间

来自v$sql_cs_statistics

其中sql _ id=' 1

1.2 查询绑定变量值

从AWR向上看

一个

2

三

四

五

六

七

八

九

10

11

12

13

14

15

16

17

18

19

a30的列名

a30的列值_字符串

设置第200行页面尺寸9999

从DBA_HIST_SQLBIND中选择SNAP_ID、名称、数据类型_字符串、值_字符串、数据类型，其中sql_id='1 '

设置第200行页面尺寸9999

a30的列绑定1

a30的列bind2

a30的列bind3

a30的列绑定4

挑选

快照标识，SQL标识，

PLAN_HASH_VALUE,
dbms_sqltune.extract_bind(bind_data,1).value_string bind1,
dbms_sqltune.extract_bind(bind_data,2).value_string bind2,
dbms_sqltune.extract_bind(bind_data,3).value_string bind3,
dbms_sqltune.extract_bind(bind_data,4).value_string bind4
from dba_hist_sqlstat
where sql_id = '1'
order by snap_id;

  • 从cursor中查找
1
2
3
4
5
6
7
8
select ADDRESS
      ,HASH_VALUE
      ,CHILD_NUMBER
      ,name
      ,DATATYPE_STRING
      ,VALUE_STRING
      ,LAST_CAPTURED
from v$sql_bind_capture where sql_id ='1';

1.3 从共享内存中删除游标

  • 通过SQL ID查找SQL地址及hash值
1
2
3
4
--查找sql的address和hash_value
select address, hash_value, sql_text
from v$sqlarea
where sql_id='1';
  • 调用dbms_shared_pool清除游标

将产生的address及hash_value代入 1， 2, 其中，c代表需要purge的类型是cursor，r表示trigger,q表示sequence等.

1
 
exec dbms_shared_pool.purge('1,2','C');

2. 如何使用SPM管理执行计划

2.1 相关术语

  • SPM
    SPM是管理SQL执行计划的框架。其主要目的是防止由于执行计划改变而导致的SQL性能的退化。同时也提供了动态调整SQL执行计划的框架。

  • SQL Plan Baseline
    当一个新的执行计划产生时候，SPM不一定会立即启用它，只有确认这些执行计划不会带来性能下降或者能提升性能，SPM才会采用(Accepted),而这些accepted的执行计划则被成为baseline(以下简称基线)。

  • Plan Evolution
    把accepted的执行计划加入到基线的过程。

  • SQL Management Base(SMB)
    存储基线、执行计划历史等的数据字典。

2.2 工作原理

SPM通过两个动态初始化参数进行控制, 两个参数均为PDB级别可修改。

  • optimizer_capture_sql_plan_baselines
    自动识别重复的SQL语句，并且为这些语句生成基线(从另一个侧面说明，SPM需要结合绑定变量使用，如果不使用绑定变量，建议使用SQL profile+ force_match参数)。该参数默认为false。
  • optimizer_use_sql_plan_baselines
    启用或者关闭SMB中的基线，默认为true。当启用时，优化器会从SMB中查找相应的基线并且挑选cost最小的作为该SQL的执行计划;如果是新执行计划，数据库会自动把这些新计划以unaccepted状态加入到基线中。

其工作流程如下图所示：

如果存在基线，优化器根据新产生的执行计划是否在基线中而作出不同选择：

  • 如果新计划在基线中，则会执行此计划
  • 如果新计划不在基线中，优化器会把新产生的执行计划标记为accepted，且加入到plan history中，接下来优化器会根据以下情况作出不同选择：
    • 如果fixed plan存在于基线中，优化器会使用最低代价的fixed plan
    • 如果没有fixed plan，优化器挑选基线中代价最低的
    • 如果基线中不存在reproduced的执行计划，比如所有执行计划相关的索引都drop掉了，优化器会采用新产生的执行计划

2.3 创建基线

创建基线有以下几种方式：

  • 使用SQL Tuning Set(STS)
  • 从缓存/AWR中加载(new for 12.2)
  • 从其他库导出并导入
  • 自动收集(optimizer_capture_sql_plan_baselines=TRUE)

2.3.1 从缓存中进行加载

  • 游标中已经存在较优的执行计划:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
declare
 pls number;
begin
 pls := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id = '1',
 --plan_hash_value = 2,
 enabled = 'YES');
end;
/

set serveroutput on

var n number
begin
:n:=dbms_spm.load_plans_from_cursor_cache(sql_id='sql_id', plan_hash_value=plan_hash_value, fixed ='NO', enabled='YES');
end;
/

EXEC dbms_output.put_line('Number of plans loaded: ' || :n);
  • 通过filter筛选SQL
1
2
3
 
exec :pls := dbms_spm.load_plans_from_cursor_cache( -
           attribute_name = 'SQL_TEXT', -
           attribute_value = '1');

2.3.2 从AWR中加载

1
 
exec pls := dbms_spm.load_plans_from_awr(begin_snap = 1, end_snap = 2);

2.4 查询基线

通过以下视图查询基线状态:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
select sql_text, plan_name, enabled, accepted from dba_sql_plan_baselines;

--通过sql_id查找基线
col sql_handle for a30
col plan_name for a50
set line 200 pagesize 9999
select s.sql_id, s.plan_hash_value, b.sql_handle, b.plan_name,
b.signature, b.enabled, b.accepted, b.fixed, s.sql_text
from v$sqlarea s JOIN dba_sql_plan_baselines b
on (s.exact_matching_signature = b.signature) and s.sql_id = '1';

--查找仅来源于手动加载的基线
col sql_handle for a30
col plan_name for a30
col creator for a30
set line 200 pagesize 200
select sql_handle, plan_name, origin, enabled, accepted,
fixed,creator,optimizer_cost,sql_text
from dba_sql_plan_baselines where origin = 'MANUAL-LOAD';

dba_sql_plan_baselines中栏位的含义:
如果是Enabled=No或者是Reproduced=No, 则优化器不会考虑相关的执行计划。

可通过dbms_spm.alter_sql_plan_baseline去调整这些参数值。如:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
var temp varchar2(4000)
exec :temp := dbms_spm.alter_sql_plan_baseline(sql_handle='SQL_HANDLE',plan_name='SQL_PLAN',attribute_name='enabled',attribute_value='YES');

var pbsts varchar2(30);
exec :pbsts := dbms_spm.alter_sql_plan_baseline('SQL_HANDLE','SQL_PLAN','accepted','NO');

SET SERVEROUTPUT ON
DECLARE
  l_plans_altered  PLS_INTEGER;
BEGIN
  l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
    sql_handle      = 'SQL_HANDLE',
    plan_name       = 'SQL_PLAN',
    attribute_name  = 'fixed',
    attribute_value = 'YES');

  DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/

查询基线执行计划:

1
2
3
4
5
6
7
8
 
SELECT PLAN_TABLE_OUTPUT
FROM   V$SQL s, DBA_SQL_PLAN_BASELINES b,
       TABLE(
       DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(b.sql_handle,b.plan_name,'all')
       ) t
WHERE  s.EXACT_MATCHING_SIGNATURE=b.SIGNATURE
AND    b.PLAN_NAME=s.SQL_PLAN_BASELINE
AND    s.SQL_ID='SQL_ID';

2.5 发展基线

Plan Evolution(发展基线)就是优化器识别新执行计划(unaccepted)并加入到基线的过程。
创建发展基线的大致步骤（12c）：

  • Create Evolve Task (dbms_spm.create_evolve_task)
  • Execute Evolve Task (dbms_spm.execute_evolve_task)
  • Report Evolve Task (dbms_spm.report_evolve_task)
  • Accept Recommendation (dbms_spm.accept_sql_plan_baseline)

2.5.1 手工创建发展基线(12c)

  • 创建evolve任务
1
2
3
4
5
6
7
8
9
10
 
VARIABLE cnt NUMBER
VARIABLE tk_name VARCHAR2(50)
VARIABLE exe_name VARCHAR2(50)
VARIABLE evol_out CLOB

EXECUTE :tk_name := DBMS_SPM.CREATE_EVOLVE_TASK(
  sql_handle = 'SQL_HANDLE',
  plan_name  = 'SQL_PLAN');

SELECT :tk_name FROM DUAL;
  • 执行该任务
1
2
 
EXECUTE :exe_name :=DBMS_SPM.EXECUTE_EVOLVE_TASK(task_name=:tk_name);
SELECT :exe_name FROM DUAL;
  • 查看报告
1
2
 
EXECUTE :evol_out := DBMS_SPM.REPORT_EVOLVE_TASK( task_name=:tk_name, execution_name=:exe_name );
SELECT :evol_out FROM DUAL;
  • 实施推荐
1
 
EXECUTE :cnt := DBMS_SPM.IMPLEMENT_EVOLVE_TASK( task_name=:tk_name, execution_name=:exe_name );

2.5.2 手动发展基线(11g)

1
2
3
 
var report clob;
exec :report := dbms_spm.evolve_sql_plan_baseline('SQL_HANDLE');
print :report

2.5.3 自动发展基线(12c)

通过SYS_AUTO_SPM_EVOLVE_TASK可以在12c中自动发展基线，

查看当前自动任务配置信息：

1
2
3
4
5
6
7
8
 
COLUMN parameter_name FORMAT A25
COLUMN parameter_value FORMAT a25

SELECT parameter_name, parameter_value
FROM   dba_advisor_parameters
WHERE  task_name = 'SYS_AUTO_SPM_EVOLVE_TASK'
AND    parameter_value != 'UNUSED'
ORDER BY parameter_name;

关闭自动任务:

1
2
3
4
5
6
7
 
BEGIN
  DBMS_SPM.set_evolve_task_parameter(
    task_name = 'SYS_AUTO_SPM_EVOLVE_TASK',
    parameter = 'ACCEPT_PLANS',
    value     = 'FALSE');
END;
/

查看自动任务执行结果：

1
2
 
SET LONG 1000000 PAGESIZE 1000 LONGCHUNKSIZE 100 LINESIZE 100
SELECT DBMS_SPM.report_auto_evolve_task FROM  dual;

2.6 删除基线

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
 
--指定某一个baseline删除
dbms_spm.drop_sql_plan_baseline(sql_handle='SQL_HANDLE',plan_name='SQL_PLAN')

--根据sql handle删除
DECLARE
  v_dropped_plans number;
BEGIN
  v_dropped_plans := DBMS_SPM.DROP_SQL_PLAN_BASELINE (
     sql_handle = 'SQL_HANDLE'
);
  DBMS_OUTPUT.PUT_LINE('dropped ' || v_dropped_plans || ' plans');
END;
/

--或者指定plan name删除
SET SERVEROUTPUT ON
DECLARE
  l_plans_dropped  PLS_INTEGER;
BEGIN
  l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (
    sql_handle = NULL,
    plan_name  = 'SQL_PLAN');
  DBMS_OUTPUT.put_line(l_plans_dropped);
END;
/

2.7 管理SBM

  • 查询当前SBM配置
1
2
3
4
5
6
 
SELECT PARAMETER_NAME, PARAMETER_VALUE FROM DBA_SQL_MANAGEMENT_CONFIG;

PARAMETER_NAME					   PARAMETER_VALUE
-------------------------------------------------- ---------------
SPACE_BUDGET_PERCENT						10
PLAN_RETENTION_WEEKS						53

上述结果为默认配置，SBM可使用的空间上限为sysaux的10%，保留期限为53周。

  • 修改SBM配置
1
2
3
4
5
 
--修改空间限制为30%:
EXECUTE DBMS_SPM.CONFIGURE('space_budget_percent',30);

--修改SBM保留期限, 修改为105周，默认为53周
EXECUTE DBMS_SPM.CONFIGURE('plan_retention_weeks',105);

Reference:
SQL Plan Management with Oracle Database 12c Release 2
Managing SQL Plan Baselines
How to Load SQL Plans into SQL Plan Management (SPM) from the Automatic Workload Repository (AWR) (Doc ID 789888.1)
White Papers and Blog Entries for Oracle Optimizer (Doc ID 1337116.1)
How to Use SQL Plan Management (SPM) - Plan Stability Worked Example (Doc ID 456518.1)

EOF

内容来源网络，如有侵权，联系删除，本文地址：https://www.230890.com/zhan/103393.html

赞 (0)

相关推荐

  • 怎么进行Nginx配置文件引用

    技术怎么进行Nginx配置文件引用怎么进行Nginx配置文件引用，针对这个问题，这篇文章详细介绍了相对应的分析和解答，希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。Nginx配置文件有很多需要我们注意很多问

    攻略 2021年11月25日

  • html如何在文本上显示图片(html怎么在页面上显示图片)

    技术html中如何显示图片本篇内容介绍了“html中如何显示图片”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

    攻略 2021年12月23日

  • SQL server服务显示远程过程调用失败的解决方法

    技术SQL server服务显示远程过程调用失败的解决方法本篇文章给大家分享的是有关SQL server服务显示远程过程调用失败的解决方法，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不

    攻略 2021年11月30日

  • 鸡西有什么用？

    技术JixiPix有什么用这篇文章将为大家详细讲解有关JixiPix有什么用，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。JixiPix软件是专业照片效果和艺术软件开发商。有着悠久而丰

    攻略 2021年12月24日

  • MySQL如何快速查看原始SQL语句

    技术MySQL如何快速查看原始SQL语句这篇文章主要讲解了“MySQL如何快速查看原始SQL语句”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“MySQL如何快速查看原始S

    攻略 2021年11月9日

  • Spring整合Mybatis的方式

    技术Spring整合Mybatis的方式 Spring整合Mybatis的方式导入依赖的jar包dependencygroupIdjunit/groupIdartifactIdjunit/artifac

    礼包 2021年10月20日