oracle SQL配置文件 5

SQL配置文件如何影响查询优化器?Oracle并未在其文档中给出答案。我认为高效地使用特性的最好方法就是了解它的工作原理。因此，让我们来看看它的内部。简单地说，SQL配置文件保存了一组hint来表示查询优化器执行的优化。

其中一些hint是在文档里有记录的，并且也用于其他环境。其他hint是未公开的，并且通常只会由SQL配置文件使用。换句话说，它们都为了这个目的而使用。它们全部都是普通的hint,因此也可以直接加入到SQL语句中。

在讨论如何查询与SQL配置文件关联的hint列表前，让我们先引入一个基于profile_all_rows.sql脚本的例子。它的目的是为了展示，使用SQL配置文件是可以命令查询优化器改变优化模式的。在这个特定的例子中，需要改变优化器模式，是因为查询包含了rule hint,这会强制查询优化器在基于规则的模式下工作。查询和它的执行计划如下：

select /*+ rule */ * FROM t ORDER BY id;

Note

- rule based optimizer used (consider using cbo)

在让SQL优化顾问在查询上工作并且应用SQL配置文件后，执行计划也会改变。正如Note部分指出的那样，会在执行计划生成期间使用SQL配置文件：

select /*+ rule */ * FROM t ORDER BY id;

Note

- SQL profile "all_rows" used for this statement

不幸的是，与SQL配置文件关联的hint无法通过数据字典视图显示。实际上，只有两个视图能提供关于SQL配置文件的信息：dba_sql_profiles视图和在12.1多租户环境下的cdb_sql_profiles视图，它们提供除了hint以外的所有信息。如果想知道哪些hint被用于SQL配置文件，那么你有两个选择。

第一个是直接查询内部数据字典表。下面的查询会介绍针对由profile_all_rows.sql脚本生成的SQL配置文件该如何查询。请注意，会用到两个初始化参数hint(all_rows和optimizer_features_enable)。此外，要命令查询优化器忽略当前SQL语句中的hint(本例是rule hint),会用到ignore_optim_embedded_hints。

Ø  该查询在10.2版本中可用：

SELECT attr_val

FROM sys.sqlprof$ p, sys.sqlprof$attr a

WHERE p.sp_name = 'all_rows'

AND p.signature = a.signature

AND p.category = a.category;

Ø  该查询在11.1版本中可用：

SELECT extractValue(value(h),'.') AS hint

FROM sys.sqlobj$data od, sys.sqlobj$ so,

     table(xmlsequence(extract(xmltype(od.comp_data),'/outline_data/hint'))) h

WHERE so.name = 'all_rows'

AND so.signature = od.signature

AND so.category = od.category

AND so.obj_type = od.obj_type

AND so.plan_id = od.plan_id;

第二种可能是将SQL配置文件移动到临时表中，这在“移动SQL配置文件”部分介绍过。接着，使用类似以下的查询，可以从临时表中获取hint。请注意会执行通过table函数的非嵌套查询，因为hint存储在VARCHAR2变长数组中：

select * FROM table(select attributes FROM mystgtab WHERE profile_name ='opt_estimate');

SQL配置文件不仅可以更改优化器的模式，实际上，它还可以用来校正查询优化器执行错误的基数估算。Profile_opt_estimate.sql脚本展示的就是这样的例子。使用第10章介绍的技巧可以识别错误的估算。可以看到在下面的例子中，几项操作的估算基数(E-Rows)与真实基数(A-Rows)完全不同：

P356

如果使用SQL优化顾问来分析这样的案例并且应用它的建议，就像profile_opt_estimate.sql脚本那样，那么会创建包含以下hint的SQL配置文件：

OPT_ESTIMATE(@"SEL$1",INDEX_SCAN,"T1"@"SEL$1","T1_COL1_COL2_I",SCALE_ROWS=477.9096254)

OPT_ESTIMATE(@"SEL$1",NLJ_INDEX_SCAN,"T2"@"SEL$1",("T1"@"SEL$1"),"T2_PK",SCALE_ROWS=0.4814075109)

OPT_ESTIMATE(@"SEL$1",NLJ_INDEX_FILTER,"T2"@"SEL$1",("T1"@"SEL$1"),"T2_PK",SCALE_ROWS=0.4814075109)

OPT_ESTIMATE(@"SEL$1",TABLE,"T1"@"SEL$1",SCALE_ROWS=486,2776343)

OPTIMIZER_FEATURES_ENABLE(default)

需要额外注意的是未公开的hint opt_estimate。使用这个特别的hint,就可以通知查询优化器它的一些估算是错误的并且还可以得知错误程度。例如，第一个hint告诉查询优化器对访问表t1的操作估算按比例增加大约478倍(“大约”是因为9500/20的分母在dbms_xplan的输出中被四舍五入了)。

适当地使用SQL配置文件，估算会变得更精确。同样请注意查询优化器选择了另一个执行计划，它是最初用来创建SQL配置文件的：

另一个SQL配置文件的用处是当对象存在错误或丢失对象统计信息时。当然这不应该发生，但当它发生并且动态采样无法为查询优化器提供需要的信息时，就可以使用SQL配置文件。Profile_object_stats.sql脚本提供了这样的例子。脚本生成的SQL配置文件是由hint组成的，尤其是以下这些。正如hint名显示的那样，每个hint都在为表、对象或列提供对象统计信息：

TABLE_stats("CHRIS"."T2",scale, blocks=735 rows=5000)

INDEX_stats("CHRIS"."T2","T2 PK",scale, blocks=14 index_rows=5000)

COLUMN_stats("CHRIS"."T2","PAD",scale, length=1000)

COLUMN_stats("CHRIS"."T2","COL2",scale,length=3)

COLUMN_stats("CHRIS"."T2","COL1",scale,length=3)

COLUMN_stats("CHRIS"."T2","ID",scale,length=3 distinct=5000 nulls=0 min=2 max=10000)

对于这部分关于未公开特性的内容，我最后想介绍的是手工创建SQL配置文件。换句话说，代替询问SQL优化顾问分析并应用SQL配置文件，你可以建立自己的SQL配置文件。通过调用dbms_sqltune包中的import_sql_profile过程来手工创建SQL配置文件。下面的示例是基于profile_import.sql脚本的调用。sql_text参数指定了绑定SQL配置文件的SQL语句，profile参数指定hint列表。其他参数与之前介绍的accept_sql_profile过程的参数具有相同的定义：

BEGIN

  dbms_sqltune.import_sql_profile(

    name        => 'import',

    description => 'SQL profile created manually',

    category    => 'TEST',

    sql_text    => 'SELECT * FROM t ORDER BY id',

    profile     => sqlprof_attr('first_rows(42)','optimizer_features_enable(default)'),

    replace     => FALSE,

    force_match => FALSE

  );

END;

/

注意尽管dbms_sqltune包中的import_sql_profile并不是官方记录的，但创建SQL配置文件的方法，与应用SQL优化顾问的建议而由数据库引擎创建的SQL配置文件是一样的。因此，我认为使用import_sql_profile过程没问题。

此外，在Oracle Suport说明SQLT(SQLTXPLAIN)- Tool that helps to diagnose a SQL statement performing poorly or one that produces wrong results (215187.1)中的coe_xfr_sql_profile.sql脚本使用了同样的过程来创建SQL配置文件。另外，可以执行coe_xfr_sql_profile.sql脚本来为库缓存中缓存的或AWR中存储的游标创建SQL配置文件。

SQL配置文件最重要的属性之一是，它们与代码是分开的。然而这也会带来问题。实际上，由于在SQL配置文件与SQL语句之间没有直接的关联，开发人员很可能会彻底忽略SQL配置文件的存在。结果，如果开发人员修改SQL语句将会导致它的签名发生改变，这样SQL配置文件就不会再生效了。同样，当你部署一个应用需要依靠SQL配置文件来保证它执行正确时，必须记得在数据库设置期间安装它们。

如果需要生成SQL配置文件，最好的做法是在生产环境中生成(如果可行),然后移动到其他环境中去做测试。但是，问题是在移动SQL配置文件之前，你不得不应用它。你不会想在未测试之前就在生产环境中应用它，因此需要确保应用的SQL配置文件使用的类别与初始化参数sqltune_category激活的类别不同。那样，SQL配置文件就不会在生产数据库上使用。总之，你总是可以在过后修改SQL配置文件的类别。

需要注意的是，SQL配置文件依赖的对象被删除时，SQL配置文件并不会被删除。但这并不是问题。例如，如果一个表或索引因为它必须重组或移动而需要重建，那么SQL配置文件没被删除就是好事。否则，就有必要重建它们。

两个有相同文本的SQL语句拥有相同的签名。即使它们引用的对象在不同的用户下。这代表单个存储概要可以被两个同名但是不同用户的表使用。再次强调，你需要小心，尤其是当数据库中同样的对象有多个副本时。

在11.2.0.2及之前的版本中，因为Oracle Support文档sql_profile not used in the Active Physical Standby(10050057.8)中描述的bug, 导致SQL配置文件在Active Data Guard环境下受限。你可以在主实例上使用SQL配置文件，但并不总是能在备用实例上使用。

当SQL语句有SQL配置文件和存储概要时，查询优化器会仅使用存储概要。当然，前提是存储概要处于激活状态。

当SQL语句有SQL配置文件和SQL计划基线时，查询优化器会尝试合并与SQL配置文件关联的hint和与SQL计划基线关联的hint。然而，合并SQL配置文件与SQL计划基线有使用限制。实际上，就像下一部分介绍的那样，SQL计划基线的目的是强制使用特定的执行计划。结果，在考虑使用SQL计划基线之前，SQL配置文件的用处或许只是生成新的不被应用的执行计划。