Oracle SPM（SQL Plan Management）的使用

DBA小记 2020-10-27

3178

1、Oracle优化器辅助手段的发展

Oracle 8：HINT

Oracle 8&9: STORED OUTLINES

Oracle 10: SQL PROFILE

Oracle 11: SPM(SQL Plan Management)

Oracle 12:自适应计划

2、SPM简介

SPM特性有助于保持SQL语句的性能，只允许执行能提高语句性能的执行计划。SPM类似但又不同于STORED OUTLINES。SPM的目的是稳定SQL语句的执行计划，STORED OUTLINES是冻结SQL语句的执行计划，而SPM允许选择新的执行计划，只要能提高SQL语句的性能即可，我们需要SPM的原由可能包括：

①新版本的Oracle（新的优化器版本--使用捕捉和回放来测试影响）；

②对优化器统计信息的更改或数据的变化；

③SCHEMA、应用程序或元数据的变化（使用SQL顾问的建议）；

④系统设置发生更改（使用数据库回放）；

⑤SQL配置文件（SQL Profile）的创建（数据倾斜和相关列的统计信息）。

3、SPM示例

SPM在捕捉计划基线时分为自动捕捉和手动导入，此示例为自动捕捉（Automatic Initial Plan Capture）

（1）启动捕获并运行SQL语句，SPM判断语句是否可重复，然后关闭捕获：

在系统或会话级别设置OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES为true。

SQL> create table test_spm as select * from dba_objects;
Table created.
SQL> show parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
NAME                             TYPE       VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean       FALSE
SQL> alter session set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true;
SQL> select owner,object_name from test_spm where object_id= '1061 ';
SQL> select owner,object_name from test_spm where object_id= '1061 ';
SQL> alter session set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=false;

（2）查询dba_sql_plan_baselines，确定SQL语句在SPM中的状态：

SQL> select plan_name,sql_handle,enabled,accepted,fixed,module,sql_text from dba_sql_plan_baselines;
PLAN_NAME                   SQL_HANDLE                  ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
MODULE
----------------------------------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
SQL_PLAN_3wv37cmg2hun7eb1890ae SQL_3e6c6764de286a87            YES YES NO
SQL*Plus
 select owner,object_name from test_spm where object_id= '1061 '

注意：enabled为yes，accepted为yes，fixed为on。另外，SQL*Plus是添加这个计划的模块。

SPM通过几个标记来实现对执行计划的控制：

Enabled (控制活动)：

+ YES (活动的，但不一定会被使用)

+ NO (可以理解为被标记删除)

Accepted(控制使用)：

+ YES (只有 “Enabled” 并且“Accepted” 的计划才会被选择使用)

+ NO (如果是“Enabled” 那么只有被evolve成“Accepted”才有可能被执)

Fixed(控制优先级)：

+ YES (如果是“Enabled”并且“Accepted”，会优先选择这个计划，这个计划会被视为不需要改变的)

+ NO (普通的计划，无需优先)

Reproduced(有效性)：

+ YES (优化器可以使用这个计划)

+ NO (计划无效，比如索引被删除)

（3）增加索引以改变数据环境，并再次运行该语句。

SQL> create index idex_test on test_spm (object_id);
Index created.
SQL> alter session set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true;
SQL> select owner,object_name from test_spm where object_id= '1061 ';
SQL> select owner,object_name from test_spm where object_id= '1061 ';
SQL> alter session set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=false;

注意：即使关闭了自动捕捉，针对存在baseline的SQL，仍旧会有新的PLAN生成，新的Plan仍会进入Plan History，标记为ENABLED但不是ACCEPTED。

（4）查询dba_sql_plan_baselines，再次确定SQL语句在SPM中的状态：

SQL> select plan_name,sql_handle,enabled,accepted,fixed,module,sql_text from dba_sql_plan_baselines;


PLAN_NAME                   SQL_HANDLE                  ENA ACC FIX MODULE
------------------------------ ------------------------------ --- --- --- ----------------------------------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
SQL_PLAN_3wv37cmg2hun7073625f3 SQL_3e6c6764de286a87            YES NO  NO  SQL*Plus
 select owner,object_name from test_spm where object_id= '1061 '


SQL_PLAN_3wv37cmg2hun7eb1890ae SQL_3e6c6764de286a87            YES YES NO  SQL*Plus
 select owner,object_name from test_spm where object_id= '1061 '

此时：添加了另外一个计划名称（具有相同的SQL_HANDLE），并且accepted是no，表示优化器不会考虑这个计划。

（5）现在设置OPTIMIZER_USE_SQL_PLAN_BASELINES为false，并允许优化器选择执行计划（不一定在计划基线中的某一种）。然后，设置OPTIMIZER_USE_SQL_PLAN_BASELINES为true，并强制优化器在计划基线中仅仅选择一个已接收的执行计划。

关闭SPM查看执行计划：

SQL> show parameter OPTIMIZER_USE_SQL_PLAN_BASELINES
NAME                             TYPE       VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_sql_plan_baselines     boolean       TRUE
SQL> alter system set OPTIMIZER_USE_SQL_PLAN_BASELINES=false;
SQL> explain plan for
select owner,object_name from test_spm where object_id= '1061 ';
SQL> select * from table(dbms_xplan.display(null,null, 'basic'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
Plan hash value: 3093064873


-------------------------------------------------
| Id  | Operation                | Name      |
-------------------------------------------------
|   0 | SELECT STATEMENT          |            |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_SPM      |
|   2 |   INDEX RANGE SCAN          | IDEX_TEST |
-------------------------------------------------

此时可以看到执行计划走的是索引的范围扫描。

开启SPM查看执行计划：

SQL> alter system set OPTIMIZER_USE_SQL_PLAN_BASELINES=true;
SQL> explain plan for
select owner,object_name from test_spm where object_id= '1061 ';
SQL> select * from table(dbms_xplan.display(null,null, 'basic'));


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
Plan hash value: 1145642998
--------------------------------------
| Id  | Operation        | Name     |
--------------------------------------
|   0 | SELECT STATEMENT  |           |
|   1 |  TABLE ACCESS FULL| TEST_SPM |
--------------------------------------

此时SQL执行计划使用了全表扫描，并没有使用accepted为no的索引扫描计划，因为它是未被接收的计划。

（6）使用DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE演进SQL执行计划，允许accepted状态为NO的计划变成YES，如果这个计划比原有accepted状态已经是YES的执行计划性能更优的话。

SQL> set serveroutput on
set long 10000
declare
  report clob;
begin
  report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
            sql_handle => 'SQL_3e6c6764de286a87');
  DBMS_OUTPUT.PUT_LINE(report);
end;
/
-------------------------------------------------------------------------------
                  Evolve SQL Plan Baseline
Report
-------------------------------------------------------------------------------
Inputs:
-------
  SQL_HANDLE = SQL_3e6c6764de286a87
  PLAN_NAME  = 
  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
  VERIFY
= YES
  COMMIT     = YES


Plan: SQL_PLAN_3wv37cmg2hun7073625f3
------------------------------------
  Plan was verified: Time used .12 seconds.
  Plan passed performance criterion: 355.2 times better
than baseline plan.
  Plan was changed to an accepted plan.


                      Baseline Plan      Test Plan       Stats Ratio
                      -------------      ---------
-----------
  Execution Status:             COMPLETE      COMPLETE
  Rows Processed:                  1             1
  Elapsed Time(ms):                5.426          .031
175.03
  CPU Time(ms):                 5.443          .111           49.04
  Buffer Gets:                       1069             3          356.33
  Physical Read Requests:            0
0
  Physical Write Requests:            0             0
  Physical Read Bytes:                  0             0
  Physical Write Bytes:             0             0
  Executions:
1             1
-------------------------------------------------------------------------------
                         Report
Summary
-------------------------------------------------------------------------------
Number of plans verified: 1
Number of plans accepted: 1


PL/SQL procedure successfully completed.

此时查询dba_sql_plan_baselines，SQL_PLAN_3wv37cmg2hun7073625f3这个执行计划此前是未接收状态，现在变成已接收状态：

SQL> select plan_name,sql_handle,enabled,accepted,fixed,module,sql_text from dba_sql_plan_baselines;
PLAN_NAME                   SQL_HANDLE                  ENA ACC FIX MODULE
------------------------------ ------------------------------ --- --- --- ----------------------------------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
SQL_PLAN_3wv37cmg2hun7073625f3 SQL_3e6c6764de286a87            YES YES NO  SQL*Plus
 select owner,object_name from test_spm where object_id= '1061 '


SQL_PLAN_3wv37cmg2hun7eb1890ae SQL_3e6c6764de286a87            YES YES NO  SQL*Plus
 select owner,object_name from test_spm where object_id= '1061 '

（7）再次设定OPTIMIZER_USE_SQL_PLAN_BASELINES为true时，运行SQL语句，就会使用这个新的已接收计划，因为索引的性能比全表扫描更好。

SQL> alter system set OPTIMIZER_USE_SQL_PLAN_BASELINES=true;
System altered.
SQL> explain plan for
select owner,object_name from test_spm where object_id= '1061 ';
Explained.
SQL> select * from table(dbms_xplan.display(null,null, 'basic'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
Plan hash value: 3093064873
-------------------------------------------------
| Id  | Operation                | Name      |
-------------------------------------------------
|   0 | SELECT STATEMENT          |            |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_SPM      |
|   2 |   INDEX RANGE SCAN          | IDEX_TEST |
-------------------------------------------------

4、使用固定的SQL计划基线

如果一条SQL语句存在不止一个固定计划，那么使用性能最好的固定计划。

SQL> declare
  l_plans_altered PLS_INTEGER;
begin
  l_plans_altered := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
            sql_handle => 'SQL_3e6c6764de286a87',
            plan_name => NULL,
            attribute_name => 'fixed',
            attribute_value => 'YES');
end;
/
PL/SQL procedure successfully completed.

此时查询dba_sql_plan_baselines，SQL_3e6c6764de286a8的fixed列值已经变为YES。

SQL> select plan_name,sql_handle,enabled,accepted,fixed,module,sql_text from dba_sql_plan_baselines;
PLAN_NAME                   SQL_HANDLE                  ENA ACC FIX MODULE
------------------------------ ------------------------------ --- --- --- ----------------------------------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
SQL_PLAN_3wv37cmg2hun7073625f3 SQL_3e6c6764de286a87            YES YES YES SQL*Plus
 select owner,object_name from test_spm where object_id= '1061 '

--删除计划

declare
  l_plans_droped PLS_INTEGER;
begin
  l_plans_droped := DBMS_SPM.DROP_SQL_PLAN_BASELINE(
            sql_handle => 'SQL_3e6c6764de286a87');
end;
/
SQL> select plan_name,sql_handle,enabled,accepted,fixed,module,sql_text from dba_sql_plan_baselines;
no rows selected

5、手动导入（Manual Plan Capture）

导入的baseline都会被自动标记为ACCEPTED， 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）通过staging table从另外一个系统中移植：

DBMS_SPM.CREATE_STGTAB_BASELINE

DBMS_SPM.PACK_STGTAB_BASELINE

DBMS_SPM.UNPACK_STGTAB_BASELINE

手动导入示例：

常用的从Cursor Cache中load plan，使用DBMS_SPM.load_plans_from_cursor_cache函数来完成。

SQL> select plan_name,sql_handle,enabled,accepted,fixed,module,sql_text from dba_sql_plan_baselines;
no rows selected
SQL> select sql_id,sql_text from v$sql where sql_text like 'select owner,object_name%';
SQL_ID
-------------
SQL_TEXT
--------------------------------------------
62jytznuqhfm2
select owner,object_name from test_spm where object_id= '1061 '
SQL> DECLARE
  l_plans_loaded  PLS_INTEGER;
BEGIN
  l_plans_loaded :=DBMS_SPM.load_plans_from_cursor_cache(sql_id => '62jytznuqhfm2');
END;
/ 
PL/SQL procedure successfully completed.

查看dba_sql_plan_baselines，视图中多了一条记录，并且显示该计划是accepted=YES状态。

SQL> select plan_name,sql_handle,enabled,accepted,fixed,module,sql_text from dba_sql_plan_baselines;




PLAN_NAME                   SQL_HANDLE                  ENA ACC FIX MODULE
------------------------------ ------------------------------ --- --- --- ----------------------------------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
SQL_PLAN_3wv37cmg2hun7eb1890ae SQL_3e6c6764de286a87            YES YES NO  SQL*Plus
select owner,object_name from test_spm where object_id= '1061 '

6、SPM术语

SMB（SQL Management Base，SQL管理基线）相关术语

SPM主要功能：

计划捕捉（Plan Capture）创建SQL计划基线，保存接收的执行计划。

计划选择（Plan selection）新生产的执行计划一开始保存在SQL计划基线中，且处于“未接收”状态。

计划演进（Plan evolution）演进那些在一定阀值下可以提高性能的“未接收”计划，变成接收状态并使用。

SQL管理库（SMB）：保存SQL计划历史和SQL计划基线，位于SYSAUX表空间，同时也保存SQL Profile。

SQL计划历史：SMB的子集，包含SQL生成的已接收和未接收计划。

SQL计划基线：SQL计划历史的子集，只包含SQL生成的已接收计划。

旗标：用来标记SMB中执行计划的状态。

启用计划历史或计划基线中执行计划状态的默认值。
接收计划在被认为可用之前需要被接收。
固定该计划优于其他计划。
重现 CBO对于给定SQL能够重现的执行计划，自动设置为YES，如果不能重现，设置为NO。
自适应计划（Oracle12c 新特性）被认定是自适应计划，没有被接收；一旦计划被接收，这个自适应标志将会变成NO。

与计划的状态相关的术语。

接收的计划：计划必须同时启用和接收，才会被优化器使用。

启用的计划：SQL计划历史或SQL计划基线中执行计划的默认值已启用。计划必须同时启用和接收，才会被优化器使用。

固定的计划：固定的执行计划相比其他的计划优先级高。除非有其他固定的执行计划，这时会选择性能最优的固定执行计划。

其他与SPM相关术语。

AUTOPURGE：如果一个计划连续53周没有被使用，就会自动从计划历史中清除（基于视图DBA_SQL_PLAN_BASELINES中的LAST_EXECUTED日期），可以使用DBMS_SPM.CONFIGURE包修改这个日期。

OPTIMIZER_USE_SQL_PLAN_BASELINES：默认是true。如果SQL语句的计划基线存在，该数据库参数确定是否需要使用它。

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES：默认是false。如果设置成true，那么任何执行的SQL语句都会被添加到SQL计划基线（但不一定是接收的计划）。

DBA_SQL_PLAN_BASELINES：收集已经创建好的计划相关信息视图。

oracle

文章转载自DBA小记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle SPM（SQL Plan Management）的使用

评论