oracle optimizer_features_enable,[Oracle维护工程师手记系列]一次升级后运行变慢的分析...

客户报告，

当他从 Oracle 11.1.0.7 ，迁移到云环境，并且升级到12.1.0.2。

运行客户的应用程序测试，发现比以前更慢了。

从AWR report 的"Top 10 Foreground Events by Total Wait Time"和"Wait Classes by Total Wait Time"等信息，

可以看到 CPU 值升高了。

初步思考，怀疑由于环境的不同，导致性能不同。但是客户说，这两个环境的CPU数目/内存大小等各方面情况都完全一样。

那么就的考虑其他的原因了。

迁移前的环境和迁移后的环境对比，从AWR report 中，可以看到，迁移后的 "Hard parse"  的比例明显偏高。

***迁移前

Load Profile

Per Second Per Transaction Per Exec Per Call

DB Time(s): 1.1 0.0 0.00 0.00

DB CPU(s): 0.3 0.0 0.00 0.00

Parses: 158.3 4.8

Hard parses: 8.8 0.3 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Executes: 620.2 18.2

***迁移后

Load Profile

Per Second Per Transaction Per Exec Per Call

DB Time(s): 0.8 0.0 0.00 0.00

DB CPU(s): 0.6 0.0 0.00 0.00

Parses (SQL): 375.8 14.7

Hard parses (SQL): 75.2 3.0 <<<<<<<<<<<<<<<<<<<<<<<<< hard parse 大幅度增加

Executes (SQL): 702.7 28.3

客户报告，在新环境里，设置了 对 应用中的所有的 SQL文 都追加了 类似于

"SELECT /*+ OPTIMIZER_FEATURES_ENABLE(‘11.1.0.7‘) ORDERED INDEX(T0001 T0001_001) NO_USE_HASH(FBM10) */" 的hint之后，

发现性能基本回到了迁移前的水平。

从这一点来考虑，OPTIMIZER_FEATURES_ENABLE，可能是影响到了 optimizer_adaptive_features 功能。这一功能会在SQL文执行

的时候，动态地判断有没有更好的执行计划。而很可能对客户程序的一部分SQL文，反而动态调整执行计划产生了大量的Hard Parse，

整体上拖延了执行时间。

客户也怀疑 是 文档 2312911.1 所提到的 12.1 的 optimizer_adaptive_features 功能不够完善导致出现问题。文档 2312911.1

提及，12.1 的 optimizer_adaptive_features 功能不够完善，可以特殊处理，以达到12.2的效果(12.2中, optimizer_adaptive_

features 有所改善，被拆分成为了两个参数)。

那么，到底是不是这样呢，客户希望进一步进行分析调查。

从拿到了迁移前后的AWR来对比，发现了有趣的状况：实际上从Top N SQL来看，迁移前的SQL文，迁移后执行时间其实都大幅度缩短了。

***迁移前環境

========================

SQL ordered by Elapsed Time

Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.

% Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100

Total DB Time (s): 2,597

Captured SQL account for 58.8% of Total  <<<<<<<<<<<

Total DB Time (s): 2,597

Captured PL/SQL account for 0.3% of Total

========================

***迁移后(未追加 OPTIMIZER_FEATURES_ENABLE hint 的时候)

========================

SQL ordered by Elapsed Time  ★

Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.

% Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100

%Total - Elapsed Time as a percentage of Total DB time

%CPU - CPU Time as a percentage of Elapsed Time

%IO - User I/O Time as a percentage of Elapsed Time

Captured SQL account for 27.9% of Total DB Time (s): 1,771  <<<<<<<<<<<

Captured PL/SQL account for 1.3% of Total DB Time (s): 1,771

========================

从具体的top 10 SQL文执行时间上，也可以看出来：

++++++++++++++++++++++++++++++++++++++++++++++

2ac8g6tagg67x  迁移后>>17,122 回执行  CPU Time 13.27s、Elapsed Time 139.03s

迁移前>>21,018 回执行  CPU Time 16s、Elapsed Time 839s

5mt3dyz3r6af8  迁移后>>425 回执行  CPU Time 25.28s、Elapsed Time 33.10s

迁移前>>509回执行  CPU Time 44s、Elapsed Time 105s

7tjhsnk6ruw6m  迁移后>>165 回执行  CPU Time 16.67s、Elapsed Time 21.69s

迁移前>>165 回执行  CPU Time 25s、Elapsed Time 50s

......

baq2zs9gns3co  迁移后>>51,730 回执行  CPU Time 9.92s、Elapsed Time 16.57s

迁移前>>67,66 4回执行  CPU Time 10s、Elapsed Time 26s

++++++++++++++++++++++++++++++++++++++++++++++

那么，问题到底处在哪里呢？ 我的猜测是： 虽然TopN 那些SQL文的执行时间减小了。但是还有很多执行很短暂的SQL文，由于动态执行

计划调整，导致事件增加了。

这也很容易理解：

如果一条SQL语句的执行时间原本是200ms，如果动态执行计划调整花5ms，使得因执行更好的执行计划而较少了执行时间20ms,总体上来说

就是赚到了。

可是如果你一条小SQL执行语句，本来执行时间才20ms,如果动态执行计划调整花5ms，使得因执行更好的执行计划而较少了执行时间2ms,

反而执行时间增加了。

如果这条小SQL执行语句，反复多次执行呢？那问题就恶化了。

就好比：虽说磨刀不误砍柴功，但是如果每砍一个小树杈，都要磨一次刀，那是不是有点浪费时间啊。

对于这个推测，客户仍然想要了解更详细的细节，那么好吧，我们来看看ASH的情况：

迁移前

"In Hard Parse" ==> 15 行     "In Hard Parse" 的比例：37.5%

2018/03/12 15:26:36 --->  2018/03/12 15:37:25.693000000

SQL_ID: 30 个

迁移后

"In Hard Parse" ==> 252 行     "In Hard Parse" 的比例：76.6%

2018/03/20 11:33:50 --->  2018/03/20 12:25:58.591000000

SQL_ID: 292 个

迁移后的总体时间不但大幅度延长了，而且ASH中统计出来的sql_id 明显增多。所以可以推测很多原本执行时间很短的sql语句，因为执行计划

调整的原因，话费时间进行了 Hard Parse，所以当ASH采样的时候，因为没有执行完毕，更多的出现在ASH履历中。

那么，作为结论， 12c的执行计划调整并不是适合所有sql文的，对效果不佳的语句，不妨添加 OPTIMIZER_FEATURES_ENABLE 进行微调。

原文：https://www.cnblogs.com/gaojian/p/8971775.html