生产环境sql语句调优实战第三篇

生产环境有一条sql语句执行比较频繁，占用了大量的cpu资源。原本执行需要花费11秒。在一次排查中引起了我的注意，决定看看cpu消耗到底在哪儿？
sql语句是比较简单的，通过查询SUBSCRIBER_FA_V是一个视图。在视图中关联了几个和业务核心表。

SELECT TO_CHAR(SUBSCRIBER_NO) SUBSCRIBER_ID,
       SUB_STATUS,
       SUB_STS_RSN_CD,
       TO_CHAR(SUB_STATUS_DATE, 'yyyyMMdd') SUB_STATUS_DATE,
       SUBSCRIBER_TYPE
  FROM SUBSCRIBER_FA_V
 WHERE BAN = :1
   AND ROWNUM :2
   AND SUB_STATUS NOT IN ('C', 'L', 'T')
 ORDER BY INIT_ACT_DATE, SUBSCRIBER_NO

如果想做sql检查，对于sql中传入的变量，sql monitor提供了很方便的功能。
Binds

很清晰看到正在执行的sql语句对应的变量值。

还是来看看执行计划。

SQL Plan Monitoring Details (Plan Hash Value=1125972187)

性能瓶颈都在几个全表扫描和一个分析函数相关的rank操作中。毕竟返回的记录数只有1条。如果过多的资源都消耗在一些不正确的连接访问中，是完全可以避免的。

视图的内容如下：
SELECT xxxxxxx
          1 RANK
     FROM subscriber, distribute ed, channel cpc
    WHERE     cpc.pym_channel_no = ed.target_pcn
          AND ed.agreement_no = subscriber.subscriber_no
          AND eg_dist_type = 'D'
          AND ed.expiration_date IS NULL
          AND SUBSCRIBER.SUB_STATUS NOT IN ('C', 'L', 'T')
   UNION
   SELECT xxxxxxx
     FROM (SELECT cpc.ban,
                  subscriber.prim_resource_val,
                  subscriber.init_act_date,
                  SUBSCRIBER.CUSTOMER_ID,
                  subscriber.subscriber_no,
                  SUBSCRIBER.SUBSCRIBER_TYPE,
                  SUBSCRIBER.SUB_STATUS,
                  SUBSCRIBER.SUB_STS_RSN_CD,
                  SUBSCRIBER.SUB_STATUS_DATE,
                  SUBSCRIBER.EFFECTIVE_DATE,
                  ROW_NUMBER ()
                  OVER (
                     PARTITION BY subscriber.subscriber_no
                     ORDER BY
                        ed.expiration_date, subscriber.subscriber_no DESC)
                     AS RANK
             FROM subscriber, distribute ed, channel cpc
            WHERE     cpc.pym_channel_no = ed.target_pcn
                  AND ed.agreement_no = subscriber.subscriber_no
                  AND eg_dist_type = 'D'
                  AND SUBSCRIBER.SUB_STATUS IN ('C', 'L', 'T'))
    WHERE RANK = 1

根据执行计划，出问题的正式标黄的union子句。
根据传入的参数，是可以走索引的，但是在Union子句中，嵌入了子查询，导致在整个视图在数据的访问中，先全表扫描整个子查询，然后再匹配传入的参数。
视图的内容是不能随便改的，可能在这种场景中合适，其他的相关查询就有问题了。
所以尝试把视图的内容直接转换成直接的sql,标黄的部分是做的相应改动。

select xxxxxxx
  from (SELECT xxxxxxx
               1 RANK
          FROM subscriber, event_distribute ed, csm_pay_channel cpc
         WHERE cpc.pym_channel_no = ed.target_pcn
           AND ed.agreement_no = subscriber.subscriber_no
           AND eg_dist_type = 'D'
           AND ed.expiration_date IS NULL
           AND SUBSCRIBER.SUB_STATUS NOT IN ('C', 'L', 'T')
           and cpc.ban = 10308170
        UNION
        SELECT "SUBSCRIBER_NO",
               "INIT_ACT_DATE",
               "SUB_STATUS",
               "SUB_STS_RSN_CD",
               "SUB_STATUS_DATE",
               "SUBSCRIBER_TYPE",
               "RANK"
          FROM (SELECT subscriber.subscriber_no,
                       SUBSCRIBER.INIT_ACT_DATE,
                       SUBSCRIBER.SUBSCRIBER_TYPE,
                       SUBSCRIBER.SUB_STATUS,
                       SUBSCRIBER.SUB_STS_RSN_CD,
                       SUBSCRIBER.SUB_STATUS_DATE,
                       ROW_NUMBER() OVER(PARTITION BY subscriber.subscriber_no ORDER BY ed.expiration_date, subscriber.subscriber_no DESC) AS RANK
                  FROM subscriber, event_distribute ed, csm_pay_channel cpc
                 WHERE cpc.pym_channel_no = ed.target_pcn
                   AND ed.agreement_no = subscriber.subscriber_no
                   AND eg_dist_type = 'D'
                   AND SUBSCRIBER.SUB_STATUS IN ('C', 'L', 'T')
                   and cpc.ban = 10308170)
         WHERE RANK = 1)
 where SUB_STATUS NOT IN ('C','L','T') and rownum
 ORDER BY INIT_ACT_DATE, SUBSCRIBER_ID

看似语句挺长的，但是走了索引执行效率还是很高的。

Elapsed: 00:00:00.01

Statistics

----------------------------------------------------------

         
0  recursive calls

         
0  db block gets

         22  consistent gets

         
0  physical reads

         
0  redo size

       
857  bytes sent via SQL*Net to client

       
520  bytes received via SQL*Net from client

         
2  SQL*Net roundtrips to/from client

         
3  sorts (memory)

         
0  sorts (disk)

         
1  rows processed