生產環境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

---

Name	Position	Type	Value
:B2	1	NUMBER	10308170
:B1	2	NUMBER	6

很清晰看到正在執行的sql語句對應的變數值。

還是來看看執行計劃。

SQL Plan Monitoring Details (Plan Hash Value=1125972187)

---

Id		Operation	Name	Estimated Rows	Cost	Active Period  (11s)	Execs	Rows	Memory (Max)	Temp  (Max)	IO Requests	CPU Activity	Wait Activity
.	0	SELECT STATEMENT	.	.	.	. .	1	1	.	.		.	.
.	1	. SORT ORDER BY	.	6	67758	. .	1	1	2.0KB	.		.	.
.	2	.. COUNT STOPKEY	.	.	.	. .	1	1	.	.		.	.
.	3	... VIEW	SUBSCRIBER_FA_V	577K	59898	. .	1	1	.	.		.	.
.	4	.... SORT UNIQUE STOPKEY	.	577K	59898	. .	1	1	2.0KB	.		.	.
.	5	..... UNION-ALL	.	.	.	. . .	1	1	.	.		.	.
.	6	......NESTED LOOPS	.	.	.	. . .	1	1	.	.		.	.
.	7	.......NESTED LOOPS	.	1	3	. . .	1	1	.	.		.	.
.	8	........NESTED LOOPS	.	1	2	. . .	1	1	.	.		.	.
.	9	.........INDEX RANGE SCAN	CHANNEL_1IX	1	1	. . .	1	1	.	.		.	.
.	10	.........TABLE ACCESS BY INDEX ROWID	DISTRIBUTE	1	1	. . .	1	1	.	.		.	.
.	11	..........INDEX RANGE SCAN	DISTRIBUTE_3IX	1	1	. . .	1	1	.	.		.	.
.	12	........INDEX UNIQUE SCAN	SUBSCRIBER_PK	1	1	. . .	1	1	.	.		.	.
.	13	....... TABLE ACCESS BY INDEX ROWID	SUBSCRIBER	1	1	. . .	1	1	.	.		.	.
.	14	...... VIEW	.	577K	35591	. .	1	0	.	.		. 20%
.	15	.......WINDOW SORT PUSHED RANK	.	577K	35591	. .	1	522K	21.2MB	63.0MB	. . 627 (38%)	. 40%	. 33%
.	16	........ HASH JOIN	.	577K	23809	. .	1	550K	23.4MB	88.0MB	. . 390 (23%)		. . 66%
.	17	.........INDEX FULL SCAN	CHANNEL_1IX	1M	1269	. . .	1	1M	.	.		.	.
.	18	.........HASH JOIN	.	577K	18648	. . .	1	550K	30.7MB	40.0MB	. 53 (3.2%)	. 20%
.	19	..........TABLE ACCESS FULL	SUBSCRIBER	430K	8704	. . .	1	424K	.	.		. 20%
.	20	..........TABLE ACCESS FULL	DISTRIBUTE	2M	6046	. . .	1	2M	.	.		.	.

效能瓶頸都在幾個全表掃描和一個分析函式相關的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 < 6
 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