作者：maria colgan
你是否经历过因为执行计划变得更差而导致的性能退化？如果是的话，那么我们有一个称为sql plan management（spm)的精致m6米乐安卓版下载的解决方案给到你。接下来的4篇博客将涵盖spm的详情。让我们从回顾执行计划改变的主要原因开始。
执行计划改变的发生可以归因于多种系统变化。比如，你也许（手动或自动）更新了部分对象的统计信息，或者修改了少量优化器相关的参数。而更显著的改变是数据库升级（比如从11g升级到12c)。所有这些变化都会潜在导致很多sql语句生成新的执行计划。大多数新的执行计划都是明显的改进，因为它们是针对新的系统环境而定制的，但有些可能更糟，从而导致性能下降。正是后者导致许多dba彻夜难眠。
处理这些退化问题，dba有几个选项。然而，大多数dba想要的很简单：只有当计划能够带来性能提升时，才应该改变计划。换句话说，优化器不应该挑选坏的执行计划。
在这个系列文章中的第一篇，会描述sql plan management的概念以及如何创建sql plan baselines（sql执行计划基线）。第二篇会描述sql plan baseline是如何以及何时使用。第三篇将讨论演进，即在sql计划基线中添加新的和改进的执行计划的过程。最后，第四部分将描述用户界面以及与其他oracle对象（如存储的大纲）的交互。
sql plan management (spm)允许数据库用户为一组sql语句保持稳定而最佳的性能。spm融合了计划适应性和计划稳定性的优点，同时又避免了它们的缺点。它有两个主要目标：
- 当面对数据库系统变化时，防止性能退化。
- 通过友好地适应数据库系统变化，提供性能改善。
托管sql语句是启用了spm的语句。spm可以配置为自动工作，也可以全部或部分手动控制（稍后介绍）。spm通过启用对托管sql语句的执行计划更改的检测，来帮助防止性能退化。为此，spm在磁盘上维护一个执行计划历史记录，其中包含了为每个托管sql语句生成的不同执行计划。oracle优化器的增强版称为spm感知优化器，它访问、使用和管理存储在名为sql management base（smb）的存储库中的信息。
执行计划历史使spm感知优化器能够确定，使用基于成本的方法生成的最佳成本计划是否是全新的计划。一个全新的执行计划代表了一个有可能导致性能退化的执行计划。基于这个原因，spm感知优化器不会选择一个全新的最佳成本执行计划。相反，它会从一系列已接受的执行计划中进行选择。一个已接受的执行计划是一个已经被证实不会导致性能下降或被指定具有良好性能的执行计划。一组可接受的执行计划称为sql执行计划基线，它是执行计划历史的子集。
全新执行计划将作为未接受计划添加到执行计划历史记录中。稍后，spm实用程序将验证其性能，并将其作为不可接受的执行计划（如果它会导致性能退化），或者将其更改为可接受的执行计划（如果它将提供性能改进）。执行计划性能验证过程确保了计划的稳定性和执行计划的适应性。
下图展示了smb中保存的三条语句的执行计划历史。每一个执行计划历史由已接受的执行计划（sql plan baseline)和未接受的执行计划组成。
你可以通过若干种方法创建sql plan baseline: 通过sql tuing set(sts,sql调优集）；通过缓存的游标；从一个库中导出然后导入到另一个库；自动为每条语句创建。让我们逐一看一看。此博客中的示例使用了oracle数据库示例schema，因此您可以自己尝试它们。
如果正在升级，则您可能已经有一个包含部分或全部sql语句的sts。该sts可能包含令人满意的执行计划。我们把这个sts称为 my_sts。可以从该sts创建sql计划基线，如下所示：
sql> variable pls number;
sql> exec :pls := dbms_spm.load_plans_from_sqlset(sqlset_name => 'my_sts', -
> basic_filter => 'sql_text like ''select%p.prod_name%''');
这将会为所有符合指定过滤条件的语句创建sql plan baselines.
您可以为当前缓存中的任意游标自动创建sql plan baselines，如下所示：
sql> exec :pls := dbms_spm.load_plans_from_cursor_cache( -
> attribute_name => 'sql_text', -
> attribute_value => 'select%p.prod_name%');
这将会为所有sql文本匹配指定字符串的sql创建sql plan baseline。该函数有多个重载，允许你通过其它游标属性进行过滤。
如果你已经有sql plan baseline了（比如在11g的测试系统中），你可以导出它们到另一个系统中（比如一个生产系统）
首先，在测试系统中，创建一个中间表并将你要导出的sql plan baseline打包：
sql> exec dbms_spm.create_stgtab_baseline(table_name => 'my_stgtab', -
> table_owner => 'sh');
pl/sql procedure successfully completed.
sql> exec :pls := dbms_spm.pack_stgtab_baseline( -
> table_name => 'my_stgtab', -
> table_owner => 'sh', -
> sql_text => 'select%p.prod_name%');
这将会对所有匹配指定过滤条件的语句的sql plan baseline进行打包。中间表my_stgtab是一个常规表，你可以使用数据泵导出的功能将其导到生产系统中。
在生产系统上，就可以解包中间表来创建sql plan baseline：
sql> exec :pls := dbms_spm.unpack_stgtab_baseline( -
> table_name => 'my_stgtab', -
> table_owner => 'sh', -
> sql_text => 'select%p.prod_name%');
这将解包中间表并创建sql plan baseline。注意，解包中间表时使用的过滤条件是可选项，是可以不同于打包时的条件的。这意味着，你可以打包若干个sql plan baseline到中间表，然后仅解包其子集到目标系统中。
您可以通过设置参数optimizer_capture_sql_plan_baselines为true（默认为false)，为所有重复执行过的语句自动创建sql plan baseline。这些语句首次被捕获到的执行计划会自动做为可接受的执行计划并成为sql plan baseline的一部分。因此，只有在你确认这些默认的执行计划性能良好时，才应打开这个参数。
从以前的数据库版本升级后，可以使用自动计划捕获模式。将optimizer_features_enable设置为早期版本并执行工作负载。每个重复执行的语句都将捕获其计划，从而创建sql plan baseline。在确定工作负载中的所有语句都有机会执行之后，可以将optimizer_features_enable重置为其默认值。
请注意，此自动计划捕获仅适用于重复执行的语句，即至少执行两次的语句。只执行一次的语句将不会从sql plan baseline中获益，因为接受的计划只在后续的硬解析中使用。
以下示例显示同一语句执行两次时自动捕获计划：
sql> alter session set optimizer_capture_sql_plan_baselines = true;
session altered.
sql> var pid number
sql> exec :pid := 100;
pl/sql procedure successfully completed.
sql> select p.prod_name, s.amount_sold, t.calendar_year
2 from sales s, products p, times t
3 where s.prod_id = p.prod_id
4 and s.time_id = t.time_id
5 and p.prod_id < :pid;
prod_name amount_sold calendar_year
--------- ----------- -------------
...
9 rows selected.
sql> select p.prod_name, s.amount_sold, t.calendar_year
2 from sales s, products p, times t
3 where s.prod_id = p.prod_id
4 and s.time_id = t.time_id
5 and p.prod_id < :pid;
prod_name amount_sold calendar_year
--------- ----------- -------------
...
9 rows selected.
sql> alter session set optimizer_capture_sql_plan_baselines = false;
session altered
如果一个语句存在存储大纲，且参数use_stored_outlines为true，那么自动计划捕获并不工作。在这种情况下，使用dbms_sqltune包中的函数capture_cursor_cache_sqlset（）将计划增量捕获到sts中。在将工作负载的执行计划收集到sts中之后，使用前面描述的方法手动创建sql计划基线。然后，禁用存储大纲或将use_stored_outlines设置为false。从现在起，spm将管理您的工作负载，存储大纲将不会用于这些语句。
在本文中，我们了解了如何创建sql plan baseline。下一步，我们将描述spm感知优化器是如何使用sql plan baseline的（第二篇 sql plan management (4-2) spm感知优化器）。
原文链接：
原文内容：
sql plan management (part 1 of 4) creating sql plan baselines
maria colgan
master product manager
introduction
do you ever experience performance regressions because an execution plan has changed for the worse? if you have, then we have an elegant solution for you called sql plan management (spm). the next four posts on our blog will cover spm in detail. let’s begin by reviewing the primary causes for plan changes.
execution plan changes occur due to various system changes. for example, you might have (manually or automatically) updated statistics for some objects, or changed a few optimizer-related parameters. a more dramatic change is a database upgrade (say from 11g to 12c). all of these changes have the potential to cause new execution plans to be generated for many of your sql statements. most new plans are obviously improvements because they are tailored to the new system environment, but some might be worse leading to performance regressions. it is the latter that cause sleepless nights for many dbas.
dbas have several options for addressing these regressions. however, what most dbas want is simple: plans should only change when they will result in performance gains. in other words, the optimizer should not pick bad plans, period.
this first post in our series, describes the concepts of sql plan management and how to create sql plan baselines. the second part will describe how and when these sql plan baselines are used. the third part will discuss evolution, the process of adding new and improved plans to sql plan baselines. finally, the fourth part will describe user interfaces and interactions with other oracle objects (like stored outlines).
sql plan management
sql plan management (spm) allows database users to maintain stable yet optimal performance for a set of sql statements. spm incorporates the positive attributes of plan adaptability and plan stability, while simultaneously avoiding their shortcomings. it has two main objectives:
- prevent performance regressions in the face of database system changes
- offer performance improvements by gracefully adapting to database system changes
a managed sql statement is one for which spm has been enabled. spm can be configured to work automatically or it can be manually controlled either wholly or partially (described later). spm helps prevent performance regressions by enabling the detection of plan changes for managed sql statements. for this purpose, spm maintains, on disk, a plan history consisting of different execution plans generated for each managed sql statement. an enhanced version of the oracle optimizer, called spm aware optimizer, accesses, uses, and manages this information which is stored in a repository called the sql management base (smb).
the plan history enables the spm aware optimizer to determine whether the best-cost plan it has produced using the cost-based method is a brand new plan or not. a brand new plan represents a plan change that has potential to cause performance regression. for this reason, the spm aware optimizer does not choose a brand new best-cost plan. instead, it chooses from a set of accepted plans. an accepted plan is one that has been either verified to not cause performance regression or designated to have good performance. a set of accepted plans is called a sql plan baseline, which represents a subset of the plan history.
a brand new plan is added to the plan history as a non-accepted plan. later, an spm utility verifies its performance, and keeps it as a non-accepted plan if it will cause a performance regression, or changes it to an accepted plan if it will provide a performance improvement. the plan performance verification process ensures both plan stability and plan adaptability.
the figure below shows the smb containing the plan history for three sql statements. each plan history contains some accepted plans (the sql plan baseline) and some non-accepted plans.
you can create a sql plan baseline in several ways: using a sql tuning set (sts); from the cursor cache; exporting from one database and importing into another; and automatically for every statement. let’s look at each in turn. the examples in this blog entry use the oracle database sample schemas so you can try them yourself.
creating sql plan baselines from sts
if you are upgrading you might already have an sts containing some or all of your sql statements. this sts might contain plans that perform satisfactorily. let’s call this sts my_sts. you can create a sql plan baseline from this sts as follows:
sql> variable pls number;
sql> exec :pls := dbms_spm.load_plans_from_sqlset(sqlset_name => 'my_sts', -
> basic_filter => 'sql_text like ''select%p.prod_name%''');
this will create sql plan baselines for all statements that match the specified filter.
creating sql plan baselines from cursor cache
you can automatically create sql plan baselines for any cursor that is currently in the cache as follows:
sql> exec :pls := dbms_spm.load_plans_from_cursor_cache( -
> attribute_name => 'sql_text', -
> attribute_value => 'select%p.prod_name%');
this will create sql plan baselines for all statements whose text matches the specified string. several overloaded variations of this function allow you to filter on other cursor attributes.
creating sql plan baselines using a staging table
if you already have sql plan baselines (say on an 11g test system), you can export them to another system (a production system for instance).
first, on the test system, create a staging table and pack the sql plan baselines you want to export:
sql> exec dbms_spm.create_stgtab_baseline(table_name => 'my_stgtab', -
> table_owner => 'sh');
pl/sql procedure successfully completed.
sql> exec :pls := dbms_spm.pack_stgtab_baseline( -
> table_name => 'my_stgtab', -
> table_owner => 'sh', -
> sql_text => 'select%p.prod_name%');
this will pack all sql plan baselines for statements that match the specified filter. the staging table, my_stgtab, is a regular table that you should export to the production system using datapump export.
on the production system, you can now unpack the staging table to create the sql plan baselines:
sql> exec :pls := dbms_spm.unpack_stgtab_baseline( -
> table_name => 'my_stgtab', -
> table_owner => 'sh', -
> sql_text => 'select%p.prod_name%');
this will unpack the staging table and create sql plan baselines. note that the filter for unpacking the staging table is optional and may be different than the one used during packing. this means that you can pack several sql plan baselines into a staging table and selectively unpack only a subset of them on the target system.
creating sql plan baselines automatically
you can create sql plan baselines for all repeatable statements automatically by setting the parameter optimizer_capture_sql_plan_baselines to true (default is false). the first plan captured for any statement is automatically accepted and becomes part of the sql plan baseline, so enable this parameter only when you are sure that the default plans are performing well.
you can use the automatic plan capture mode when you have upgraded from a previous database version. set optimizer_features_enable to the earlier version and execute your workload. every repeatable statement will have its plan captured thus creating sql plan baselines. you can reset optimizer_features_enable to its default value after you are sure that all statements in your workload have had a chance to execute.
note that this automatic plan capture occurs only for repeatable statements, that is, statements that are executed at least twice. statements that are only executed once will not benefit from sql plan baselines since accepted plans are only used in subsequent hard parses.
the following example shows a plan being captured automatically when the same statement is executed twice:
sql> alter session set optimizer_capture_sql_plan_baselines = true;
session altered.
sql> var pid number
sql> exec :pid := 100;
pl/sql procedure successfully completed.
sql> select p.prod_name, s.amount_sold, t.calendar_year
2 from sales s, products p, times t
3 where s.prod_id = p.prod_id
4 and s.time_id = t.time_id
5 and p.prod_id < :pid;
prod_name amount_sold calendar_year
--------- ----------- -------------
...
9 rows selected.
sql> select p.prod_name, s.amount_sold, t.calendar_year
2 from sales s, products p, times t
3 where s.prod_id = p.prod_id
4 and s.time_id = t.time_id
5 and p.prod_id < :pid;
prod_name amount_sold calendar_year
--------- ----------- -------------
...
9 rows selected.
sql> alter session set optimizer_capture_sql_plan_baselines = false;
session altered
automatic plan capture will not occur for a statement if a stored outline exists for it and is enabled and the parameter use_stored_outlines is true. in this case, turn on incremental capture of plans into an sts using the function capture_cursor_cache_sqlset() in the dbms_sqltune package. after you have collected the plans for your workload into the sts, manually create sql plan baselines using the method described earlier. then, disable the stored outlines or set use_stored_outlines to false. from now on, spm will manage your workload and stored outlines will not be used for those statements.
in this article, we have seen how to create sql plan baselines. in the next, we will describe the spm aware optimizer and how it uses sql plan baselines.