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：收集已经创建好的计划相关信息视图。
7583
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
