SQL之美- 通过SQL MONITOR解读并优化SQL

编辑手记：SQL优化及SQL审核，是从源头解决性能问题的根本手段，无论是开发人员还是DBA，都应当持续深入的学习SQL开发技能，从而为解决性能问题打下根基。

本系列经典文章

之一：标量子查询优化

之二：OR展开与子查询优化案例详解。

之三：IN子查询返回结果集异常

今天是系列第四讲：通过SQL MONITOR来优化SQL

作者简介：

黄廷忠（网名：认真就输）

云和恩墨技术专家

个人博客：http://www.htz.pw/

注意：SQL MONITOR是ORACLE 11G新增加的功能，在12C中此功能得到了增强。

下面这个案例，是在出账的时候，开发工程师收到通知账号被锁了，需要手动KILL进程，才能保证出账进程能顺利完成。

首先我们查询数据库中锁的信息，看到1752阻塞者是ACTIVE的，正在执行3wscxx88myd7t这个SQL。

查询1752会话的状态，可以发现，1752会话执行ID为 3wscxx88myd7t 的SQL已经很长一段时间了。我们通过sql monitor来查看执行计划。这里需要特别注意红色方框里面的内容。

SQL执行的开始时间在15:48分，已经执行4760S还没有执行完，通过MODULE PROGRAM我们知道这个是前台打印发票，这个SQL正常情况下，应该在1S内出结果，执行这么久，明显是异常的。

下面继续查看SQL MONITOR的信息。首先来看绑定变量和全局的一些信息。

执行计划如下：

我们重点来看红色方框标出的内容。

在红色标记部分，可以看到最后一个VIEW是NL被驱动表，视图内部是通过HASH JOIN连接返回0行结果，整个问题就出现在VIEW这里。

下面来查看一个VIEW这部分的代码

(SELECT/*+use_nl(balance_source,a,A_CACHED_BALANCE)*/ NVL(SUM(AA.AMOUNT), NULL) PAYOUT_BALANCE FROM (SELECT AMOUNT, ACCT_BALANCE_ID FROM BALANCE_PAYOUT WHERE OPER_DATE > TO_DATE('20160102144414', 'YYYYMMDDHH24MISS') AND STATE = '00A') AA, A_CACHED_BALANCE WHERE A_CACHED_BALANCE.ACCT_BALANCE_ID= AA.ACCT_BALANCE_ID ANDA_CACHED_BALANCE.ACCT_ID = 114680344) D,

这里开发使用NL提示，出发点是好的，但是执行计划出来是走的HASH JOIN。

下面手动添加提示

(SELECT /*+ index(A_CACHED_BALANCE,IDX_A_CACHED_BALANCE_ACCT_ID_1)use_nl(AA,A_CACHED_BALANCE)*/ NVL(SUM(AA.AMOUNT), NULL)PAYOUT_BALANCE FROM (SELECT AMOUNT,ACCT_BALANCE_ID FROM BALANCE_PAYOUT WHERE OPER_DATE > TO_DATE('20160102152005', 'YYYYMMDDHH24MISS') AND STATE = '00A')AA, A_CACHED_BALANCE WHEREA_CACHED_BALANCE.ACCT_BALANCE_ID = AA.ACCT_BALANCE_ID ANDA_CACHED_BALANCE.ACCT_ID = 144023766) D,

添加提示后的执行计划信息如下：

这里看到，SQL已经按我们预期的提示走执行计划了。

下面看看SQL执行的性能。

逻辑读下降到57 ，执行时间为Elapsed:00:00:00.06

The end