SQL Plan Management(SPM)in oracle 11g & 12c

本文主要介绍SPM的原理与运行机制,针对12c做一些补充记录,并添加一些11g和12c里面的知识点,以做备忘。

干预SQL执行计划的历史

SQL的执行效率,取决于它的执行计划是否高效。 优化器的算法是一个平衡,需要收集尽量少的信息,用尽量快的速度试图去得到一个最优的执行计划,这也决定了它不是万能的。 所以Oracle提供了一些辅助手段来“修复”优化器可能产生的错误,并不断改进这些方法。

  • Oracle 8: hint
  • Oracle 8i&9i: stored outline
  • Oracle 10g: sql profile
  • Oracle 11g: sql plan manangement 、adaptive cursor sharing
  • Oracle 12c: sql plan manangement 、adaptive cursor sharing、Adaptive Execution Plans

SQL Plan Management

SPM组成

SQL Plan Management(以下简称SPM)是一种优化器自动管理执行计划的预防机制,它确保数据库仅使用已知的、经过验证的执行计划。在Oracle 11g之前,执行计划一直是作为“运行时”生成的对象存在。虽然oracle提供了一些方法去指导它的生成,但Oracle一直没有试图去保存完整的执行计划。 从11g开始,执行计划就可以作为一类资源被保存下来,允许特定SQL语句只能选择“已知”的执行计划。

同其他方法相比,SPM更加的灵活。如我们所熟知的,一条带有绑定变量的SQL语句,最好的执行计划会根据绑定变量的值而不同,11g以前的方法都无法解决这个问题。在11g中,与adaptive cursor sharing配合,SPM允许你同时接受多个执行计划。执行时,根据不同的变量值,SPM会花费很少的运算从中选择一条最合适的。

SPM有以下三个主要组件:

  • Plan capture

    捕捉并存储与SQL执行计划相关的信息

  • Plan selection

    使用SQL plan baselines选择合适的执行计划以避免性能退化

  • Plan evolution

    向SQL plan baselines增加新的执行计划

SQL Management Base

SMB是数据字典的一部分,位于SYSAUX表空间,其中存储着statement logs, plan histories, SQL plan baselines, and SQL profiles等内容。

Plan History是优化器生成的所有执行计划的总称;SQL Plan Baseline是Plan History里那些被标记为“ACCEPTED”的执行计划的总称,称为SQL执行计划基线;SQL Statement Log是一系列的查询签名(signatures),用于在自动捕获执行计划时辨别重复执行的sql;关系如下图所示:

SQL Management Base

Plan capture(执行计划的捕获)

Automatic Initial Plan Capture(自动捕获)

开启自动捕获只需在初始化参数中将OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES设置为TRUE(默认为FALSE),开启之后数据库就会为每个重复执行的sql创建SQL Plan Baseline,甄别是否是重复执行的SQL就是利用的上文提到的SQL Statement Log,基线包括供优化器重新生成该执行计划的所有信息,例如SQL text, outline, bind variable values, and compilation environment,这个初始的执行计划会被自动标记为“accepted”,如果之后又有新的执行计划生成,那么该执行计划会被加入到Plan History但是会被标记为“unaccepted”。

Oracle Database 12c Release 2 增加了自动捕获时sql过滤的功能,利用DBMS_SPM.CONFIGURE存储过程可以创建automatic capture filter。因此,你可以设置仅仅捕获你想要捕获的SQL,可以从DBA_SQL_MANAGEMENT_CONFIG视图中查询当前的设置:

SPM CONFIG

自动捕获状态下执行计划的匹配算法如下:

  • 如果SQL plan baseline不存在,那么优化器会为该SQL创建SQL plan baseline和Plan History,并将捕获的plan标记为accepted,加入SQL plan baseline

  • 如果SQL plan baseline存在,那么优化器的行为就依赖于解析时生产的基于成本的执行计划

    • 如果该plan与SQL plan baseline中的plan都不匹配,那么优化器将其标记为unaccepted加入Plan History
    • 如果该plan匹配到SQL plan baseline中的plan,那么执行该plan,不对现有的SQL plan baseline与Plan History做任何改动

    Manual Plan Capture(手动捕获)

    手动加载已存在的执行计划到SPM是最常用的方式,需要注意的是,默认情况下手动load的plan,会被自动标记为”accepted“,创建新的SQL plan baseline或加入到已有的SQL plan baseline中,oracle提供以下5种方式手动加载Execution Plan:

  • From a SQL Tuning Set(DBMS_SPM.LOAD_PLANS_FROM_SQLSET)

  • From the cursor cache (DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE)

  • From the AWR repository (new to Oracle Database 12c Release 2)(DBMS_SPM.LOAD_PLANS_FROM_AWR)

  • Unpacked from a staging table(DBMS_SPM.UNPACK_STGTAB_BASELINE)

  • From existing stored outlines(DBMS_SPM.MIGRATE_STORED_OUTLINE)

Loading Plans into a SQL Plan Baseline

手动load的结果有2种情况,由baseline是否存在而决定:

  • 如果SQL Plan Baseline不存在,则数据库做如下事情

    1. Creates a plan history and plan baseline for the statement
    2. Marks the initial plan for the statement as accepted
    3. Adds the plan to the new baseline
  • 如果SQL Plan Baseline存在,则数据库做如下事情

    1. Marks the loaded plan as accepted
    2. Adds the plan to the plan baseline for the statement without verifying the plan's performance

    手动load的plan会被自动标为accepted,因为优化器会假定任何由管理员手动加载的plan都是性能上可接受的;当然,你也可以在load的时候在DBMS_SPM.LOAD_PLANS_FROM_% 函数中将enable设为NO,从而达到禁用的目的。

    具体的load过程,本文暂不做讨论,详情请参阅官方文档 Managing SQL Plan Baselines,这里需要说明的一点是从AWR导入执行计划是oracle 12.2才开始加入的,12.2以前要想实现从AWR中导入执行计划,需要先创建SQL Tuning set,并从AWR中将plan load进STS,再使用DBMS_SPM.LOAD_PLANS_FROM_SQLSET将计划加载到SPM中。

    下面再谈一下,对于已存在SQL Plan Baseline的SQL,其后续的新的执行计划的捕获情况;

    不论使用哪种方式创建了SQL Plan Baseline,后续新的plan都会被加入到Plan History并被标记为”unaccepted“,此行为不依赖于OPTIMIZER_CAPTURE_SQL_PLAN_ BASELINES的设置,新加入的plan不会被使用,直到其经过验证比已accepted的plan性能更好,并演化为accepted加入SQL Plan Baseline。

Plan Selection(执行计划的选择)

SPM通过几个标记来实现对执行计划的控制,这些标记可以在视图DBA_SQL_PLAN_BASELINES中查到:

  • Enabled(控制活动)

    • YES(活动的,但不一定会被使用)
    • NO(禁用的,不活动的,肯定不被使用)
  • Accepted(控制使用)

    • YES(只有 “Enabled” 并且 “Accepted” 的计划才会被选择使用)
    • NO(如果是“Enabled” 那么只有被evolve成“Accepted”才有可能被执行)
  • Fixed(控制优先级)

    • YES(如果是“Enabled”并且“Accepted”,会优先选择这个计划,这个计划会被视为不需要改变的、固定的)
    • NO(普通的计划,无需优先)
  • Reproduced(有效性)

    • YES(优化器可以使用这个计划)
    • NO(计划无效,比如索引被删除)
  • ADAPTIVE(12c引入,标记是否是Adaptive Plans)

    • YES(是一个adaptive plan,在evolve的时候会被考虑,evolve时会测试执行,验证后final plan会变为accepted)
    • NO(adaptive plan被evolve后会被标记为NO)

    先来看一下SQL Plan Selection的决策树:

Decision Tree for SQL Plan Selection

当数据库为一条sql执行硬解析的时候,优化器会生成一个best-cost plan,如果初始化参数OPTIMIZER_USE_SQL_PLAN_BASELINES被设置成为TRUE(默认为TRUE),那么在执行best-cost plan之前会先检查是否存在对应的SQL Plan Baseline,匹配的时候使用SQL语句的signature,signature是一个由SQL文本规范化后的SQL标识符(case insensitivity and with whitespaces removed)(由此可见只要一条sql生成了baseline之后,那么无论大小写改变、空格多少都会被认为是一条sql),这个比较是内存操作,因此开销很小。

如果baseline不存在,就按生成的计划执行。如果baseline存在,那么要查看history里是否有这个计划,如果没有,就将这个计划插入,并标记为ENABLED,NON-ACCEPTED。

在baseline中查看是否有FIXED的计划存在,如果存在,执行FIXED的计划,如果存在多个FIXED的计划,根据统计信息重新计算cost,选择cost小的那个。

如果FIXED的计划不存在,就选择ACCEPTED的计划执行。 如果存在多个ACCEPTED的计划,根据统计信息重新计算cost,选择cost小的那个。

如果因为某些系统的改变(例如索引删除)导致已accepted的计划无法reproducible,那么优化器将使用新生成的best-cost plan,并将其加入plan history标记为unaccepted

* 注意,这里每次重新计算cost的代价不大,因为执行计划是已知的,优化器不必遍历所有的可能,只需根据算法计算出已知计划的cost便可。

* 注意,当sql plan baseline中有Fixed的时候,新生成的执行计划是不会被加入到plan history中的。

Plan Evolution(执行计划演化)

当优化器生成一个新的执行计划后,将其加入到plan history中作为一个unaccepted的plan,它需要被verified之后才可以使用,Verification是一个比较unaccepted和accepted(所有accepted中最小cost的)plan的执行性能的过程,verify的过程是实际执行该plan的过程,通过与accepted的plan比较elapse time, CPU time and buffer gets等性能统计信息来判断新plan的性能,如果新的plan的性能优于原plan或者相差无几,那么该plan会被标记为accepted加入SQL Plan Baseline,否则它仍然是一个unaccepted的plan,但是LAST_VERIFIED属性会被更新为当前时刻,12c之后的Automatic plan evolution过程还会考虑自上次被verify之后超过30天的plan,oracle认为如果系统有所改变,也许之前的plan会有更好的性能,当然这个功能可以通过dbms_spm.alter_sql_plan_baseline来禁止。

12c之前没有自动Evolve的机制,从12.1开始automatic plan evolution由SPM Evolve Advisor来完成,11g时代的DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE被废弃,下面将分别针对11g和12c来介绍Plan Evolution的过程:

11g DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE

11g可以使用Oracle Enterprise Manager或者DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE函数来演化SQL Plan,语法如下:

1
2
3
4
5
6
7
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE (
sql_handle IN VARCHAR2 := NULL,
plan_name IN VARCHAR2 := NULL,
time_limit IN INTEGER := DBMS_SPM.AUTO_LIMIT,
verify IN VARCHAR2 := 'YES',
commit IN VARCHAR2 := 'YES')
RETURN CLOB;

这里由两个标记控制:

- Verify 
    - YES (验证比较性能)
    - NO (不验证性能)
- Commit
    - YES (演化)
    - NO (只生成报告,不演化)

这里可以通过不同的排列组合,达到不同的效果:

  • 自动接收所有性能更好的执行计划,并生成report (Verify->YES, Commit->YES)
  • 自动接收所有新的执行计划,不验证性能,生成report (Verify->NO, Commit->YES)
  • 比较性能,生成report,人工确认是否演化 (Verify->YES, Commit->NO)

12c SPM Evolve Advisor Task & Manually Evolve Task

SPM Evolve Advisor Task

从12.1开始,自动演化任务由SPM Evolve Advisor每天在维护窗口期内自动执行,SPM Evolve Advisor是一个自动任务(SYS_AUTO_SPM_EVOLVE_TASK),它每天做如下操作:

  1. Locates unaccepted plans

  2. Ranks all unaccepted plans

  3. Performs test executions of as many plans as possible during the maintenance window

  4. Selects the lowest-cost plan to compare against each unaccepted plan

  5. Accepts automatically any unaccepted plan that performs sufficiently better, using a cost-based algorithm, than the existing accepted plan

    * 需要注意的是,没有单独针对Automatic SPM Evolve Advisor task的scheduler client,Automatic SQL Tuning Advisor 和 Automatic SPM Evolve Advisor共用一个client,因此它们两个同时启用或同时禁用。

    自动任务属性可以通过DBMS_SPM.SET_EVOLVE_TASK_PARAMETER来配置,下面列举几个重要的属性:

    1
    2
    3
    4
    5
    6
    7
    8
    COL PARAMETER_NAME FORMAT a25
    COL VALUE FORMAT a42
    SELECT PARAMETER_NAME, PARAMETER_VALUE AS "VALUE"
    FROM DBA_ADVISOR_PARAMETERS
    WHERE ( (TASK_NAME = 'SYS_AUTO_SPM_EVOLVE_TASK') AND
    ( (PARAMETER_NAME = 'ACCEPT_PLANS') OR
    (PARAMETER_NAME LIKE '%ALT%') OR
    (PARAMETER_NAME = 'TIME_LIMIT') ) );
  6. 2的默认值如下(各个属性的意义详见官方文档):

    1
    2
    3
    4
    5
    6
    7
    PARAMETER_NAME		    VALUE
    ------------------------- ------------------------------------------
    TIME_LIMIT 3600
    ALTERNATE_PLAN_LIMIT 10
    ALTERNATE_PLAN_SOURCE CURSOR_CACHE+AUTOMATIC_WORKLOAD_REPOSITORY
    ALTERNATE_PLAN_BASELINE EXISTING
    ACCEPT_PLANS TRUE

    修改属性:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
      BEGIN
    DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
    task_name => 'SYS_AUTO_SPM_EVOLVE_TASK'
    , parameter => 'TIME_LIMIT'
    , value => '1200'
    );
    DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
    task_name => 'SYS_AUTO_SPM_EVOLVE_TASK'
    , parameter => 'ACCEPT_PLANS'
    , value => 'true'
    );
    DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
    task_name => 'SYS_AUTO_SPM_EVOLVE_TASK'
    , parameter => 'ALTERNATE_PLAN_LIMIT'
    , value => '500'
    );
    END;
    /

    查看结果:

    1
    2
    3
    4
    5
    6
    7
     PARAMETER_NAME            VALUE
    ------------------------- ------------------------------------------
    ALTERNATE_PLAN_LIMIT 500
    ALTERNATE_PLAN_SOURCE CURSOR_CACHE+AUTOMATIC_WORKLOAD_REPOSITORY
    ALTERNATE_PLAN_BASELINE EXISTING
    ACCEPT_PLANS true
    TIME_LIMIT 1200

    自动演化的报告可以通过函数DBMS_SPM.REPORT_AUTO_EVOLVE_TASK来查询:

    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
    26
    27
    28
    29
    30
    31
    32
    SQL> SET LONG 1000000 PAGESIZE 1000 LONGCHUNKSIZE 100 LINESIZE 100
    SQL> SELECT DBMS_SPM.report_auto_evolve_task FROM dual;

    REPORT_AUTO_EVOLVE_TASK
    ----------------------------------------------------------------------------------------------------
    GENERAL INFORMATION SECTION
    ---------------------------------------------------------------------------------------------

    Task Information:
    ---------------------------------------------
    Task Name : SYS_AUTO_SPM_EVOLVE_TASK
    Task Owner : SYS
    Description : Automatic SPM Evolve Task
    Execution Name : EXEC_1173
    Execution Type : SPM EVOLVE
    Scope : COMPREHENSIVE
    Status : COMPLETED
    Started : 04/20/2017 22:00:05
    Finished : 04/20/2017 22:00:09
    Last Updated : 04/20/2017 22:00:09
    Global Time Limit : 3600
    Per-Plan Time Limit : UNUSED
    Number of Errors : 0
    ---------------------------------------------------------------------------------------------

    SUMMARY SECTION
    ---------------------------------------------------------------------------------------------
    Number of plans processed : 0
    Number of findings : 0
    Number of recommendations : 0
    Number of errors : 0
    ---------------------------------------------------------------------------------------------
Manually Evolve Task

手动演化的过程大致如下图:

Evolving SQL Plan Baselines

  1. Create an evolve task

  2. Optionally, set evolve task parameters(在12.2.0.1中仅TIME_LIMIT有效)

  3. Execute the evolve task

  4. Implement the recommendations in the task

  5. Report on the task outcome

    个人认为4、5无绝对先后顺序

    具体操作,本文暂不讨论,请浏览Manually Evolving SQL Plan Baselines in Oracle Database 12c Release 2

Managing the SQL Management Base

利用DBMS_SPM.CONFIGURE存储过程可以配置SMB,视图DBA_SQL_MANAGEMENT_CONFIG显示了各个配置项的状态。

参数有如下几个:

  • SPACE_BUDGET_PERCENT(SYSAUX表空间的最大使用率)

  • PLAN_RETENTION_WEEKS(没被使用的plan的最大保留weeks,默认是53周)

  • AUTO_CAPTURE_PARSING_SCHEMA_NAME(自动捕获过滤schema)

  • AUTO_CAPTURE_MODULE

  • AUTO_CAPTURE_MODULE

  • AUTO_CAPTURE_SQL_TEXT(自动捕获指定的sql)

    修改SMB磁盘使用限额

    1.查询当前配置

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
      
    SELECT PARAMETER_NAME, PARAMETER_VALUE AS "%_LIMIT",
    ( SELECT sum(bytes/1024/1024) FROM DBA_DATA_FILES
    WHERE TABLESPACE_NAME = 'SYSAUX' ) AS SYSAUX_SIZE_IN_MB,
    PARAMETER_VALUE/100 *
    ( SELECT sum(bytes/1024/1024) FROM DBA_DATA_FILES
    WHERE TABLESPACE_NAME = 'SYSAUX' ) AS "CURRENT_LIMIT_IN_MB"
    FROM DBA_SQL_MANAGEMENT_CONFIG
    WHERE PARAMETER_NAME = 'SPACE_BUDGET_PERCENT';

    PARAMETER_NAME %_LIMIT SYSAUX_SIZE_IN_MB CURRENT_LIMIT_IN_MB
    -------------------- ---------- ----------------- -------------------
    SPACE_BUDGET_PERCENT 10 211.4375 21.14375

    2.修改配额

    1
    EXECUTE DBMS_SPM.CONFIGURE('space_budget_percent',30);

    3.查看结果

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
      SELECT PARAMETER_NAME, PARAMETER_VALUE AS "%_LIMIT", 
    ( SELECT sum(bytes/1024/1024) FROM DBA_DATA_FILES
    WHERE TABLESPACE_NAME = 'SYSAUX' ) AS SYSAUX_SIZE_IN_MB,
    PARAMETER_VALUE/100 *
    ( SELECT sum(bytes/1024/1024) FROM DBA_DATA_FILES
    WHERE TABLESPACE_NAME = 'SYSAUX' ) AS "CURRENT_LIMIT_IN_MB"
    FROM DBA_SQL_MANAGEMENT_CONFIG
    WHERE PARAMETER_NAME = 'SPACE_BUDGET_PERCENT';

    PARAMETER_NAME %_LIMIT SYSAUX_SIZE_IN_MB CURRENT_LIMIT_IN_MB
    -------------------- ---------- ----------------- -------------------
    SPACE_BUDGET_PERCENT 30 211.4375 63.43125

修改保留策略

1.查看当前配置

1
2
3
4
5
6
7
SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE
2 FROM DBA_SQL_MANAGEMENT_CONFIG
3 WHERE PARAMETER_NAME = 'PLAN_RETENTION_WEEKS';

PARAMETER_NAME PARAMETER_VALUE
------------------------------ ---------------
PLAN_RETENTION_WEEKS 53

2.修改配置

1
EXECUTE DBMS_SPM.CONFIGURE('plan_retention_weeks',105);

3.查看结果

1
2
3
4
5
6
7
SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE
2 FROM DBA_SQL_MANAGEMENT_CONFIG
3 WHERE PARAMETER_NAME = 'PLAN_RETENTION_WEEKS';

PARAMETER_NAME PARAMETER_VALUE
------------------------------ ---------------
PLAN_RETENTION_WEEKS 105

Monitoring SQL plan baselines

视图DBA_SQL_PLAN_BASELINES展示了当前SQL plan baselines的信息:

1
2
3
4
5
6
7
8
9
10
11
select SIGNATURE,
SQL_HANDLE,
SQL_TEXT,
PLAN_NAME,
PARSING_SCHEMA_NAME,
LAST_EXECUTED,
ENABLED,
ACCEPTED,
REPRODUCED,
EXECUTIONS
from dba_sql_plan_baselines;

结果如下:

1
2
3
4
5
6
7
8
9
                SIGNATURE SQL_HANDLE           SQL_TEXT                       PLAN_NAME                      PARSING_SC LAST_EXECUTED                       ENA ACC REP EXECUTIONS
----------------------- -------------------- ------------------------------ ------------------------------ ---------- ----------------------------------- --- --- --- ----------
here id=1

1962643652257108320 SQL_1b3cb600d175d160 select /*liu*/ * from test wh SQL_PLAN_1qg5q038rbnb025a3834b SCOTT 13-APR-17 02.23.12.000000 PM YES YES YES 0
ere id=1

1962643652257108320 SQL_1b3cb600d175d160 select /*liu*/ * from test wh SQL_PLAN_1qg5q038rbnb097bbe3d0 HR 13-APR-17 02.35.09.000000 PM YES YES YES 0
ere id=1

需要注意的一点是,该视图中LAST_EXECUTED和EXECUTIONS并不是实时更新的。

函数DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE可以查看baseline中的执行计划,语法如下:

1
2
3
4
5
 DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE (
sql_handle IN VARCHAR2 := NULL,
plan_name IN VARCHAR2 := NULL,
format IN VARCHAR2 := 'TYPICAL')
RETURN dbms_xplan_type_table;

例如:

1
2
3
4
5
6
7
8
select 
*
from table(
dbms_xplan.display_sql_plan_baseline(
sql_handle=>'SQL_5671036d51fd678f',
format=>'basic'));
--或者
select * from table(dbms_xplan.display_sql_plan_baseline('SQL_17574e83c195631c',null,'BASIC +NOTE'));

也可以通过V$SQL视图查看一条SQL是否使用SQL Base Line,如果使用了baseline,那么它的sql_plan_baseline列将会显示plan_name,因此可以连接DBA_SQL_PLAN_BASELINES和V$SQL视图:

1
2
3
4
select s.SQL_ID, s.SQL_TEXT, b.plan_name, b.origin, b.accepted
from dba_sql_plan_baselines b, v$sql s
where s.EXACT_MATCHING_SIGNATURE = b.signature
and s.SQL_PLAN_BASELINE = b.plan_name;

查看v$sql中的sql是否有baseline:

1
2
3
4
5
6
SELECT sql_handle,
plan_name
FROM dba_sql_plan_baselines
WHERE signature IN
( SELECT exact_matching_signature FROM v$sql WHERE sql_id='1s6a8wn4p6rym'
);

Dropping SQL Plan Baselines

1.查询要删除的baseline

1
2
3
4
5
6
7
8
9
10
SQL> SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN,
2 ENABLED, ACCEPTED
3 FROM DBA_SQL_PLAN_BASELINES
4 WHERE SQL_TEXT LIKE 'SELECT /* repeatable_sql%';

SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC
-------------------- -------------------- ------------------------------ -------------- --- ---
SQL_b6b0d1c71cd1807b SELECT /* repeatable SQL_PLAN_bdc6jswfd303v2f1e9c20 AUTO-CAPTURE YES YES
_sql */ count(*) fro
m hr.jobs

2.删除sql plan baseline

1
2
3
4
5
6
7
8
9
DECLARE
v_dropped_plans number;
BEGIN
v_dropped_plans := DBMS_SPM.DROP_SQL_PLAN_BASELINE (
sql_handle => 'SQL_b6b0d1c71cd1807b'
);
DBMS_OUTPUT.PUT_LINE('dropped ' || v_dropped_plans || ' plans');
END;
/

3.确认删除

1
2
3
4
5
6
SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN,
ENABLED, ACCEPTED
FROM DBA_SQL_PLAN_BASELINES
WHERE SQL_TEXT LIKE 'SELECT /* repeatable_sql%';

no rows selected

参考文献:

  1. Oracle 11g 针对SQL性能的新特性(三)- SQL Plan Management
  2. Database SQL Tuning Guide
  3. White Paper:SQL Plan Management in Oracle Database 11g
  4. White Paper:SQL Plan Management with Oracle Database 12c Release 2
0%