本文固定链接：https://www.askmac.cn/archives/oracle-11g-ocm-spm.html

 

 

6  SQL执行计划管理

 

 

6.1目标

 

在完成这一课后，你应该能够做下列事情：

• 设置SQL计划管理
• 设置各种各样的SQL计划管理场景
• 迁移存储大纲到SQL计划基线

 

 

6.2 SQL 执行计划管理：概述

 

• SQL 执行计划管理是由自动控制SQL计划发展而来
• 优化器自动的管理SQL计划基线

–只有知道和验证过的计划被使用

• 计划变更被自动验证

–只用比较过或者更好的计划在之后使用。

• 可以从SQL性能分析中的STS中表示重要的SQL
• 最主要的收益是通过避免执行计划的回退来保证系统性能的稳定。

 

当一个SQL语句的执行计划变更时有潜在的性能风险。一个SQL计划的变更可以是由多种原因引起，例如优化器版本，优化器统计信息，优化器参数，方案定义，系统配置，和SQL 概要文件的创建。

 

各种计划控制技术（例如存储大纲和SQL概要文件）已经在过去的oracle数据库版本中介绍过，来解决由于计划的变化带来的性能下降问题。但是，这些技术都非活动进程，需要人工来干预。

 

SQL计划管理是oracle11g中介绍的一个新特性，可以让系统自动的控制SQL计划，从管理 SQL 计划基线演化而来。当这个特性被开启，一个新生产的SQL 计划，如果被证明这样做不会导致性能回退，那么可以整合为一个SQL 计划基线。在执行一个SQL语句期间，只有对应为SQL计划基线的一部分的计划能被使用。和在这课之后描述的那样，SQL 计划基线可以自动的加载或可以被作为SQL 优化集的种子。在这个课程的后面将覆盖多个场景。

 

SQL 计划管理特性主要的好处是通过避免计划回退来保证系统性能的稳定性。除此之外，其节省了DBA的时间，通常是用来识别和分析SQL性能的下降和寻找可行的解决方案。

6.3 SQL 计划基线：体系结构

 

SPM特性介绍了支持维护计划和验证新计划性能的必要设施和服务。

对于那些执行不止一次的SQL语句，优化器会为单独的SQL语句维护一个计划历史记录。优化器通过维护的一个语句日志识别一个重复的SQL语句。一个SQL 语句在被记录后，再一次解析或执行时被识别为重复的。在一个语句被识别为重复后，多种计划被优化生成，并被作为一个历史计划维护其相关的信息（例如SQL文本，大纲，绑定变量，和编译环境），可以被优化器用来重现一个执行计划。

 

作为自动识别重复SQL语句和创建它们的计划历史的替代和补充，手动将SQL语句引入到计划中也是支持的。随着时间的推移，一个计划历史会包含，由优化器对一个SQL语句生成的不同的计划。但是，在计划历史中只有一些计划是被接受可用的。例如，一个新的执行计划被优化器产生，其不会正常使用直到它被验证不会导致性能回退。计划验证作为自动SQL优化的一部分，并作为一个维护窗口的自动任务是非常好的。

一个自动SQL优化任务的目标仅仅是那些高负载的SQL语句。为了它们，其自动的实施行动，例如将一个成功验证的计划变为一个接受的计划。一组可接受的计划构成了一个SQL计划基线。一个SQL语句生成的最快的计划显然是可以被接受的；同时，它形成了原始计划基线。随后被优化器发现的任何新计划，被最为计划历史的一部分，但是不会是最初计划基线的一部分。

语句日志，计划历史，和计划基线被存储在SQL 管理基地中（SMB），其也包含了SQL 概要。SMB作为数据字典的一部分，存储在SYSAUX表空间。SMB是自动空间管理的(例如，定期清理未使用的执行计划).你可以配置SMB，改变其计划保留期策略和设置空间大小限制。

 

注意：在oracle数据库11g，如果数据库实例是启动的，但是SYSAUX表空间是离线的，优化器不能访问SQL管理器的对象。这个会影响到一份SQL工作负载的性能

 

 

6.4 加载SQL计划基线

 

你可以使用下列方法加载SQL计划：

• 自由捕获：

通过配置OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES为TRUE来使用自动计划捕获。这个参数的默认值是FALSE。设置为TRUE打开自动识别重复的SQL语句和自动为这些语句自动创建计划历史。在图示左边显示说明，你可以看到第一次生成SQL计划自动被收集到最初的SQL计划基线。

• 批量装载：

使用DBMS_SPM包，可以让你手动的管理SQL计划基线。使用这个包，你可以直接从游标缓存或现有的STS中装载SQL计划到一个SQL计划基线中。从STS中将一个SQL语句的计划装载到衣蛾SQL计划基线，这个语句需要将其执行计划存储到STS。DBMS_SPM允许你将一个基线计划的状态从接受改变到不接受（或从不接受到接受）。其也允许你从一个临时表导出基线计划，然后这些SQL计划基线可以用来在其他数据库中装载。

• 从存储大纲中迁移：使用DBMS_SPM.MIGRATE_STORED_OUTLINE函数来把一个或多个SQL语句从概要大纲中迁移到SMB中。额外的信息将在本课程之后讲到。

 

6.5 演化 SQL 计划基线

在SQL计划基线演化阶段，oracle 数据库定期评估新计划的性能和将性能更好的计划集成到SQL计划基线中。当优化器发现了一个SQL语句的新计划时，这个计划被作为不接受的计划加入到计划历史记录。然后验证这个计划和相对的SQL计划基线的性能。当这个未接受的计划被验证不会引起性能回退时（包含手动或自动），这个计划被变更为接受的计划，并被集成到SQL计划基线。成功的对不接受的计划验证，是从SQL计划基线中选取一个计划，然后比较2者之前的性能，确保被验证的计划有更好的性能。

这里有2种方式来演化SQL计划基线：

• 通过使用DBMS_SPM.EVOLVE_SQL_PLAN_BASE函数。一个调用的例子是在图示中显示的。这个函数返回了一个报告，告诉你现存的一些历史计划是否被移动到计划基线。你也可以在历史中指定特殊的计划来进行测试。
• 通过运行SQL优化指导：SQL 计划基线可以被手动演化或者使用SQL 优化指导来自动的优化SQL语句。当SQL优化指导发现一个调整的计划，并验证其性能优于从SQL计划基线中选择的相应的计划，它会产生一个建议来接受一个SQL概要。当这个SQL概要被接受，被调整的计划被添加到相应的SQL计划基线。

 

 

6.6 查看重要的基线 SQL 计划属性

SELECT signature, sql_handle, sql_text, plan_name, origin, enabled,
accepted, fixed, autopurge
FROM dba_sql_plan_baselines;

 

exec :cnt := dbms_spm.alter_sql_plan_baseline(sql_handle => 'SYS_SQL_37e0168b0…3efe', plan_name => 'SYS_SQL_PLAN_8dfc352f359901ea', attribute_name => 'ENABLED', attribute_value => 'NO');

 

 

你可以通过使用DBA_SQL_PLAN_BASELINES视图来查看每个计划的属性，如图所示。你可以使用DBMS_SPM.ALTER_SQL_PLAN_BASELINE函数来变更其中一些属性。你也可以通过使用DBMS_SPM.DROP_SQL_PLAN_BASELINE函数来移除一个计划或者整个计划历史。上面的例子是变更SYS_SQL_PLAN_8DFC352F359901EA计划的ENABLED属性为NO

关于属性的附加信息在下一节给出。

注意：DBA_SQL_PLAN_BASELINES视图包含一些额外的视图，使你可以明确每个计划的最后使用和一个计划是否将被自动清理。

 

 

6.7 重要基线SQL计划属性

 

• 起源：

-AUTO-CAPTURE:自动的被捕获

-MANUAL-LOAD：手动演化

-MANUAL-SQLTUNE：自动的被SQL 优化指导演化

-AUTO-SQLTUNE:自动的被自动SQL优化演化

 

• ENABLES:计划已被优化器启用使用
• ACCEPTED:计划被验证为是一个好的计划
• FIXED:优化器只考虑这些计划

 

当一个计划进入到计划历史，它于一些重要的属性关联：

• SIGNATURE, SQL_HANDLE, SQL_TEXT, 和 PLAN_NAME是重要的搜索标识。
• ORIGIN 允许你来确认计划是否是被自动捕获（AUTO-CAPTURE），或者手动演化（MANUAL-LOAD），自动的被SQL 优化指导演化（MANUAL-SQLTUNE），或者自动的被SQL 优化指导演化（AUTO-SQLTUNE）。
• ENABLED和ACCEPT:ENABLES属性意味着计划被优化器启用使用。如果ENABLED没有设置，这个计划将不会被考虑。ACCEPTED属性意味着这个计划是被验证为一个好计划，既可以被系统自动或者用户手动变更其为ACCEPTED。当一个计划变更为ACCEPTED,它将只能被DBMS_SPM.ALTER_SQL_PLAN_BASELINE()来修改为非ACCEPTED.一个ACCEPTED计划可以通过移除ENABLED配置，临时不启用。一个计划必有ENABLED和ACCEPTED,优化器才会考虑使用它。
• FIXED 意味着优化器仅考虑这些计划，不考虑其他的计划。例如，如果你有10个基线计划，其中3个标记为FIXED,优化器仅使用这3个中最优的，而忽略其他的。一个SQL计划基线被称为FIXED，如果其包含至少一个启用的固定计划。如果一个新计划被添加到一个固定的SQL计划基线，这些新计划不能被使用，知道他们被手动表明为FIXED.

 

6.8 SQL计划选择

 

 

 

如果你使用自动计划捕获，第一次执行一个SQL语句被认为是重复的，其最好的计划被添加到相应的SQL计划基线。这个计划然后被执行语句使用。当OPTIMIZER_USE_SQL_PLAN_BASELINES初始化参数设置为TRUE时，优化器使用一个比较计划选择策略。每个SQL语句编译时刻，优化器先使用传统的成本-基础搜索方法来构建一个最佳成本计划。然后，它视图在SQL计划基线中找到一个匹配的计划。如果匹配到，这个过程和普通情况一样。如果没有匹配到，它首先在计划历史中增加新计划，然后得到SQL计划基线中每个接受的计划进行成本，再从中选择一个最低的。这个被接受的计划使用存储在其中的大纲重建。所以一个SQL语句的SQL计划基线的作用是优化器从SQL计划基线中选择的一个接受的计划。

使用SQL 计划管理（SPM），优化程序可以产生一个计划，它可以是一个最佳的成本计划或基线计划。这些信息通过EXPLAN PLAN被转储在PLAN_TABLE的OTHER_XML字段。

除此之外，比可以使用新函数DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE函数来显示一个或多个被指定sql_handle计划基线的执行计划。如果PLAN_NAME也被指定，那么相应的执行计划被显示。

注意：为了保持向后兼容，如果一个存储大纲在用户会话中的一个SQL语句中活动，这个语句将使用存储大纲来编译。除此之外，即使自动计划捕获在这个会话被开启，优化器生成的计划还是会使用存储大纲，并且不会存储到SMB。

 

 

6.9 练习

 

下来哪种方式可以加载SQL计划基线？

a.通过设置OPTIMIZER_CAPTURE_SQL_PLAN_BASELINE 为TRUE自动

b.使用DBMS_SPM包从SQL性能分析STS中

c.使用DBMS_SPM包从游标缓存中

d.默认自动的

答案：a,b,c

 

 

6.10可能的SQL计划管理方案

 

 

 

• 数据库升级：当系统被从早期版本升级到Oracle数据库11g时，批量加载SQL计划特别有用。为此，你可以在升级之前，捕获计划为工作负载到STS中，然后在升级之后，立即将这些计划从STS中加载到SQL计划基线。这种策略可以最大限度地减少，使用新的优化器版本导致的计划回退。
• 新应用程序部署：部署一个新的应用程序模块，意味着在系统中引进新的SQL语句。软件供应商可以把应用软件和新的SQL引入适当的SQL计划基线。由于计划基线，新的SQL语句将最初使用，在标准测试配置中提供最好性能的计划。但是，如果用户系统的配置和测试配置有很多的不同，计划基线可以随着时间的演变，产生更好的性能。

在这两种情况下，你可以使用自动SQL计划捕获之后手动加载，来确保只有更好的计划将在未来用于你的应用程序。

注意：在这个课程中的所有场景中，假定OPTIMIZER_USE_SQL_PLAN_BASELINES设置为TRUE

 

 

 

6.11 SQL性能分析和SQL计划基线场景

 

一个在前面的幻灯片介绍的第一种方法的变化，是通过SQL性能分析，使用如下：

1.你可以捕获每个oracle 11g数据库的计划到一个STS，然后倒入到oracle数据库11g.

2.然后设置OPTIMIZER_FEATURE_ENABLE 初始化参数为10.2来设置优化器行为作为oracle 10g 数据库。

3.下一步为STS运行SQL优化分析。

4.当执行完成时，设置OPTIMIZER_FEATURE_ENABLE 初始化参数回到11.2，然后重新为STS运行SQL优化分析。

 

SQL性能分析产生了一个报告，列出了那些从10g到11g回退SQL语句的执行计划。通过SQL性能分析显示这些由于新优化器版本导致回退的sql语句，你可以使用STS捕获它们的执行计划，然后加载他们到SMB。

这种方法是最好的计划-引入过程，因为它有助于防止性能回归，同时在数据库升级时，保留性能改进。

 

 

 

 

 

6.12 自动加载一个SQL计划基线

 

自动加载一个SQL计划基线：场景

 

另外一个升级场景涉及到使用自动SQL计划捕获原理。在这个场景中，为每一个预oracle数据库11g设置OPTIMIZER_FEATURE_ENABLE初始化参数值为一个初期的值，例如四分之一，然后在升级之后使用自动SQL计划捕获执行你的负载。

在最开始的时候，由于OPTIMIZER_FEATURE_ENABLE参数的设置，优化器对于大多数SQL语句来说，能够重新生产预11g的计划。因为开启了自动捕获，这些计划也hi被自动的捕获为SQL计划基线。

当初始化时间结束，移除OPTIMIZER_FEATURE_ENABLE的配置，利用新的优化器版本来引入最小或没有回退的计划基线。回退的计划将使用之前的优化器版本；无回退的语句将从新版本的优化器中获益。

 

 

6.13 清理SQL 管理器的基本策略

SQL> exec dbms_spm.configure('SPACE_BUDGET_PERCENT',20);
SQL> exec dbms_spm.configure('PLAN_RETENTION_WEEKS',105);

↓

     DBA_SQL_MANAGEMENT_CONFIG

 

exec :cnt := dbms_spm.drop_sql_plan_baseline('SYS_SQL_37e0168b04e73efe');

 

SMB管理的空间每周检查一次看是否达到定义的限制。这个限制是一个基于SYSAUX表空间的百分比。默认情况下，SMB的空间限制为10%。但是，你可以使用DBMS_SPM.CONFIGURE存储过程设置SMB，并且可 以从从1%到50%修改这个预估的值。

如果SMB空间超过了定义的百分比限制，告警将写入alert.log。警告每周都会生成，直到SMB空间限制增加，SYSAUX表空间增加或者通过清理一些SQL管理对象来降低SMB大小（例如SQL计划基线或者SQL概要）

管理SQL计划基线的空间会主动使用周清理作业。这个作业在维护窗口运行一个自动的任务。任何没有使用超过53周的计划被清理。但是，你可以配置SMB来改变计划保留时间，这个值从5周到532周（超过10年）。要这样做的话，使用DBMS_SPM.CONFIGURE存储过程。

你可以从DBA_SQL_MANAGEMENT视图中看到当前SMB的配置。除此之外，你可以手动的使用DBMS_SPM.DROP_PLAN_BASELINE函数手动的清理SMB（如图所示的例子）

 

 

 

 

 

 

6.14 EM和SQL计划基线

 

 

在服务器页的查询优化区域中，点击SQL计划控制，访问SQL计划控制页面。

使用SQL 计划控制页面来管理SQL概要，SQL 补丁，和SQL计划基线，这个是在EM的同一个位置，而不是不同的位置。你也可以开启，禁止，删除，打包，解压，加载和演化选中的基线。

在这个页面，你也可以配置各种SQL计划基线参数。

 

 

6.15 使用MIGRATE_STORED_OUTLINE 函数

 

• 指定存储大纲要迁移的基于大纲的名字，SQL文本，或大纲的分类，或迁移所有存储大纲到系统SQL计划基线：

 

DBMS_SPM.MIGRATE_STORED_OUTLINE (
attribute_name IN VARCHAR2,
attribute_value IN CLOB,
fixed IN VARCHAR2 := 'NO')
RETURN CLOB;

 

• 指定要迁移的一个或多个存储大纲：

 

DBMS_SPM.MIGRATE_STORED_OUTLINE (
outln_list IN DBMS_SPM.NAME_LIST,
fixed IN VARCHAR2 := 'NO')
RETURN CLOB;

 

你可以使用DBMS_SPM.MIGRATE_STORED_OUTLINE函数来为一个或多个SQL语句迁移存储概要到SMB中的计划基线。指定存储大纲迁移基于大纲的名字，SQL文本，或大纲分类。你也可以迁移所有存储大纲到系统SQL计划基线。参数如下：

• attribute_name:指定attribute_value使用的参数类型，来表示迁移的存储大纲。值都是outline_name,sql_text,category和all
• attribute_value:基于attribute_name指定来指定值.如果attribute_name是all则值为NULL。
• fixed：值是NO（默认）和YES。指定计划在迁移中产生的”fixed”状态。默认情况下，计划被产生为”no-fixed”计划。

一个CLOB被返回，其中包含了在迁移过程中描述统计数据的格式化报表

 

 

6.16 总结

在这个课程中，你将学会如何：

• 设置SQL计划管理
• 设置各种SQL计划管理方案
• 迁移概要大纲到SQL计划基线