Dav_笔记10：Using SQL Plan Management之2

本节进行sql管理基准的knowledge show

管理SQL计划基准

管理SQL计划基准涉及以下阶段：

■捕获SQL计划基准

■选择SQL计划基准

■不断发展的SQL计划基准

捕获SQL计划基准

在SQL计划基准捕获阶段，数据库会检测计划更改并记录新计划，以便数据库管理员可以对其进行演化（验证）。为此，数据库维护各个SQL语句的计划历史记录。由于临时SQL语句不会重复，因此不会降低性能，因此数据库仅为可重复的SQL语句维护计划历史记录。

为了识别可重复的SQL语句，数据库维护一个语句日志，该日志包含优化程序已评估的各种SQL语句的SQL ID。数据库在将SQL语句记录后再次解析或执行时将其识别为可重复。

对于每个可重复的SQL语句，数据库维护一个计划历史记录，其中包含优化程序生成的所有计划。计划历史记录中所有已接受计划的集合是SQL计划基准。

您可以配置SQL计划基准捕获阶段，以自动捕获计划历史记录和可重复SQL语句的SQL计划基准。或者，您可以手动将计划作为SQL计划基准加载。

本节包含以下主题：

■自动捕获计划

■从现有计划创建基准

自动捕获计划

启用自动计划捕获后，数据库将使用优化程序提供的信息自动创建和维护SQL语句的计划历史记录。计划历史记录包括优化程序用于重现执行计划的相关信息，例如SQL文本，大纲，绑定变量和编译环境。

优化程序将为SQL语句生成的初始计划标记为已使用，并表示计划历史记录和SQL计划基准。计划历史记录包括所有后续计划。在SQL计划基准演变阶段，数据库会将计划添加到已验证不会导致性能下降的基准。

要启用自动计划捕获，请将OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES初始化参数设置为TRUE。默认情况下，此参数为FALSE。

从现有计划创建基准

您可以通过手动加载一组SQL语句的现有计划作为计划基准来创建SQL计划基准。数据库不验证手动加载的性能计划，而是将它们作为已接受的计划添加到现有或新的SQL计划基准。您可以使用手动计划加载或作为自动计划捕获的替代方法。

您可以执行以下手动计划加载：

■从SQL调整集和AWR快照加载计划

■从共享SQL区域加载计划

从SQL调整集和AWR快照加载计划

要从SQL调优集加载计划，请使用DBMS_SPM程序包的LOAD_PLANS_FROM_SQLSET函数。以下示例加载存储在名为tset1的SQL调优集中的计划：

DECLARE

my_plans PLS_INTEGER;

BEGIN

my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( sqlset_name => 'test1');

END;

/

要从自动工作负载存储库（AWR）加载计划，请在使用LOAD_PLANS_FROM_SQLSET函数之前将存储在AWR快照中的计划加载到SQL调优集中，如本节所述。

从共享SQL区域加载计划

要从共享SQL区域加载计划，请使用DBMS_SPM程序包的LOAD_PLANS_FROM_CURSOR_CACHE函数。 在以下示例中，Oracle数据库为sql_id标识的SQL语句加载位于共享SQL区域中的计划：

DECLARE

my_plans PLS_INTEGER;

BEGIN

my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id => '99twu5t2dn5xd');

END;

/

您可以通过以下方式在共享SQL区域中识别计划：

■SQL identifier (SQL_ID)

■SQL text (SQL_TEXT)

■One of the following attributes:

–PARSING_SCHEMA_NAME

–MODULE

–ACTION

选择SQL计划基准

在SQL计划基准选择阶段，Oracle数据库会根据存储的计划历史记录检测计划更改，并选择计划以避免对一组SQL语句进行潜在的性能回归。

每次数据库编译SQL语句时，优化器都会执行以下操作：

1.使用基于成本的搜索方法来构建最佳成本计划

2.Tries在SQL计划基准中找到匹配的计划

3.根据是否找到匹配，执行以下任一操作：

■如果找到，则优化程序将使用匹配的计划继续

■如果未找到，则优化程序将评估SQL计划基准中每个已接受计划的成本，并选择成本最低的计划

优化程序找到的与SQL语句的计划历史记录中的任何计划都不匹配的最佳成本计划表示新计划。数据库将此计划添加为计划历史记录的不接受计划。在验证不会导致性能下降之前，数据库不会使用新计划。但是，如果系统中的更改（例如删除的索引）导致所有已接受的计划变得不可再现，则优化程序会选择最佳成本计划。因此，SQL计划基准的存在会导致优化程序对SQL语句使用保守的计划选择策略。

要启用SQL计划基准，请将OPTIMIZER_USE_SQL_PLAN_BASELINES初始化参数设置为TRUE（默认值）。

不断发展的SQL计划基准

在SQL计划基准演变阶段，数据库会评估新计划的性能，并将具有更好性能的计划集成到SQL计划基准中。

当优化程序找到SQL语句的新计划时，数据库会将计划作为不接受的计划添加到计划历史记录中。数据库可以验证相对于SQL计划基准性能的性能计划。成功验证未接受的计划包括将其性能与从SQL计划基准中选择的计划的性能进行比较，并确保其提供更好的性能。当数据库验证不接受的计划不会导致性能回归时，数据库会将其更改为已接受的计划并将其集成到基准中。

本节介绍如何发展SQL计划基准并包含以下主题：

■手动计划加载的演变计划

■使用DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE制定计划

手动计划加载的演变计划

您可以通过从共享SQL区域或SQL调优集手动加载计划来发展现有SQL计划基准。手动将计划加载到SQL计划基准中时，数据库会将这些已加载的计划添加为已接受的计划。

使用DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE制定计划

PL / SQL函数DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE尝试将优化程序添加的新计划发展为现有计划基准的计划历史记录。如果该函数可以验证新计划的性能优于从相应的SQL计划基准中选择的计划，则数据库会将新计划添加为已接受的计划。

以下DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE函数示例演变了由SQL句柄标识的SQL语句的新计划，该句柄是字符串形式的唯一SQL标识符。您可以通过查询DBA_SQL_PLAN_BASELINES.SQL_HANDLE找到SQL句柄。

SET SERVEROUTPUT ON

SET LONG 10000

DECLARE

report clob;

BEGIN

report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(

sql_handle => 'SYS_SQL_593bc74fca8fsdfs8');

DBMS_OUTPUT.PUT_LINE(report);

END;

/

以下输出显示Oracle数据库已成功制定计划：

报告

或者，您可以使用DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE函数指定：

■要发展的特定计划的名称

■计划进展列表

■没有价值

通过不指定任何值，您可以启用Oracle数据库来发展SMB中当前所有未接受的计划。