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

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值