故障类型

业务 SQL 性能问题

现象错误号

数据库整体性能正常，某条 sql 前台执行缓慢，后天执行正常。

故障描述

某客户的业务系统局部出现性能问题，某条 SQL 语句前台执行缓慢，后台执行很快，根据此现象深度排查，由于临时表所引起，添加所有后恢复正常。

故障原因分析

经过检查 AWRSQRPT （运行 @?/rdbms/admin/awrrpti.sql 产生报告）发现业务系统性能主要是由一条 SQL 引起，如下所示：

具体的 SQL 显示如下：

可以看到这条 SQL 执行一次需要 406814ms ，但执行计划显示确实相当完美（ cost 不大，每一步骤执行时间很短），显示如下：

但是实际执行统计信息确相当糟糕，尤其 buffer gets 读取很高，达到了 595764242 ，显示如下：

根据执行计划显示，最大的一张表 GL_VERIFY_LOG 才 17710 条记录，至于其它表的数据量更加可以忽略不计。在规模如此小之下，出现这么高的 buffer gets 数量是及其不正常的。于是猜测，执行计划信息和实际执行情况不符合可能是由于数据库对象的统计信息不准引起的，于是对 SQL 涉及到的对象进行了采样 100% 的统计，但是现象依旧。

由于这条 SQL 没有绑定变量，不会出现 bind peeking 现象（即执行计划不稳定）。所以奇怪的是，这条 SQL 每次后台执行很慢，但前台执行很快，执行时间几乎不用 1 秒，而且后台执行时显示的执行计划和 AWR 报告中的执行计划一样。于是怀疑是不是中间件和网络出现了问题？检查发现中间件和网络不存在问题。

进行到这里，本次性能优化暂时碰到了难题。但是我注意到了一个细节。由于之前我对 SQL 涉及到的对象进行了采样 100% 的统计，但后台执行的执行计划出现了动态采样（ Dynamic Sampling ）。显示如下：

根据这一现象，推测该 SQL 中存在临时表，因为 Oracle 生成临时表的执行计划时会进行动态采样。经过检查， ASSTEMPORA 为临时表，前台程序在执行时加载了大量的数据，进而导致前台执行很慢，所以执行期间产生大量 buffer get 也是正常的。而我们在后台执行时，由于临时表的数据为空，所以执行起来很快。于是在临时表 ASSTEMPORA 中增加了一条索引，业务模块性能恢复正常：