oracle spm buffer get比较过程,Oracle SPM（SQL Plan Management）的使用

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：收集已经创建好的计划相关信息视图。