Oracle 性能问题-基数反馈

嗨，

数据库版本-11.2.0.4
兼容优化器-11.2.0.4

-Query包含许多子查询和联接，在某些表中，数据每天都会更改，而在某些表中，数据是最近的。合并了10-11个表，其中只有一个表包含大约400K行，其余是微型的。
-查询包含所有文字没有绑定变量。

问题-查询在第一次运行时表现良好，并在大约15秒内返回结果集，但从随后的运行中，查询继续执行更多的时间 (时间不是恒定的，但在30-50分钟之间变化)。

初步诊断-

在第一次运行时，Oracle通过粗略的基数估计 (大约110K行作为估计) 运行它，但从随后的运行中，它改善了基数 (大约1000行)，但总体成本增加了很多 (从两个查询的执行计划中显而易见)。
怀疑这是问题所在。

试图为第一次运行创建一个基线，以便在随后的执行中使用相同的基线，但即使在 “optimizer_capture_sql_plan_baselines = FALSE” 之后，它也会生成不同的基线，并且在随后的运行中，它会忽略两个基线，并在基数反馈之后继续停留更多的时间。

SQL> 从dba_sql_plan_baselines中选择sql_handle，plan_name，接受，启用，优化 _ 成本，其中SQL_HANDLE = 'sql_540fb108407c78ed ';

SQL_HANDLE PLAN_NAME ACC ENA OPTIMIZER_COST
-
SQL_540fb108407c78ed SQL_PLAN_583xj1107sy7db3ec76e2 YES 33095
SQL_540fb108407c78ed SQL_PLAN_583xj1107sy7dc81ab709否是195016

=

1.您还能想到这里可能出现的问题吗？
2.为什么Oracle不追求执行查询的总成本，而不是基数反馈，而基数反馈对于特定执行而言成本更高？
3.使用 “更改会话集” _ optimizer_use_feedback “= false;” 是否会引起任何问题，在这种情况下是否建议？
4.如果我们将查询的更好执行计划 (从其他来源运行) 的SQLprofile分配/强制分配给特定查询的所有后续执行？会带来一些问题吗？无论是好的还是坏的做法。
5.如果基数反馈并不总是好的，那么如何避免这种情况。我们应该每次强制禁用它吗？

似乎这里发生了很多事情。所以，我想一个接一个地解决它们。

首先，您有一个复杂的SQL语句，其中初始基数估计不准确，因此基数反馈正在启动并更改后续执行的执行计划。但是，通过基数反馈确定的执行计划是次优的。

在Oracle数据库中，11g基数反馈仅限于单个索引/表基数和按估计分组。如果问题实际上是联接顺序或选择的联接方法，则仅纠正这些估计可能不会导致更好的计划。

由于该语句仅在初始执行时受到监视，因此无法像在12c中那样在后续执行中继续改进计划。因此，我建议您不要对此声明使用基数反馈。

问题就变成了，我应该如何防止这个陈述发生基数反馈？

正如你正确指出的，你有很多选择。

1.您可以通过在会话或语句级别将下划线参数 “_ optimizer_use_feedback” 设置为false来完全禁用基数反馈。这样做安全吗？是的。但是，我不会在系统级别上禁用它，因为您有证据证明它的危害更大。
就个人而言，我更喜欢在最低级别更改参数。因此，我将使用opt_param提示仅针对此语句禁用基数反馈，如下所示: /* opt_param('_ OPTIMIZER_USE_FEEDBACK'，'FALSE') */。鉴于并非总是可以添加提示以使我进入选项2。

2.使用SQL计划管理确保优化器不使用基数反馈计划你有两种不同的方法来做到这一点:
a.捕获您在硬解析中获得的初始计划
b.捕获手动添加提示opt_param时获得的计划 ('_ OPTIMIZER_USE_FEEDBACK'，'FALSE')

您提到您最初尝试使用SQL Plan Management失败，这可能是正确的，原因有很多。根据您的描述，您选择了上面的选项A，捕获初始计划。从dba_sql_plan_baselines提供的输出中，我们可以看到您捕获的计划已启用和接受。

SQL_HANDLE      PLAN_NAME    ACC ENA COST

------------------------ ------------------------------ --- --- ---------

SQL_540fb108407c78ed    SQL_PLAN_583xj1107sy7db3ec76e2 YES YES 33095
SQL_540fb108407c78ed    SQL_PLAN_583xj1107sy7dc81ab709  NO YES 195016

复制

那么，为什么不使用它？不幸的是，从您提供的信息中很难分辨出来。有关优化器如何将SQL语句与SQL计划基线匹配的更多详细信息，请参见此博客文章https://blogs.oracle.com/optimizer/entry/how_does_sql_plan_management

可能的原因包括初始化参数OPTIMIZER_USE_SQL_PLAN_BASELINES被设置为FALSE (非默认)，解析架构与创建基线的架构不同，或者更可能无法复制捕获的计划。确认计划是否可重现的唯一方法是查看语句的优化器跟踪。您需要在跟踪文件中搜索缩写SPM。跟踪的这一部分将告诉您优化器是否确实将您捕获的计划与您的SQL语句相关联，以及为什么它无法重现它。

您还可以捕获由提示的SQL语句生成的计划，并按照本博客文章中概述的说明将其存储在非提示的SQL语句的基线中https://blogs.oracle.com/optimizer/entry/how_do_i_deal_with_a_third_party_application_that_has_embedded_hints_that_result_in_a_sub-optimal_ex

您还建议使用SQL配置文件作为替代方法。SQL配置文件包含辅助信息，当这些信息与对象和系统统计信息一起使用时，优化器可以最大程度地减少基数估计错误。由于SQL配置文件不包含一组完整的提示来重现确切的执行计划，因此不能保证随着基础对象统计信息的更改，它将继续生成您想要的计划。因此，我建议在SQL配置文件上使用SQL计划基线。