sql - 在 Oracle 12c 中查看外连接的性能问题

Question

我的两个客户最近升级到 Oracle 12c 12.1.0.2 .升级后，我在使用带有外连接的 View 进行查询时遇到了显着的性能下降。下面是一个简单查询的例子，它在旧的 Oracle 11g 11.2.0.2 上运行几秒钟数据库，但在新的 12c 上需要几分钟时间数据库。更令人困惑的是，此查询在 12c 之一上运行得相当快(但没有那么快)。数据库，但在其他方面根本没有。性能太差了 12c我开发的报告无法使用的数据库。

我比较了 11g 之间的索引和系统参数和两个 12c数据库，并没有发现任何显着差异。 Execution Plans之间有区别， 然而。上 11g外连接表示为 VIEW PUSHED PREDICATE但在 12c它表示为 HASH JOIN没有 PUSHED PREDICATE .

当我添加提示时 /*+ NO_MERGE(pt) PUSH_PRED(pt) */到 12c 上的查询数据库，那么性能在几秒钟内。

向 SQL 添加提示不是我们 Crystal Reports 中的一个选项。 (至少我不这么认为，而且还有几份报告)，所以我希望我们能弄清楚为什么一个 12c 数据库的性能可以接受，而另一个数据库则不能。

我和我的团队不知道接下来要尝试什么，尤其是为什么两者之间的 react 如此不同 12c数据库。我们在12c中研究了几篇关于性能下降的文章。 ，但似乎没有什么特别适用于这个特定问题。作为补充说明，使用表而不是 View 的查询会在可接受的时间范围内返回结果。任何见解或建议将不胜感激。





询问:

select pi.*
, pt.*
from policyissuance_oasis pi
, policytransaction_oasis pt
where 
pi.newTranKeyJoin = pt.polTranKeyJoin(+)
and pi.policyNumber = '1-H000133'
and pi.DateChars='08/10/2017 09:24:51'    -- 2016 data
--and pi.DateChars = '09/26/2016 14:29:37'  --2013 data
order by pi.followup_time

Answer 1

正如 krokodilko 所说，执行这些:

explain plan for 
select pi.*
, pt.*
from policyissuance_oasis pi
, policytransaction_oasis pt
where 
pi.newTranKeyJoin = pt.polTranKeyJoin(+)
and pi.policyNumber = '1-H000133'
and pi.DateChars='08/10/2017 09:24:51'    -- 2016 data
--and pi.DateChars = '09/26/2016 14:29:37'  --2013 data
order by pi.followup_time;
select * from table(dbms_xplan.display());

然后，您可能会在计划的底部看到这一点:

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

那里，

dynamic sampling

概念应该是性能问题的关注中心(level=2 是默认值，范围在 0-11 之间)。

事实上，引入动态采样 (DS) 是为了提高优化器生成良好执行计划的能力。此功能已增强并重命名 Dynamic Statistics in Oracle Database 12c .最常见的误解是 DS可以作为优化器统计信息的替代，而 DS的目标是增加 optimizer statistics ;用于 regular statistics不足以获得高质量的基数估计。

对于串行 SQL 语句， dynamic sampling level由控制

optimizer_dynamic_sampling

参数但请注意来自 Oracle Database 12c Release 1 SQL plan directives的存在也可以发起 dynamic statistics在编译查询时收集。这是自适应统计的一个特性，由数据库参数控制

optimizer_adaptive_features (OAF)

在 Oracle Database 12c Release 1和

optimizer_adaptive_statistics (OAS)

在 Oracle Database 12c Release 2 .

换句话说，来自 Oracle Database 12c Release 1 (我们也在办公室使用 db12.1.0.2)以后， DS如果通过将相关参数设置为 TRUE 启用某些自适应功能，将使用.

串行语句通常是短期运行的和任何 DS编译时的开销会对整体系统性能产生很大影响(如果语句经常被硬解析)。对于匹配此配置文件的系统，设置 OAF=FALSE推荐( alter session set optimizer_adaptive_features=FALSE 注意

you shouldn't alter system but session

)。

对于 Oracle Database 12c Release 2 onwards ，使用默认 OAS=FALSE被推荐。
Parallel statements通常更占用资源，因此通常值得在编译时投资额外的开销以可能找到更好的 SQL execution plan .

对于 serial type SQL statements ，您可以尝试手动设置 optimizer_dynamic_sampling 的值(假设没有相关的 SQL 计划指令)。如果我们要针对具有并行属性集的更大表发出类似样式的查询，我们可以看到动态采样开始了。

什么时候应该使用 dynamic sampling ? DS当您知道由于复杂的谓词而得到错误的执行计划时，通常建议使用。但不应该像我之前提到的那样是系统范围的。

什么时候使用 dynamic sampling 不是一个好主意?
如果查询编译时间需要尽可能快，例如，不重复 OLTP无法在多次执行中分摊额外编译成本的查询。

As the last word, for your case, it could be beneficient to set optimizer_adaptive_features parameter to FALSE for individual SQL statements and see the gained results.