oracle SQL计划管理 2

1.1.1.1   捕获SQL计划基线

可以通过几个步骤来捕获新的SQL计划基线。基本上，它们是由数据库引擎自动创建的，或由数据库管理员或开发人员手动创建。下面三部分分别介绍了三种方法。

Ø  自动捕获

将初始化参数optimizer_capture_sql_plan_baselines设置为TRUE时，查询优化器会自动保存新的SQL计划基线。默认情况下，会将初始化参数设置为FALSE。可以在会话和系统级别更改它。

启用自动捕获时，查询优化会为每条多次执行(即至少执行两次)的SQL语句保存新的SQL计划基线。为此，它会在SQL基础管理平台中管理一个日志来插入每条它处理的SQL语句签名。这代表某一SQL语句第一次执行后，它的签名仅会插入日志。然后，当同一个SQL语句第二次执行时，会创建仅包含当前执行计划的SQL计划基线并且标记为接受。从第三次执行开始，由于SQL计划基线已经与SQL语句相关联，因此查询优化器还会比较当前执行计划与SQL计划基线生成的执行计划。

如果它们不匹配，这代表根据当前查询优化器的估算，最优的执行计划并不是存储在SQL计划基线中的那个。为了保存这个信息，会将当前执行计划添加到SQL计划基线中并且标记为不接受。然而，就像你之前看到的那样，当前执行计划无法使用。会强制查询优化器使用SQL计划基线生成的执行计划。图11-9总结了整个处理过程。

图11-9 自动捕获SQL计划基线期间执行的主要步骤

将某个新的执行计划存储到SQL计划基线中时，重点需要区分以下两种情况。

Ø  如果这是SQL计划基线的第一个执行计划，则会将执行计划存储为接受，因此，查询优化器将能够使用它。

Ø  如果这不是SQL计划基线的第一个执行计划，则会将它存储为不接受，因此，查询优化器无法使用它。“进化SQL计划基线”部分将介绍如何使SQL计划基线生效，以使其对查优化器可用。

Ø  从库缓存中加载

要基于存储在库缓存中的游标手动将SQL计划基线加载进数据字典中，可以使用dbms_spm包下的load_plans_from_cursor_cache函数。

实际上，会多次重载函数来支持确定必须处理哪些游标的不同方法。这包含两种主要的可能。第一，通过指定以下属性之一来标识多个SQL语句。

Ø  sql_text:SQL语句的文本。这个属性支持通配符(例如%)。

Ø  parsing_schema_name:用来解析游标的模式名称。

Ø  module:执行SQL语句的模块名称。

Ø  action:执行SQL语句的动作名称。

举例说明，下面的调用引用自baseline_from_sqlarea1.sql脚本，为存储在库缓存中包含注释MysqlStm字符串的每个SQL语句创建SQL计划基线：

ret:= dbms_spm.load_plans_from_cursor_cache(attribute_name =>'sql_text',attribute_value =>'%/* MysqlStm */%');

第二，通过它的SQLID来标识SQL语句，以及可选执行计划的散列值。如果散列值没有指定或设置为NULL,所有对指定SQL语句可用的执行计划都会被加载。下面的调用，引用自baseline_from_sqlarea2.sq1脚本：

ret := dbms_spm.load_plans_from_cursor_cache(sql_id=>'2y5r75r8y3sjo',plan_hash_value=>NULL);

使用这些函数加载的执行计划会被存储为可接受，因此查询优化器可以立即利用它们。

在之前的例子中，SQL计划基线基于库缓存中找到的SQL语句的文本。这只有在你想确保当前的执行计划未来也会被用到时才有关系。有时，使用SQL计划基线的目的是优化SQL语句而不用修改应用。让我们看这样一个基于baseline_from_sqlarea3.sql脚本的例子。

假设应用执行以下SQL语句。查询优化器基于全表扫描生成执行计划。这是因为在SQL语句中包含一个hint,该hint强制查询优化器指向此操作：

select /*+ full(t) */ count(pad) FROM t WHERE n = 42;

select * from table(dbms_xplan.display_cursor);

你会注意到限制列(n)上有索引存在。接着你或许想知道当使用索引时性能会怎样。因此，正如下面的例子所示，使用某个hint来执行SQL语句，以确保能够使用索引：

select /*+ index(t) */ count(pad) FROM t WHERE n =42;

select * from table(dbms_xplan.display_cursor);

如果第二个执行计划比第一个更有效率，你的目的就是让应用使用它。如果无法更改应用来删除或者修改hint,可以利用SQL计划基线来解决这个问题。可以自动或者手动创建SQL计划基线来达到目的。在这种情况下，你决定使用初始化参数optimizer_capture_sql_plan_baselines:

ALTER SESSION SET optimizer_capture_sql_plan_baselines = TRUE;

select /*+ full(t)*/ count(pad) FROM t WHERE n =42;

select /*+ full(t)*/ count(pad) FROM t WHERE n = 42;

ALTER SESSION SET optimizer_capture_sql_plan_baselines = FALSE;

一旦SQL计划基线创建好，就要检查是否真的使用了它。通过dbms_xplan包可以看到SQL计划名，用来标识生成执行计划的SQL计划基线：

select /*+ full(t) */ count(pad) FROM t WHERE n = 42;

select * from table(dbms_xplan.display_cursor);

Note

- sql plan baseline SQL_PLAN_3u6sbgq7v4u8z3fdbb376 used for this statement

接着，基于之前的输出提供的SQL计划名，通过dba_sql_plan_baselines视图，可以找到SQL计划基线的标识符，即SQL句柄 (SQL handle):

select sql_handle FROM dba_sql_plan_baselines WHERE plan_name='SQL_PLAN_3u6sbgq7v4u8z3fdbb376';

最后，你使用SQL计划基线替换执行计划。要这么做，需要加载执行索引扫描的执行计划，并移除执行全表扫描的执行计划。前者被SQL标识符以及执行计划散列值引用，后者被SQL句柄和SQL计划名引用：

ret := dbms_spm.load_plans_from_cursor_cache(

sql_handle =>'SQL_3d1b0b7d8fb2691f',

sql_id =>'dat4n4845zdxc',

plan_hash_value =>'3694077449');

ret := dbms_spm.drop_sql_plan_baseline(

sql_handle=>'SQL_3d1b0b7d8fb2691f',

plan_name =>'SQL_PLAN_3u6sbgq7v4u8z3fdbb376');

要检查替换是否成功，可以测试新的SQL计划基线。请注意即使SQL语句包含full hint,执行计划也不会再使用全表扫描。

注意 在实践中，不恰当的hint经常导致低效的执行计划。能够使用这部分技术来覆盖它们是非常有用的。

select /*+ full(t)  */ count(pad) FROM t WHERE n= 42;

select * from table(dbms_xplan.display_cursor);

Note

- sql plan baseline SQL_PLAN_3u6sbgq7v4u8z59340d78 used for this statement

要想知道SQL计划基线是否被用于某条SQL语句，也可以查询v$sql视图的sql_plan_baseline列。请注意该列显示的是SQL计划名，不是dbms_xplan包显示的SQL句柄。

Ø  从SQL调优集中加载

dbms_spm包下的load_plans_from_sqlset函数，可以从SQL调优集中加载SQL计划基线。加载仅需要指定所有者(owner)和SQL调优集名称。下面的调用，节选自baseline_from_sqlset.sql脚本：

ret := dbms_spm.load_plans_from_sqlset(sqlset_name=>'test_sqlset',sqlset_owner=>user);

使用该函数加载的执行计划会被存储为可接受。因此，查询优化器可以立即利用它们。

升级到新版本可以用到该函数。实际上，10.2版本的数据库创建的SQL调优集也可以加载到11.2版本的数据库中。Baseline_upgrade_10g.sq1和baseline_upgrade_11g.sql脚本列举了这样的应用。