沃趣科技·刘金龙

原文链接 ORACLE WHITE PAPER SQL Plan Management with Oracle Database 12c Release 2

概 述

任何数据库应用程序的性能严重依赖于一致性查询语句的执行。而oracle优化器非常适合在没有用户干扰的情况下产生最佳执行计划。但SQL语句的执行计划会有不可预测的变化，原因包括重新收集优化器统计信息，更改优化器参数或对象元数据定义。由于缺少一个执行计划的改变总是好的保证，一些客户会选择固定他们的SQL执行计划（stored outlines）或者锁定优化器统计信息。然而这样做就会影响他们应用新的优化器功能（比如新的访问路径），这些对改进都是执行计划有帮助的。解决这个难题的最理想方法就是在环境中保留当前的执行计划，并且只在有更好的执行计划的时候再去做改变。

SQL计划管理（SPM）提供了这样一个框架，并允许完全控制计划的演化 。优化器使用SPM自动管理执行计划并且确保已知的和已经验证过的执行计划被使用。当SQL语句的新执行计划被发现后，它不会被立马使用，直到它被验证过比现有的执行计划更好后才会被使用。

本文提供了一个深入的解释，SPM如何工作并且为什么它是每个DBA工具箱重要的一部分。本文分为三个部分，第一部分是描述SPM基础，以及他们是如何协同工作以提供一个稳定受控的执行计划演化过程。第二部分讨论了SPM如何与影响优化器计划选择的其他Oracle数据库特性协同合作。最后一部分提供了如何一步一步使用SPM在一些艰难的任务中比如升级提升数据库和应用的性能。

SQL计划管理

SQL计划管理（SPM）确保运行时性能不会因执行计划更改而降低。为了保证这一点，SPM只接受已执行的执行计划；所有后来的执行计划演进都会被进行跟踪和评估，并且只有在新计划显示在运行时要优于原有计划，该计划才会被接受。SQL计划管理主要有三部分组成：

• 计划捕捉：为所有SQL语句所接受的执行计划创建SQL计划基线。SQL计划基线做为SQL管理基础存储在 SYSAUX表空间。

• 方案选择：确保只接受在SQL计划基线中被记录的新计划，这些新计划是在SQL计划基线中没有被接受的那些计划。

• 计划演变：为一个给定的语句评估所有未执行的计划，只有计划在显示性能比在SQL计划基线中的执行计划高的时候才会被接受。

SQL管理基础

SQL管理库（SMB）是在数据字典中的一个逻辑库，物理存储在SYSAUX表空间中，它存储结构如下：

• SQL计划历史：SQL计划历史是SQL语句的执行计划的SQL语句时产生的。

• 历史包含SQL计划基线和接受计划。

• SQL计划基线：SQL计划基线是一个被优化器认可的SQL语句的执行计划。在一般情况下，只有在验证新的计划执行良好之后，数据库才接受计划进入计划基线。

• SQL语句日志：一系列标识某条一次或者多次执行的SQL语句的信息，它会在执行的时候自动被捕捉（见下文）。

计划收集

要激活SPM，SQL管理基础必须有一组可接受的执行计划，这些计划将成为相应SQL语句的SQL计划基线。有两种不同的方法来生成SQL管理基础：自动的或手动的。

自动计划收集

可以使用初始化参数optimizer_capture_sql_plan_baselines启用（默认为false）自动计划收集。当自动计划收集被启用后，任何重复执行的SQL语句都会被自动创建SQL基线。为了识别可重复的SQL语句，每个SQL语句在第一次编译时优化器会记录SQL签名（从标准化SQL文本生成的唯一SQL标识符）。SQL签名存储在sql管理库中的SQL语句日志中。

如果SQL语句再次执行，那么在语句的日子中就会标记它为一条重复执行的语句，然后SQL基线就会被创建，该基线信息包括能够使优化器为当前语句生成基于cost的执行计划所需的的所有信息，如：SQL文本、outline、绑定变量以及编译环境。这个初始的执行计划会被标记为接受状态。如果后续有新的执行计划被发现，SQL基线会记录这个执行计划但是不会标记为接受状态。

默认情况下不启用自动计划捕获，因为它将为系统上执行的每一个可重复SQL语句创建一个SQL计划基线，包括所有监视和递归SQL语句。在一个非常繁忙的系统，一些不必要的SQL计划基线可能会充满SYSAUX表空间。而且基线第一次收集到的的一些SQL语句的执行计划并不是最高效的。因此，只有当关键SQL语句按预期的生成默认计划，才会在启用自动计划捕获。

oracle数据库12CR2版本针对那些SQL语句被收集做了过滤和限制。例如，图1显示怎样针对目标数据库用户进行设置，以及视图显示设置的信息。

图1：配置和显示SPM配置信息

手工计划收集

当数据库正在从以前的版本升级时，或者部署新应用程序时，将计划手动加载到SPM中是填充SQL计划基线最常用的和非常有用的方法。手动加载可以与自动计划捕获结合使用，也可以用于单个语句或应用程序中的所有SQL语句。执行计划被手动加载后数据库自动创建新的SQL计划基线，或者作为已接受的计划添加到现有SQL计划基线中。可以通过四种不同的方式手工加载计划，使用dbms_spm包或通过Oracle企业管理器使用的功能（EM）进行配置：

• SQL Tuning集

• 游标缓存 AWR资料库（12CR2版本）

• 从staging表取出

• 基于现有的outline

SQL Tuning集

一个SQL Tuning集是包含一条或多条SQL语句他们的执行统计和执行上下文（执行计划可以包含的所有信息）的数据库对象。我们可以将它从一个数据库导入到另外一个数据库中。我们可以用DBMS_SPM.LOAD_PLANS_FROM_SQLSET过程将一个或者多个计划从STS加载到SPM中。手工从STS加载的执行计划会被自动接受

游标缓存

通过使用SQL语句文本、模块名、SQL_id或者解析树过滤一个SQL并通过DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE将执行计划可以直接从游标缓存加载到SQL计划基线中

AWR资料库

Oracle数据库12cR2版本增加了从AWE资料库导入SQL基线的功能，通过使用DBMS_SPM.LOAD_PLANS_FROM_AWR过程。计划可以指定的AWR快照开始和结束的时间，在共同与其他人工方法，过滤器可以用于限制SQL语句选择要存储为SQL计划基线。 并且协同其他手工方式指定需要存储为基线的SQL语句。

staging表

正如可以将优化器统计数据从一个数据库系统传输到另一个数据库系统一样，也可以通过staging表传输SQL计划基线。SQL计划基线可以使用dbms_spm.pack_stgtab_baseline过程打包成一个staging表。然后用数据泵从一个数据库导入到另外一个数据库。一旦表被导入，我们就可以使用DBMS_SPM.UNPACK_STGTAB_BASELINE包将该表解打包。一旦解包，SQL计划基线将变成ACTIVE状态，并在SQL语句下次执行的时候使用。

图2：将SQL计划基线从一个数据库复制到另一个数据库

在Oracle数据库的早期版本中，outline是保存执行计划的唯一方式。使用outline，只有一个计划可以用于给定的SQL语句，并且不可能进行计划演化。存储在Oracle数据库11g过时了，Oracle强烈建议任何现有的存储的outline都要迁移到SPM。

可以使用dbms_spm.migrate_stored_outline过程。可以指定名称，类型或者相关的SQL文件来迁移指定的outline，也可以直接将所有的outline全部的迁移。

为现有基线捕获新增计划

不管我们最开始使用什么方式创建的SQL计划基线，随后任何新的执行计划都会以一种不被接受的方式被添加到计划基线中。这个特性并不受初始化参数OPTIMIZER_CAPTURE_SQL_PLAN_ BASELINES的影响，即使这个参数被设置为FALSE(默认情况)。这些新增的计划都不会被使用，直到这些计划被验证比现有SQL计划基线中存储的执行计划效率更高。

计划选择

每次编译SQL语句时，优化器首先使用传统的基于成本的方法来评估出基于成本的最佳执行计划。如果初始化参数OPTIMIZER_USE_SQL_PLAN_BASELINES被设置成TRUE，那么基于成本的执行计划优化器会检查该语句SQL计划基线的存在。SQL语句使用SQL语句的签名与SQL计划基线匹配。这个签名是从标准SQL文本（未封装的和无空格）产生的一个对sql的l唯一性标识。这与SQL profile和SQL补丁使用的技术相同。这种比较是在内存中操作进行的，因此对任何应用程序都没有明显的开销。

如果SQL语句已经存在已接受的SQL计划基线，那么生成的基于成本的计划将与SQL计划基线中的计划进行比较（使用计划哈希值检测匹配）。如果找到匹配项，并且SQL计划基线处于接受状态，优化器将继续执行此计划。如果没有发现匹配，新生成的计划添加到SQL计划基线作为一个不接受的计划。在它被接受之前，它必须被验证过。优化器不执行新生成的计划，而是为SQL语句的每个接受计划计算成本代价，并选择最低成本的计划（请注意，SQL计划基线可以为给定语句提供多个可接受的计划）。然而，如果系统发生变化（如索引被删除）导致所有的接受计划成为不可接受的状态，优化器将使用新生成的基于成本的计划，这个计划将作为一个不合格的计划存储在SQL计划历史。

图3：执行计划选择工作流程图

当从SQL基线中选择计划的时候，优化器的选择也可能会受到影响。在基线中一条或者多条计划会被标记为固定的。固定的计划像优化器表名自己是首选的。优化器将选择这些固定计划成本消耗成本最低的那个，除非没有固定的计划可以选择。在这种情况下，优化器会选择SQL基线中剩余的（已经被接受的并且没有被固定）计划，并选择其中一条消耗最小的计划。

注意，计算一个现有的计划并不像一个完全基于成本的优化那样昂贵。优化器并不是在寻找所有可能的替代方案，而是在计划中指定的特定方案，比如给定的访问路径。

如果在SQL基线包含了一个固定计划，那么新的计划并不会被加入到基线中，即便在解析阶段发现了新的基于成本的计划。

计划演变

当优化器发现一个SQL语句的新计划，该计划被添加到SQL计划基线作为一个不接受的计划，需要验证才可以成为一个接受状态的计划。验证是对未接受计划和最佳接受计划（最低成本计划）执行性能进行比较的过程。如果未接受计划的性能更好，那么他将会被自动接受。否则，它还是会被标记为未被接受的但是last_verified属性将会被更新为当前时间。在Oracle数据库12c的计划自动演化（见下文）如果自上次验证通过至少30天将会再考虑这个计划（只要SQL仍在执行）。我们可以使用dbms_spm.alter_sql_plan_baseline过程进行禁用这种演变，但oracle建议启用并继续允许优执行计划验证的自动演化。毕竟，如果对一个系统做出了改变，那么一个备选方案可能会被证明是更优的。

演变使用的性能标准与SQL调试顾问和SQL性能分析器所使用的标准相同。根据每一个测试执行时间、CPU时间和缓冲区，计算出总性能统计数据。然后对性能统计数据进行比较，如果新计划比现有的计划提高了1.5，那么它将被接受。

演变的结果都会被记录在数据字典中，可以在任何时候使用dbms_spm.report_evolve_task查看这些信息。

图4：SQL演变报表

演变报表描述了对哪些计划进行了测试，并对每个执行的性能标准进行了比较。做为报表的一部分，它会清楚的描述对比的过程，以及是否应该接受新的计划。最后，有一个建议部分，详细说明下一步该做什么，包括必要的语法。如图4演变报表。当新的未被接受的计划性能1最后，有一个建议部分，详细说明下一步该做什么，包括必要的语法最后，有一个建议部分，详细说明下一步该做什么，包括必要的语法最后，有一个建议部分，详细说明下一步该做什么，包括必要的语法。如图4,sql演变报表，如果新的未被接受的执行计划1.5倍于原有执行计划，那么他将被接受并且被存储为新的SQL计划基线。

与计划捕获一样，计划演化可以自动或手动完成。

自动计划演变

从Oracle数据库12C版本1开始，自动计划的演变是由SPM演变顾问执行。SPM是一个自动任务(SYS_AUTO_SPM_EVOLVE_TASK), 这些操作会在夜晚自动维护窗口自动运行。自动维护窗口会为所有未被接受的计划进行排名（新发现的计划优先级最高），然后开始进行计划演变，并且尽可能在自性能不达标那么LAST_VERIFIED属性会被更行。然后再过30天后并且SQL语句是活跃的（LAST_EXECUTED属性被更新）。那么他就会被夜间自动维护任务执行。

手动计划演变

事实上，可以使用EM或者dbms_spm包手动对未被接受的计划进行演变。从oracle数据库12CR1版本开始，起初的SPM演变函数(DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE) 已经被替换成SPM演变顾问。图5显示了执行SPM演变顾问的步骤。执行演变顾问通常有三个步骤。每个任务都有一个唯一的名称，它可以多次执行。一旦任务被执行，我们可以通过dbms_spm.report_evolve_task功能提供任务名称和执行的名字查询演变报告。

图5：手动执行SPM演变顾问

当手动调用SPM发展顾问，未被接受的计划并不会自动被接受，即使已经达到性能要求。计划必须手工使用DBMS_SPM.ACCEPT_SQL_PLAN_BASELINE过程来完成接受操作。演进报告包含详细的接受这些计划说明，包括特定的语法。

图6：手动接受一个计划

注意‘Administer SQL Management Object’ 权限是手工演变计划所需的权限。

管理监控SQL计划基线

所有管理和监控SQL计划基线都可以通过oracleEM完成或者使用PL/SQL包 DBMS_SPM, DBMS_XPLAN 和DBA视图DBA_SQL_PLAN_BASELINES。

oracle EM

访问SQL计划基线页：

• 访问企业管理器数据库主页。

• 在页面的顶部，点击“性能”选项卡，从下拉列表选择SQL计划控制。

• SQL计划控制页面。

• 在页面的顶部，点击SQL计划基线表显示SQL计划基线的子页面。

初始化参数

有两个初始化参数控制SPM

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES。控制为重复执行的SQl语句自动创建SLQ计划基线。默认情况下参数为FALSE。注意，没有必要为了将新发现的计划添加到SQL计划基线中将该参数设置为true。

如果sql语句有多个计划，则所有这些计划都将被捕获，但是只有第一个将被接受。如果你不想自动收集的计划被做为基线。可以设置参数optimizer_use_sql_plan_baselines为false。

OPTIMIZER_USE_SQL_PLAN_BASELINES。用于控制SQL计划基线的使用。当启用时，在SQL解析期间优化器会检查该SQl是否有SQL计划基线。如果找到了SQL计划基线，而基于成本的计划在基线中是一个被接受的计划，那么优化器将继续使用该计划。但是，如果在基线中找到SQL计划基线，而基于成本的计划不是一个被接受的计划，那么它将被添加到SQL计划基线中，但不会执行。

优化器会计算在SQL计划基线中存储的所有接受的计划，并选择其中一条成本最低的执行。默认情况下该参数为TRUE。当该参数被设置为FALSE，优化器将只使用解析期间确定的基于成本的计划（SQL计划基线将被忽略），并且不会将新计划添加到现有SQL计划基线中。

这些参数值可以在会话或系统级的命令行中使用“alter session set ”或“alter system set ”进行更改。还可以调整EM企业管理器中的主SQL计划基准页（设置部分）的左上角的参数设置。

管理SQL管理基线的空间

语句日志和所有SQL计划基线都存储在SQL管理基线库中。SQL管理的基线库是数据库字典的一部分，存储在SYSAUX表空间。默认情况下，SQL管理基线库的空间被限制为不超过SYSAUX表空间大小的10%。当然这个限制可以通过oracle的EM或者DBMS_SPM.CONFIGURE过程进行更改，值为1%到50%.每周后台进程测量SQL管理基线库占用的总空间，当超出定义的限制时，进程将在警报日志中生成警告，例如：

SPM: SMB space usage (99215979367) exceeds 10.000000% of SYSAUX size (1018594954366).

达到这个限度不会阻止新计划被添加到现有SQL计划基线或添加新SQL计划基线到SQL管理基线库中。

达到限制也不能阻止新的计划被添加到SQL计划基线或者添加到现有的SQL计划基线到SQL管理库。还有一个每周定期清理进程（mmon）管理SPM在SQL管理库中使用的磁盘空间。任务自动运行和清洗，不使用超过53周的计划，在每一个运行dbms_spm.drop_sql_plan_baseline功能。可以通过使用dbms_spm.configure或企业经理变更未计划停留时间；它的值的范围可以从5到523周（不超过10年）。定时任务会使用DBMS_SPM.DROP_SQL_PLAN_BASELINE过程自动清理超过53周没有使用的SQL基线。当然也可以使用DBMS_SPM.CONFIGURE过程或者EM改变未被使用计划基线的保留时间。值范围从5到523周（10年）之间。

SQL管理库是完全存储在SYSAUX表空间，所以当这个表空间不可使用时SPM也不能被使用。

监控SQL计划基线

可以通过查看dba_sql_plan_baselines显示数据库中当前的SQL计划基线信息。Oracle企业管理器中SQL计划基线页的底部也可以显示相同的信息。

图8：使用字典视图dba_sql_plan_baselines监测SQL计划基线

在图8的示例中，同一SQL语句在SQL计划基线中有两个计划。两个计划都是自动捕捉的，但是只有计划(SQL_PLAN_c2bg7p288wk7b18498f6e)会被使用，另外一个是未被接受的计划。这意味着它一直不会被使用除非它已经被验证过。

可以使用dbms_xplan.display_sql_plan_baseline过程或者ORACLE企业管理器EM去监控所有SQL计划基线的详细执行计划。在Oracle数据库11g，执行这个函数会引发编译使用存储在SQL管理基线库的信息。这种情况在12C数据库中也可能发生，当这个计划是在11g数据库中被创建的。从12CR1版本开始Oracle会向SPM添加新计划时会捕获实际计划行。这意味在Oracle 12C数据库中， 当计划被添加到SQL计划基线时，DISPLAY_SQL_PLAN_BASELINE 会显示实际的计划数据信息。

捕获实际的执行计划确保如果SQL计划基线从一个系统转移到另一个系统，SQL计划基线中的计划仍然可以被显示，即使其中的一些对象或解析模式本身不存在于新系统上。这意味着即使不能执行，也可以显示计划。

图9显示了图8所接受的计划的执行计划。所示的计划是将该计划添加到SQL计划基线时捕获的实际计划，因为属性’Plan rows’ 被设置为‘From dictionary’。也可以设置属性’Plan rows’ 被设置为From outline’。

图9：从SQL计划基线中显示一个已接受的计划

还可以通过v$sql视图检查SQL语句是否使用sql计划基线。如果SQL语句使用一个SQL计划基线，那么plan name 会显示是从SQL 基线得来的。也可以使用plan_name或者exact_matching_signature 列对DBA_SQL_PLAN_BASELINES和v$sql进行关联。

图10：关联v$sql和DBA_SQL_PLAN_BASELINES