is916ena3量产工具 (is917量产工具) SPM简介

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

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

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

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

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

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

Oracle优化器辅助手段的发展

Oracle 8：HINT

Oracle 8&9: STORED OUTLINES

Oracle 10: SQL PROFILE

Oracle 11: SPM(SQL Plan Management)

Oracle 12:自适应计划

SPM 自动管理执行计划

建立 Baseline 基线（有效的执行计划的集合，包括一个或多个执行计划集合） baseline 中的执行计划会被优化器电脑使用。

应用环境发生改变时，可以通过 baseline 选择更好的执行计划，稳定 sql 执行计划。

稳定执行计划的方法：

hints、store outline、sql profile 提供了单一的执行计划稳定方法，不会随数据变化而改变。

可选择的：SPM允许选择新的执行计划，只要提高sql性能便可。

SPM在捕捉计划基线时分为自动捕捉和手动导入，本文分别演示两种场景。

自动捕获

SQL> show parameter optimizer_use NAME TYPE VALUE------------------------------------ ----------- ------------------------------optimizer_use_invisible_indexes boolean FALSEoptimizer_use_pending_statistics boolean FALSEoptimizer_use_sql_plan_baselines boolean TRUE 使用为trueSQL> show parameter optimizer_captureNAME TYPE VALUE------------------------------------ ----------- ------------------------------optimizer_capture_sql_plan_baselines boolean FALSE 自动捕获为false

创建测试表SQL> create table test as select object_id,object_name from dba_objects;Table created.查看当前的baseline情况SQL> select plan_name,sql_handle,enabled,accepted,fixed,module,sql_text from dba_sql_plan_baselines;no rows selecte会话级别启动spm捕获在系统或会话级别设置OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES为trueSQL> alter session set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=电脑true;SQL> select object_id,object_name from test where object_id='18';SQL> select object_id,object_name from test where object_id='18';SQL> alter session set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=false;查看自动捕获的baseline信息select plan_name,sql_handle,enabled,accepted,fixed,module,sql_text from dba_sql_plan_baselinesSQL> /PLAN_NAME SQL_HANDLE ENA ACC FIX MODULE SQL_TEXT-------------------------------------------------- ------------------------------ --- --- --- -------------------- --------------------------------------------------------------------------------SQL_PLAN_ccs5bvy606pgh97bbe3d0 SQL_c660abdf8c0355f0 YES YES NO SQL*Plus select object_id,object_name from test where object_id='18'注意：enabled为yes，accepted为yes，fixed为NO。另外，SQL*Plus是添加这个计划的模块。SPM通过几个标记来实现对执行计划的控制：Enabled (控制活动)： + YES (活动的，但不一定会被使用) + NO (可以理解为被标记删除)Accepted(控制使用)： + YES (只有 “Enabled” 并且“Accepted” 的计划才会被选择使用) + NO (如果是“Enabled” 那么只有被evolve成“Accepted”才有可能被执)Fixed(控制优先级)： + YES (如果是“Enabled”并且“Accepted”，会优先选择这个计划，这个计划会被视为不需要改变的) + NO (普通的计划，无需优先)Reproduced(有效性)： + YES (优化器可以使用这个计划) + NO (计划无效，比如索引被删除) 启用 计划历史或计划基线中执行计划状态的默认值。 enable 接收 计划在被认为可用之前需要被接收。 accept 固定 该计划优于其他计划。fix查看此时的执行计划SQL> explain plan for select object_id,object_name from test where object_id='18';Explained.SQL> select * from table(dbms_xplan.display());PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 1357081020--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 39 | 123 (1)| 00:00:01 ||* 1 | TABLE ACCESS FULL| TEST | 1 | 39 | 123 (1)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 - filter("OBJECT_ID"=18)Note----- - SQL plan baseline "SQL_PLAN_ccs5bvy606pgh97bbe3d0" used for this statement17 rows selected.

SQL_PLAN_ccs5bvy606pgh97bbe3d0 为自动生成的baseline，优化器选择此执行计划进行sql执行。

针对此表创建索引再次执行捕获

SQL> create index test_idx on test(object_id);Index created.SQL> alter session set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true;Session altered.SQL> select object_id,object_name from test where object_id='18'; OBJECT_ID OBJECT_NAME---------- --------------------------------------------------------------------------------------------------------------------------------18 OBJ$SQL> / OBJECT_ID OBJECT_NAME---------- --------------------------------------------------------------------------------------------------------------------------------18 OBJ$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_ccs5bvy606pgh25c952fc SQL_c660abdf8c0355f0 YES NO NO SQL*Plus select object_id,object_name from test where object_id='18'SQL_PLAN_ccs5bvy606pgh97bbe3d0 SQL_c660abdf8c0355f0 YES YES NO SQL*Plus select object_id,object_name from test where object_id='18'SQL_PLAN_gpgp0201jych63b190376 SQL_fabea010031f3206 YES YES NO SQL*Plus select plan_name,sql_handle,enabled,accepted,fixed,module,sql_text from dba_sql_

新捕获的baseline的accept状态是NO

SQL_PLAN_ccs5bvy606pgh25c952fc SQL_c660abdf8c0355f0 YES NO NO

此时虽然表中有索引但是优化器仍然不会选择此执行计划，只有 “Enabled” 并且“Accepted” 的计划才会被选择使用。

再次允许sql

select object_id,object_name from test where object_id='18';

如图所示执行计划仍然按照原来的全表扫描执行，但是我们直到这并不是最优的执行计划。

此时如果设置了OPTIMIZER_USE_SQL_PLAN_BASELINES=false那么优化器会自动选择索引扫描如下：

执行计划演进

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

select plan_name,sql_handle,enabled,accepted,fixed,module,sql_text from dba_sql_plan_baselinesSQL> /PLAN_NAME SQL_HANDLE ENA ACC FIX MODULE SQL_TEXT------------------------------------------------------------ ------------------------------ --- --- --- -------------------- ------------------------------------------------------------SQL_PLAN_0datnfnnzk5rjdf463620 SQL_06ab347529f916f1 YES YES NOSQL*Plus select * from table(dbms_xplan.display())SQL_PLAN_ccs5bvy606pgh25c952fc SQL_c660abdf8c0355f0 YES NO NOSQL*Plus select object_id,object_name from test where object_id='18'SQL_PLAN_ccs5bvy606pgh97bbe3d0 SQL_c660abdf8c0355f0 YES YES NOSQL*Plus select object_id,object_name from test where object_id='18执行演进SQL> set serveroutput onset long 10000declare report clob;begin report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE( sql_handle => 'SQL_c660abdf8c0355f0'); DBMS_OUTPUT.PUT_LINE(report);end;/

执行演进的时候数据库会自动分析两个执行计划的执行效率，如果发现新的执行计划优于原有的那么会接收此执行计划作为新的baseline。状态accept变为yes。

再次查看baseline状态发现两个全部变为了accept的状态。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_0datnfnnzk5rjdf463620 SQL_06ab347529f916f1 YES YES NOSQL*Plus select * from table(dbms_xplan.display())SQL_PLAN_ccs5bvy606pgh25c952fc SQL_c660abdf8c0355f0 YES YES NOSQL*Plus select object_id,object_name from test where object_id='18'SQL_PLAN_ccs5bvy606pgh97bbe3d0 SQL_c660abdf8c0355f0 YES YES NOSQL*Plus select object_id,object_name from test where object_id='18'SQL_PLAN_gpgp0201jych63b190376 SQL_fabea010031f3206 YES YES NOSQL*Plus select plan_name,sql_handle,enabled,accepted,fixed,module,sq l_text from dba_sql_plan_baselines

再次执行sql语句查看执行计划状态

SQL> explain plan for select object_id,object_name from test where object_id='18';Explained.SQL> select * from table(dbms_xplan.display());PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 3208281253------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 39 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST | 1 | 39 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | TEST_IDX | 1 | | 1 (0)| 00:00:01 |------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 2 - access("OBJECT_ID"=18)Note----- - SQL plan baseline "SQL_PLAN_ccs5bvy606pgh25c952fc" used for this statement18 rows selected

- SQL plan baseline "SQL_PLAN_ccs5bvy606pgh25c952fc" used for this statement

可以看到sql已经选择了新的执行计划并且为索引访问。

固定执行计划

为了防止baseline一直变化，可以通过固定执行计划的方式解决。

declare l_plans_altered PLS_INTEGER;begin l_plans_altered := DBMS_SPM.ALTER_SQL_PLAN_BASELINE( sql_handle => 'SQL_c660abdf8c0355f0', #输入上面对应的sql_handle. plan_name => NULL, attribute_name => 'fixed', attribute_value => 'YES');end;/

再次查看baseline信息发现fixed变为了yes。

删除执行计划

declare l_plans_droped PLS_INTEGER;begin l_plans_droped := DBMS_SPM.DROP_SQL_PLAN_BASELINE( sql_handle => 'SQL_c660abdf8c0355f0');end;/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_0datnfnnzk5rjdf463620 SQL_06ab347529f916f1 YES YES NOSQL*Plus select * from table(dbms_xplan.display())SQL_PLAN_gpgp0201jych63b190376 SQL_fabea010031f3206 YES YES NOSQL*Plus select plan_name,sql_handle,enabled,accepted,fixed,module,sq l_text from dba_sql_plan_baselines执行计划已经删除手动加载执行计划

导入的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_BASELINEDBMS_SPM.PACK_STGTAB_BASELINEDBMS_SPM.UNPACK_STGTAB_BASELINE

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

SQL> select sql_id,sql_text from v$sql where sql_text like 'select object_id,object_name%';SQL_ID SQL_TEXT------------- ------------------------------------------------------------0jfwj5fdugcxt select object_id,object_name from test where object_id='18'SQL> DECLARE l_plans_loaded PLS_INTEGER;BEGIN l_plans_loaded :=DBMS_SPM.load_plans_from_cursor_cache(sql_id => '0jfwj5fdugcxt');END;/ PL/SQL procedure successfully completed.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_0datnfnnzk5rjdf463620 SQL_06ab347529f916f1 YES YES NOSQL*Plus select * from table(dbms_xplan.display())SQL_PLAN_ccs5bvy606pgh25c952fc SQL_c660abdf8c0355f0 YES YES NOSQL*Plus select object_id,object_name from test where object_id='18'SQL_PLAN_gpgp0201jych63b190376 SQL_fabea010031f3206 YES YES NOSQL*Plus select plan_name,sql_handle,enabled,accepted,fixed,module,sq l_text from dba_sql_plan_baselines

再次执行sql查看执行计划，sql的执行计划已使用手动导入的base plan。

接收的计划：计划必须同时启用和接收，才会被优化器使用。启用的计划：SQL计划历史或SQL计划基线中执行计划的默认值已启用。计划必须同时启用和接收，才会被优化器使用。固定的计划：固定的执行计划相比其他的计划优先级高。除非有其他固定的执行计划，这时会选择性能最优的固定执行计划。计划演进（Plan evolution） 演进那些在一定阀值下可以提高性能的“未接收”计划，变成接收状态并使用。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：收集已经创建好的计划相关信息视图。

参考《https://www.modb.pro/db/37771》


电脑