merge语句导致的性能问题紧急优化

晚上正在休息的时候，突然收到一封报警邮件。
报警内容: CPU utilization is too high
------------------------------------
报警级别: PROBLEM
------------------------------------
监控项目: CPU idle time：59.11 %
------------------------------------
这个报警信息已经非常明确，CPU使用率很紧张了。这台服务器上运行着Oracle和MySQL实例，所以第一感觉是不是MySQL的慢查询导致的，结果登录到服务器端，发现MySQL的连接数很少，没有发现慢查询，那么问题很可能就和Oracle有关系了，使用top查看，果然几个top的进程都是带有LOCAL=NO的字样，很可能是应用端触发的SQL导致。
查看v$session的信息，发现其中一个会话已经执行一条SQL超过了40分钟。这个SQL是merge语句，看来merge又摊上事了。

语句的内容如下：

我们来看看语句的执行计划情况，如下：

可以明显看到一个全表扫描，这个表中的数据大概是700多万，就算全表扫描也应该几分钟就出结果了。怎么执行了40分钟了还没有任何反应。
我们来看看执行的瓶颈在哪里。还是使用SQL Monitor来定位，一目了然。

可以明显看出瓶颈在全表扫描的部分。

表中的数据有700多万，而经过排查发现竟然没有任何索引。所以问题的原因就更加明显了。
我们需要再总结一下，问题的原因定位到了，执行计划的效率极低。怎么改进。
1.创建索引，这就无形带来几个问题，基于哪些列来创建索引
2.这个表中目前存在频繁的DML操作，如何创建索引
3.为什么执行计划的消耗如此之大
我们来一个一个看问题，首先是创建索引，这个看起来目标明确，但是具体来做，就需要一些技巧了， 对于这类的问题，个人比较偏好dbms_sqltune.report_tuning_task，因为调用方便，简单而且能很快出结果。不到1分钟分析结果就出来了，提出了3点建议，一个就是收集统计信息，一个是修改SQL Profile，还有一个就是创建索引，我们的目标明确，就是索引的改进，建议里面会根据数据的选择度来进行评估和分析，还是比较全面的。

但是创建索引的过程中，这个DDL肯定会排斥其他的DML,很容易出现这样的错误。

这种情况下，新特性ddl_lock_timeout就值得推荐了，我们可以设定一个略微长一些的超时时间，让它在后台自己去试，很快就能得到结果。

回头一看前两个问题已经解释了，那么第3个问题，为什么执行计划的差别如此之大，就算全表也不至于那么慢啊。
语句的谓词部分会做出解释：

可以看到走了filter过滤，using部分和表中的数据映射存在重大的偏差，内部的映射竟然是一大堆的case when的形式。
当然这个语句优化之后，性能提升也很明显。精确到分钟级，效果提升还是不错的。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23718752/viewspace-2123182/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23718752/viewspace-2123182/