PLSQL_性能优化工具系列09_SQL Plan Management
2014-09-24 Created By BaoXinjian
一、摘要
SQL 语句的SQL 执行计划发生更改时，可能存在性能风险。
SQL 计划发生更改的原因有很多，如优化程序版本、优化程序统计信息、优化程序参数、方案定义、系统设计和SQL 概要文件创建等。
SQL 计划管理是一种随Oracle Database 11g 引入的新功能，通过维护所谓的“SQL 计划基线(SQL plan baseline(11g))”来使系统能够自动控制SQL 计划演变。
启用此功能后，只要证明新生成的SQL 计划与SQL 计划基线相集成不会导致性能回归，就可以进行此项集成。
因此，在执行某个SQL 语句时，只能使用对应的SQL 计划基线中包括的计划。可以使用SQL 优化集自动加载或植入SQL 计划基线。
SQL 计划管理功能的主要优点是系统性能稳定，不会出现计划回归。此外，该功能还可以节省DBA 的许多时间，这些时间通常花费在确定和分析SQL 性能回归以及寻找可用的解决方案上。
在oracle的使用中，有时会出现sql执行计划改变导致性能下降的情况。
这种性能不稳定的情况，在有些批处理作业中会导致延误，而重要的在线交易系统如果发生这种问题就会成为故障。
随着Oracle版本演变，不断有新的技术来试图解决这个问题。本篇简单介绍一下这些技术。
1. SQL执行计划不稳定的大概原因有
- 数据库升级，优化器和原来不同了，同样的sql语句有可能生成不同的执行计划。在多个系统的升级过程中都遇到过。
- Table/index上的optimizer statistics过期或丢失。
- 数据量变化后重新统计optimizer statistics，或者增加、删除了索引，优化器可能选择不同的执行计划。
2. 针对这些问题，以往的解决方案有
2.1 使用hint改写sql
Hint可以指定索引，关联方式等等。
缺点是：
- 需要改代码；
- hint需要维护。例如，hint指定索引，但如果索引后来在维护时更改了名字，那么这个hint就失效了。
2.2 Outline
这是oracle10之前采用的技术。简单说，outline就是某个sql的一套hint。
(1). 建立outline
可以在系统级别或session级别，设置CREATE_STORED_OUTLINES参数。
例如，alter session set CREATE_STORED_OUTLINES=true。这样该session之后运行的sql就都会被创建outline。
也可以为单个sql创建outline
CREATE OR REPLACE OUTLINE test_bip_outline01 FOR CATEGORY TEST ON
SELECT BILLING_ACCOUNT_NO, …
FROM NRC, NRC_KEY
WHERE BILLING_ACCOUNT_NO = :B2 AND BILL_REF_NO = 0
(2). 使用outline
如果创建outline时sql运行性能良好，那么可以使用这些outline，以后运行时就会用同样的执行计划。
Alter system|session use_stored_outlines=true。
(3). Outline存在以下缺点：
- 没有进化机制。例如当数据量变化，outline中当初最优的执行计划性能不再是最优的。
- 对于一个sql只能选择一个outline。
- 缺乏主动预先发现性能问题的能力。通常出现性能问题后dba才会去用outline进行调优。
- Hint和outline称为plan stability技术。由于上面提到的缺点，oracle10g开始引入了一些新的技术。
2.3 Sql Profile
关于sql的补充信息，保存在数据字典中，用来帮助优化器选择执行计划。(可以这么理解，sql的profile，相当于table/index的optimizer statistics)
2.4 SQL Pan Baseline
前面提到，outline是sql的一套hint。和outline不同，sql plan baseline是sql的一套执行计划。
5. 总结
各种技术都有不同的适用场合，例如hint尽管存在许多缺点，但有时程序使用动态sql，表名都肯是变化的，这是就只能使用hint来固定执行计划了。
这是oracle 10g之前的概念 (plan stability)，主要是通过hint和outline实现。这两种技术存在的缺点是：
- Hint需要维护，例如hint中的索引名字如果发生变更，hint就失效了。
- 没有进化机制，一个sql只有一个固定的执行计划。当环境变化（例如数据量变化），当初选择的执行计划性能不再是最优的，hint和outline技术不能发现这点，执行计划不能‘与时俱进’。
6. 10g开始，Oracle提出了执行计划管理的概念(SQL Plan Management,简写为SPM)，先介绍SPM的几个概念
(1). SQL Plan History
对于一个sql，其运行历史中由于统计信息变化，或添加了索引等，可能产生不同的执行计划，这些计划都会保存在该sql的sql plan history。
(2). Sql Plan Baseline
用户评估其中哪些计划是性能上可以接受的。执行计划被接受后，就进入sql plan baseline中。
二、SQL Plan Baseline过程：抓取 选择 演化
Step1. 抓取
1.1 Capture sql plan baseline(抓取)
(1). 设置参数自动抓取，
初始化参数：OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true
则自动抓取系统中所有sql的sql plan
(2). 从awr/sql tuning set/shared pool中载入
(3). 对于老版本升级上来的系统，还可以将stored outline转换为sql plan
从目前的实践看，将存在性能不稳定问题的或者业务核心的sql从awr/shared pool中单独载入是比较可行的方法。
1.2 结构图
1.3 加载方式
(1). 即时捕获：
使用自动计划捕获，方法是：将初始化参数OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES 设置为TRUE。默认情况下，该参数设置为FALSE。将该参数设置为TRUE 将打开自动标识可重复SQL 语句，以及自动为此类语句创建计划历史记录的功能。
(2). 成批加载：
使用DBMS_SPM 程序包；该程序包支持手动管理SQL 计划基线。使用此程序包，可以将SQL 计划从游标高速缓存或现有的SQL 优化集(STS) 直接加载到SQL计划基线中。
对于要从STS 加载到SQL 计划基线的SQL 语句，需要将其SQL计划存储在STS中。
使用DBMS_SPM 可以将基线计划的状态从已接受更改为未接受（以及从未接受更改为已接受），还可以从登台表导出基线计划，然后使用导出的基线计划将SQL 计划基线加载到其它数据库中。
Step2. 选择
2.1 Selecting sql plan Baseline（选择执行计划）
当sql硬解析生成一个执行计划时，根据该sql是否创建了sql plan, 有不同的处理：
(1). 通常情况，sql plan baseline不存在，就按生成的计划执行。(目前大多数系统包括综合帐务都是的)
(2). 如果该sql baseline存在，那么要查看history里是否有这个计划，如果没有，说明这是个‘有史以来’新的执行计划，将这个计划插入plan history，并标记为ENABLED,NON-ACCEPTED.
(3). 在baseline选择一个执行计划。 如果存在多个ACCEPTED的计划，根据统计信息重新计算cost，选择cost小的那个。
(4). 对于新加入plan history的执行计划，用户可以评估以决定是否接受。如果接受，则进入baseline。这就是执行计划的演变(sql plan evolution)
2.2 结构图
2.3 计划选择方式
如果使用的是自动计划捕获，则第一次将某个SQL 语句标识为可重复时，其最佳成本计划将被添加到对应的SQL 计划基线中。然后，该计划将用于执行相应的语句。
如果某个SQL 语句存在计划基线，并且初始化参数OPTIMIZER_USE_SQL_PLAN_BASELINES 被设置为TRUE（默认值），则优化程序将使用比较计划选择策略。
每次编译SQL 语句时，优化程序都会先使用传统的基于成本的搜索方法建立一个最佳成本计划，然后尝试在SQL 计划基线中找到一个匹配的计划。
如果找到了匹配的计划，则优化程序将照常继续运行。
如果未找到匹配的计划，则优化程序会先将新计划添加到计划历史记录中，然后计算SQL计划基线中各个已接受的计划的成本，并选择成本最低的那个计划。
使用随各个已接受的计划存储的大纲复制这些已接受的计划。
因此，对于SQL 语句来说，拥有一个SQL 计划基线的好处就是：优化程序始终选择该SQL 计划基线中的一个已接受的计划。
Step3. 演化
3.1 Evolving SQL Plan Baseline(演化)
(1). 手工载入的sql plan都是accepted的。
(2). 否则，可以用DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE进行演化，验证及接受新的执行计划。
可以看到，接受的执行计划放在baseline中，SPM允许你同时接受多个执行计划，都是用户从性能上可以接受的(满足性能底线)，每次硬解析时, SPM根据统计信息会花费很少的运算重新计算cost，这就保证了统计信息变更后仍能选择最好的执行计划，而不是一成不变。
新产生的执行计划，先加入到plan history，用户认可后才进入baseline，这防止了性能突变。
无论sql plan stability还是sql plan management 都是对oracle自适应优化器缺陷的弥补。
3.2 结构图
3.3 演化方式
(1). 使用DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE 函数
该函数将返回一个报表，显示是否已将一些现有的历史记录计划移到了计划基线中。也可以在历史记录中指定要测试的特定计划。
(2). 运行SQL 优化指导
通过使用SQL 优化指导手动或自动优化SQL 语句，演化SQL计划基线。
SQL优化指导发现已优化的计划，并确认其性能优于从相应的SQL 计划基线中选择的计划的性能时，就会生成一个建议案以接受SQL 概要文件。
接受了该SQL 概要文件后，会将已优化的计划添加到相应的SQL 计划基线中。
Step4. 总结Summary
否则为什么要这么麻烦？oracle优化器根据当前情况选择一个最好的不就得了吗？
下面以实验来演示当环境变更时，使用outline和sql plan management的不同效果；并演示sql plan是如何使用的。
实验说明：table t1上有一个索引t1_idx，数据量从几十条到几万条之间变化，由于统计信息变化可能产生全表扫描的执行计划和走索引t1_idx的执行计划。
为了性能稳定，我们可以使用outline和sql plan baseline来使其走索引。之后当该表结构变化(增加了一个索引t1_idx2)，我们可以看到sql plan managment能够演变而outline却不能。
三、SQL Plan Baseline属性
如果将计划添加到计划历史记录中，则该计划将与一些重要的属性关联：
(1). SIGNATURE、SQL_HANDLE、SQL_TEXT 和PLAN_NAME 是搜索操作的重要标识符。
(2). 使用ORIGIN 可以确定计划是自动捕获的(AUTO-CAPTURE)、手动演化的(MANUALLOAD)、通过SQL 优化指导自动演化的(MANUAL-SQLTUNE) 还是通过自动SQL 优化自动演化的(AUTO-SQLTUNE)。
(3). ENABLED 和ACCEPTED：
ENABLED属性表示计划已启用，可供优化程序使用。
如果未设置ENABLED，则系统将不考虑此计划。
ACCEPTED 属性表示用户在将计划更改为ACCEPTED 时计划已经过验证为有效计划（系统自动进行的或用户手动进行的）。
如果将某个计划更改为ACCEPTED，则仅当使用DBMS_SPM.ALTER_SQL_PLAN_BASELINE()更改其状态时，该计划才是非ACCEPTED 的。
可以通过删除ENABLED设置暂时禁用ACCEPTED 计划。计划必须为ENABLED 和ACCEPTED，优化程序才会考虑使用它。
(4). FIXED 表示优化程序仅考虑标记为FIXED 的计划，而不考虑其它计划。
例如，如果有10 个基线计划，其中的三个计划被标记为FIXED，则优化程序将仅使用这三个计划中的最佳计划，而忽略其它所有计划。
如果某个SQL 计划基线至少包含一个已启用的已修复计划，则该SQL 计划基线就是FIXED 的。
如果在修复的SQL 计划基线中添加了新计划，则在手动将这些新计划声明为FIXED 之前，无法使用这些新计划。
(5). 总结
可以使用DBA_SQL_PLAN_BASELINES视图查看每个计划的属性。然后，可以使用DBMS_SPM.ALTER_SQL_PLAN_BASELINE 函数更改其中的某些属性。也可以使用DBMS_SPM.DROP_SQL_PLAN_BASELINE 函数删除计划或整个计划历史记录。
注：DBA_SQL_PLAN_BASELINES 视图包含了一些附加属性；使用这些属性可以确定各个计划的上次使用时间，以及是否应自动清除某个计划。
(6). 结构图
四、SQL Baseline Plan结构图
1. 发展
2. 解析计划抓取
3. 手工解析计划抓取
4. 解析计划选择
5. DBMS_SPM包调用方式
五、案例：启动outline固定一段SQL的解析计划，并进行演化
案例：启动outline固定一段SQL的解析计划，并进行演化
Step1. 创建测试表和测试索引
CREATE TABLE bxj_sql_outline AS SELECT * FROM dba_objects; CREATE INDEX bxj_outline_index ON bxj_sql_outline(object_name);
Step2. 分析表
BEGIN DBMS_STATS.GATHER_TABLE_STATS (ownname => 'SYSTEM', tabname => 'BXJ_SQL_OUTLINE', estimate_percent => 5, degree => 4, cascade => TRUE); END;
Step3. 创建Outline
CREATE OR REPLACE OUTLINE bxj_outline FOR CATEGORY bxj_cate_outline ON SELECT * FROM bxj_sql_outline t1 WHERE t1.object_name LIKE 'TO%' AND t1.object_id BETWEEN 30 AND 100;
Step4. 查看所创建的Outline
Step5. 设定参数use_stored_outlines，以启动outline设定
ALTER SYSTEM SET USE_STORED_OUTLINES = bxj_cate_outline;
Step6. 没有索引时，查询该SQL的解析计划SQL Plan
SQL> SET autotrace ON explain SQL> SELECT * FROM bxj_sql_outline t1 WHERE t1.object_name LIKE 'TO%' AND t1.object_id BETWEEN 30 AND 100; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 3932729292 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 97 | 6 (0)| 00:00:01 | |* 1 | TABLE ACCESS BY INDEX ROWID| BXJ_SQL_OUTLINE | 1 | 97 | 6 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | BXJ_OUTLINE_INDEX | 6 | | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("T1"."OBJECT_ID"<=100 AND "T1"."OBJECT_ID">=30) 2 - access("T1"."OBJECT_NAME" LIKE 'TO%') filter("T1"."OBJECT_NAME" LIKE 'TO%') Note ----- - outline "BXJ_OUTLINE" used for this statement
Step7. 创建新的索引，并重新分析该表
CREATE INDEX bxj_outline_index_new ON bxj_sql_outline(object_id); BEGIN DBMS_STATS.GATHER_TABLE_STATS (ownname => 'SYSTEM',tabname => 'BXJ_SQL_OUTLINE',estimate_percent => 5,degree => 4,cascade => TRUE); END;
Step8. 查看新增Index后的解析计划
SQL> SELECT * FROM bxj_sql_outline t1 WHERE t1.object_name LIKE 'TO%' AND t1.object_id BETWEEN 30 AND 100; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 3932729292 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 97 | 6 (0)| 00:00:01 | |* 1 | TABLE ACCESS BY INDEX ROWID| BXJ_SQL_OUTLINE | 1 | 97 | 6 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | BXJ_OUTLINE_INDEX | 6 | | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("T1"."OBJECT_ID"<=100 AND "T1"."OBJECT_ID">=30) 2 - access("T1"."OBJECT_NAME" LIKE 'TO%') filter("T1"."OBJECT_NAME" LIKE 'TO%') Note ----- - outline "BXJ_OUTLINE" used for this statement
Step9. Outline总结Summary
结论：由于存在outline，即使新增加了索引，oracle仍旧不会产生新的执行计划。
性能虽然也算稳定，但不能根据环境变化而产生最优性能，这就是outline的缺点。
五、案例：启动SQL Plan Baseline Management并固定一段SQL的解析计划
案例：启动SQL Plan Baseline Management并固定一段SQL的解析计划
Step1. 创建表和索引
CREATE TABLE bxj_sql_baseline AS SELECT * FROM dba_objects; CREATE INDEX bxj_index ON bxj_sql_baseline(object_name);
Step2. 分析表
BEGIN DBMS_STATS.GATHER_TABLE_STATS (ownname => 'SYSTEM', tabname => 'BXJ_SQL_BASELINE', estimate_percent => 5, degree => 4, cascade => TRUE); END;
Step3. 查看SQL Explan 和 Execute Time
SQL> SET autotrace traceonly SQL> SET linesize 600 SQL> SELECT * FROM system.bxj_sql_baseline t1 WHERE t1.object_name LIKE 'TO%' AND t1.object_id BETWEEN 30 AND 100; 2 3 no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 2603117765 ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 97 | 6 (0)| 00:00:01 | |* 1 | TABLE ACCESS BY INDEX ROWID| BXJ_SQL_BASELINE | 1 | 97 | 6 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | BXJ_INDEX | 6 | | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("T1"."OBJECT_ID"<=100 AND "T1"."OBJECT_ID">=30) 2 - access("T1"."OBJECT_NAME" LIKE 'TO%') filter("T1"."OBJECT_NAME" LIKE 'TO%') Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 10 consistent gets 0 physical reads 0 redo size 1183 bytes sent via SQL*Net to client 408 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed
Step4. 查看SQLID
SELECT sql_id, sql_text FROM v$sql WHERE sql_text LIKE '%system.bxj_sql_baseline%';
SQL_ID => 1fnvxw3qaupc2
Step5. 创建sql baseline, 注意手工载入的执行计划不需要ACCEPT而会自动进入baseline
DECLARE bxj_plans PLS_INTEGER; BEGIN bxj_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (sql_id => '1fnvxw3qaupc2'); END;
Step6. 查看SQL Plan的Baseline
SQL_HANDLE => SYS_SQL_6826c334bfd35f14
Step7. 可以看看一下这个执行计划是怎样的
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE (sql_handle => 'SYS_SQL_6826c334bfd35f14',format => 'basic'));
PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL handle: SYS_SQL_6826c334bfd35f14 SQL text: SELECT * FROM system.bxj_sql_baseline t1 WHERE t1.object_name LIKE 'TO%' AND t1.object_id BETWEEN 30 AND 100 -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- Plan name: SQL_PLAN_6h9q36kzx6rsn84dabf37 Plan id: 2228928311 Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD -------------------------------------------------------------------------------- Plan hash value: 2603117765 -------------------------------------------------------- | Id | Operation | Name | -------------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID| BXJ_SQL_BASELINE | | 2 | INDEX RANGE SCAN | BXJ_INDEX | --------------------------------------------------------
Thanks and Regards
参考：David http://blog.csdn.net/tianlesoftware/article/details/8292410
参考：百度文库
参考：Oracle Metalink http://docs.oracle.com/database/121/TGSQL/tgsql_spm.htm#TGSQL625
技术交流，技术讨论，欢迎加入
Technology Blog Created By Oracle ERP - 鲍新建
