生產環境大型sql語句調優實戰第一篇（二）

繼續昨天的部分，上一篇的連結為：
http://blog.itpub.net/23718752/viewspace-1217012/
對這條大sql的效能瓶頸進行了分析。主要瓶頸在於一個很大的業務表，資料量在億級。如果透過時間條件來過濾，會有5%以內的資料被過濾出來。
但是沒有時間相關的索引欄位，所以會走全表掃描，在目前的產品線中，這個大分割槽表的索引時嚴格控制的，所以最後經過測試和比對，還是考慮加並行來提高資料的查取速度。

--查詢效能瓶頸，
根據反饋，查取的資料其實並不錯，可能在幾千條以內的樣子。但是有很多的查詢條件過濾。

如果有些大表走了索引，但是join的消耗很大，很可能就是表的查詢順序不當導致的。
有些情況下使用全表掃描的代價要比使用索引要低。
像這個例子，排查後，logical_date表中雖然有上千條記錄，但是實際上使用的只有一條記錄。
memo這個表是最大的表，由上億條記錄，走了索引。但是join的效率很差，根據排查,memo這個表是這個查詢的關鍵，需要根據時間來得到前一天的資料變化。

如果根據時間來過濾，可以過濾到絕大多數的資料。
上一條記錄過濾後只剩下 74811 rows selected.
如果關聯配置表memo_type查詢的資料就會一下子減少到1713條左右，這是對於效能極大的提升和關鍵。
--考慮加入並行
如果按照時間來查詢，這個大表上沒有和時間相關的欄位，查詢走全表掃描會很長，大概在5分鐘左右。

--without parallel
74811 rows selected.
Elapsed: 00:03:23.10
這個時候如果只能走全表掃描，但是想使得速度能夠提升，可以考慮並行，加入並行後，查詢速度控制在了一分鐘以內。

--add table mo1_memo_type, with parllel 8
1713 rows selected.

--加上配置表的過濾條件，查取的資料更少了，速度也有了提升。
Elapsed: 00:00:41.85
--去除笛卡爾積連線
如果是以Memo表作為首發，表的執行計劃就有了很大的不同，關聯時間時，會不停的去和Logical_date表做關聯，其實Logical_date表裡只需要一條記錄，檢視執行計劃卻走了笛卡爾積連線。
-去除笛卡爾積連線可以考慮採用with的句式，把資料先快取起來，作為後續的查詢，就避免了反覆全表掃描的消耗。
可以把這段子查詢抽取出來，在後續的查詢中直接使用
with LO as  (select logical_date from (select logical_date from logical_date
          where EXPIRATION_DATE IS NULL
   AND LOGICAL_DATE_TYPE = 'B'
   AND EXPIRATION_DATE IS NULL)where rownum<2)

--簡化sql
可以看到sql語句中存在著很多重複的過濾條件，需要考慮在不改變業務的情況下保證語句的簡單易讀。

--減少/去除全表掃描  
嘗試減少或者去除全表掃描，保證效率。
如果透過sql monitor來監控sql語句的效能，可以發現在最後的查取中，對三個表又走了全表掃描。
SQL Plan Monitoring Details (Plan Hash Value=1239783398)

---

Id		Operation	Name	Estimated  Rows	Cost	Active Period  (678s)	Execs	Rows	Memory	Temp	IO Requests	CPU Activity	Wait Activity	Progress
.	0	SELECT STATEMENT	.	.	.	.	1	.	.	.		.	.	.
.	1	. SORT AGGREGATE	.	1	.	. .	1	0	.	.		. 17%		.
->	2	.. HASH JOIN	.	10G	305K	. .	1	4G	7.3MB	.		. 63%		.
.	3	... HASH JOIN	.	76218	260K	. . .	1	90960	.	.		.	.	.
.	4	.... PARTITION RANGE ALL	.	602K	248K	. . .	1	449K	.	.		.	.	.
.	5	..... TABLE ACCESS FULL	BL1_RC_RATES	602K	248K	. . .	11	449K	.	.	. 13526 (36%)	. 1.1%	. 24%	done
.	6	.... TABLE ACCESS FULL	SUBSCRIBER	1M	8495	. . .	1	1M	.	.		.	.	.
->	7	... TABLE ACCESS FULL	CUSTOMER	1M	7441	. .	1	464K	.	.		.16%		. 734s
.	8	. HASH UNIQUE	.	1	469K	.	1	.	.	.		.	.	.
.	9	.. FILTER	.	.	.	.	1	.	.	.		.	.	.
.	10	... PX COORDINATOR	.	.	.	.	17	.	.	.		.

如果對於這部分有所疑惑，可以參見最後select中的這段sql。
(SELECT sum(BR.AMOUNT)
                   FROM BL1_RC_RATES BR, CUSTOMER CU, SUBSCRIBER SS
                  WHERE BR.SERVICE_RECEIVER_ID = SS.SUBSCRIBER_NO
                    AND BR.RECEIVER_CUSTOMER = SS.CUSTOMER_ID
                    AND BR.EFFECTIVE_DATE <= BR.EXPIRATION_DATE 
                  AND((SS. SUB_STATUS <> 'C' and SS.
                            SUB_STATUS <> 'T' and BR.EXPIRATION_DATE is null)
                        OR (SS. SUB_STATUS = 'C' and
                            BR.EXPIRATION_DATE like SS.EFFECTIVE_DATE))
                    AND BR.PP_IND = 'Y'
                    AND BR.CYCLE_CODE = CU.BILL_CYCLE) AS PP_RATE,
                CU.BILL_CYCLE AS CYCLE_CODE,
                to_char(NVL(SS.L9_TMV_ACT_DATE, SS.INIT_ACT_DATE),'YYYYMMDD') AS ACTIVATED_DATE,
                to_char(CD.EFFECTIVE_DATE, 'YYYYMMDD') AS SHOP_EFFECTIVE_DATE,
寫這個sql的人是考慮在最後的資料集返回時，根據bl1_rc_rates來選擇性的返回資料，但是在總查詢中已經關聯了customer,subscriber，在這個地方又關聯就重複了！冗餘的全表掃描就是因為這個導致的。

--子查詢最大程度過濾結果集
可以考慮使用一些儘可能過濾較多資料的子查詢來提高效率。
如果一些表的過濾條件會過濾掉大多數的資料，可以考慮子查詢。
比如表product 根據soc_type來過濾會排除大多數的資料，可以使用如下的方式
( SELECT SOC_CD,SOC_NAME,SOC_DESCRIPTION FROM PRODUCT WHERE  SOC_TYPE='P') co來儘可能直接過濾掉最多的資料。

--觀察執行計劃中表的查取順序。
做了如上的努力之後，發現還是一些全表掃描，效率貌似更差了。根據我的分析，這些表都應該走索引的。

Id		Operation	Name	Estimated Rows	Cost	Active Period  (235s)	Execs	Rows	Memory	Temp	IO Requests	CPU Activity	Wait Activity	Progress
.	0	SELECT STATEMENT	.	.	.	.	.	.	.	.		.	.	.
.	1	. SORT AGGREGATE	.	1	.	.	.	.	.	.		.	.	.
.	2	.. PARTITION RANGE ALL	.	1	3	.	.	.	.	.		.	.	.
.	3	... TABLE ACCESS BY LOCAL INDEX ROWID	BL1_RC_RATES	1	3	.	.	.	.	.		.	.	.
.	4	.... INDEX RANGE SCAN	BL1_RC_RATES_3IX	8	2	.	.	.	.	.		.	.	.
.	5	. HASH UNIQUE	.	1	39T	.	1	.	.	.		.	.	.
->	6	.. FILTER	.	.	.	. .	1	0	.	.		. 3.9%		.
->	7	... PX COORDINATOR	.	.	.	. .	9	260M	.	.		. 17%		.
->	8	.... PX SEND QC (RANDOM)	:TQ10006	20T	30G	. .	8	260M	.	.		. 38%		.
->	9	..... NESTED LOOPS	.	20T	30G	. .	8	260M	.	.		.	.	.
->	10	...... HASH JOIN	.	30G	96M	. .	8	89181	23.4MB	.		.	.	.
.	11	....... BUFFER SORT	.	.	.	. . .	8	152K	.	.		.	.	.
.	12	........ PX RECEIVE	.	16895	40	. . .	8	152K	.	.		.	.	.
.	13	......... PX SEND BROADCAST	:TQ10000	16895	40	. . .	1	152K	.	.		.	.	.
.	14	.......... MAT_VIEW ACCESS FULL	CSM_DEALER	16895	40	. . .	1	18958	.	.		.	.	.
->	15	....... NESTED LOOPS	.	31G	96M	. .	8	89181	.	.		.	.	.
->	16	........ HASH JOIN	.	46M	3M	. .	8	26	498.2MB	.		. .97%		.
.	17	......... BUFFER SORT	.	.	.	. .	8	10M	.	.		. .42%		.
.	18	.......... PX RECEIVE	.	944K	5947	. .	8	10M	.	.		.14%		.
.	19	........... PX SEND BROADCAST	:TQ10001	944K	5947	. . .	1	10M	.	.		.	.	.
.	20	............ TABLE ACCESS FULL	ACCOUNT	944K	5947	. .	1	1M	.	.	. 2224 (8.2%)	.28%	.27%	.

可以使用Hint leading來校正表的訪問順序。
/*+ leading(MO MOT SS CU CHD CPC CA ) */

最後修正後的sql語句如下：

with LO as  (select logical_date from (select logical_date from logical_date
          where EXPIRATION_DATE IS NULL
   AND LOGICAL_DATE_TYPE = 'B'
   AND EXPIRATION_DATE IS NULL)where rownum<2)
SELECT   /*+ leading(MO MOT SS CU CHD CPC CA ) */ DISTINCT CA.L9_CONVERGENCE_CODE AS ATB2,
                CU.CUST_SUB_TYPE AS ACCOUNT_TYPE,
                CST.DESCRIPTION AS ACCOUNT_TYPE_DESC,
                SS.PRIM_RESOURCE_VAL AS MSISDN,
                CA.BAN AS BAN_KEY,
                to_char(MO.MEMO_DATE, 'YYYYMMDD') AS MEMO_DATE,
                CU.L9_IDENTIFICATION AS THAI_ID,
                SS.SUBSCRIBER_NO AS SUBS_KEY,
                SS.DEALER_CODE AS SHOP_CODE,
                CD.DESCRIPTION AS SHOP_NAME,
                MOT.SHORT_DESC,
                REGEXP_SUBSTR(MO.ATTR1VALUE, '[^ ;]+', 1, 3) STAFF_ID,
                MO.OPERATOR_ID AS USER_ID,
                MO.MEMO_SYSTEM_TEXT,
                CO2.SOC_NAME AS FIRST_SOCNAME,
                CO3.SOC_NAME AS PREVIOUS_SOCNAME,
                CO.SOC_NAME AS CURRENT_SOCNAME,
                REGEXP_SUBSTR(MO.ATTR1VALUE, '[^ ; ]+', 1, 1) NAME,
                CO.SOC_DESCRIPTION AS CURRENT_PP_DESC,
                CO3.SOC_DESCRIPTION AS PREV_PP_DESC,
                CO.SOC_CD AS SOC_CD,
                (SELECT sum(BR.AMOUNT)
                   FROM BL1_RC_RATES BR,-- CUSTOMER CU, SUBSCRIBER SS  --去除冗餘的全表掃描
                  WHERE BR.SERVICE_RECEIVER_ID = SS.SUBSCRIBER_NO
                    AND BR.RECEIVER_CUSTOMER = SS.CUSTOMER_ID
                    AND BR.EFFECTIVE_DATE <= BR.EXPIRATION_DATE 
                  AND((SS. SUB_STATUS <> 'C' and SS.
                            SUB_STATUS <> 'T' and BR.EXPIRATION_DATE is null)
                        OR (SS. SUB_STATUS = 'C' and
                            BR.EXPIRATION_DATE like SS.EFFECTIVE_DATE))
                    AND BR.PP_IND = 'Y'
                    AND BR.CYCLE_CODE = CU.BILL_CYCLE) AS PP_RATE,
                CU.BILL_CYCLE AS CYCLE_CODE,
                to_char(NVL(SS.L9_TMV_ACT_DATE, SS.INIT_ACT_DATE),'YYYYMMDD') AS ACTIVATED_DATE,
                to_char(CD.EFFECTIVE_DATE, 'YYYYMMDD') AS SHOP_EFFECTIVE_DATE,
                CD.EXPIRATION_DATE AS SHOP_EXPIRED_DATE,
                CA.L9_COMPANY_CODE AS COMPANY_CODE
  FROM SERVICE_DETAILS S, --大分割槽表，千萬級資料量，存放著交易的明細資訊
       ( SELECT SOC_CD,SOC_NAME,SOC_DESCRIPTION FROM PRODUCT WHERE  SOC_TYPE='P')  CO,  --產品配置表，大概幾萬條左右
       CSM_PAY_CHANNEL   CPC, --賬務相關表，百萬級
       ACCOUNT       CA,  --賬務相關表，百萬級
       SUBSCRIBER        SS, --使用者相關表，百萬級
       CUSTOMER          CU, --使用者相關表，百萬級
       CUSTOMER_SUB_TYPE CST, --使用者配置表，幾千條資料
       CSM_DEALER        CD, --產品配置表，大概幾千條左右
       SERVICE_DETAILS S2,
       ( SELECT SOC_CD,SOC_NAME,SOC_DESCRIPTION FROM PRODUCT WHERE  SOC_TYPE='P')  CO2,  --產品配置表，大概幾萬條左右
       SERVICE_DETAILS S3,
       ( SELECT SOC_CD,SOC_NAME,SOC_DESCRIPTION FROM PRODUCT WHERE  SOC_TYPE='P')  CO3,  --產品配置表，大概幾萬條左右
       (select /*+ parallel(T 8)*/ 
       MEMO_ID,ENTITY_ID,MEMO_TYPE_ID,ATTR1VALUE,OPERATOR_ID,MEMO_SYSTEM_TEXT,MEMO_DATE from 
       MO1_MEMO          T
    WHERE    T.ENTITY_TYPE_ID = 6
     AND TRUNC(T.SYS_CREATION_DATE) = (select TRUNC(LO.LOGICAL_DATE - 1) from lo)
     ) MO ,   --交易備註表，資料量過億
       MEMO_TYPE     MOT, --配置表，資料量幾千
      -- LOGICAL_DATE      LO, --時間配置表，資料量1千多
       CHARGE_DETAILS CHD --交易表，資料量千萬
 WHERE SS.SUBSCRIBER_NO = CHD.AGREEMENT_NO  
   AND CPC.PYM_CHANNEL_NO = CHD.TARGET_PCN
   AND CHD.CHG_SPLIT_TYPE = 'DR'
   AND CHD.EXPIRATION_DATE IS NULL
   AND S.SOC = CO.SOC_CD
   AND CO.SOC_TYPE = 'P'
   AND S.AGREEMENT_NO = SS.SUBSCRIBER_NO
   AND SS.PRIM_RESOURCE_TP = 'C'
   AND CPC.PAYMENT_CATEGORY = 'POST'
   AND CA.BAN = CPC.BAN
   AND (CA.L9_COMPANY_CODE = 'RF' OR CA.L9_COMPANY_CODE = 'RM' OR
       CA.L9_COMPANY_CODE = 'TM')
   AND SS.CUSTOMER_ID = CU.CUSTOMER_ID
   AND CU.CUST_SUB_TYPE = CST.CUST_SUB_TYPE
   AND CU.CUSTOMER_TYPE = CST.CUSTOMER_TYPE
   AND SS.DEALER_CODE = CD.DEALER
   AND S2.EFFECTIVE_DATE= (SELECT MAX(SA1.EFFECTIVE_DATE)
                             FROM SERVICE_DETAILS SA1--, PRODUCT o1 --去除冗餘的表連線
                            WHERE SA1.AGREEMENT_NO = SS.SUBSCRIBER_NO
                              AND co.soc_cd = sa1.soc
                             -- and co.soc_type = 'P'
                              )
   AND S2.AGREEMENT_NO = S.AGREEMENT_NO
   AND S2.SOC = CO2.SOC_CD
   AND CO2.SOC_TYPE = 'P'
   AND S2.EFFECTIVE_DATE = (SELECT MIN(SA1.EFFECTIVE_DATE)
                             FROM SERVICE_DETAILS SA1--, PRODUCT o1
                            WHERE SA1.AGREEMENT_NO = SS.SUBSCRIBER_NO
                              AND co2.soc_cd = sa1.soc
                             -- and co2.soc_type = 'P'
                              )
   AND S3.AGREEMENT_NO = S.AGREEMENT_NO
   AND S3.SOC = CO3.SOC_CD
   AND CO3.SOC_TYPE = 'P'
   AND S3.EFFECTIVE_DATE =
       (SELECT MAX(SA1.EFFECTIVE_DATE)
          FROM SERVICE_DETAILS SA1, PRODUCT o1
         WHERE SA1.AGREEMENT_NO = SS.SUBSCRIBER_NO
           AND SA1.EFFECTIVE_DATE <
               (SELECT MAX(SA1.EFFECTIVE_DATE)
                  FROM SERVICE_DETAILS SA1--, PRODUCT o1
                 WHERE SA1.AGREEMENT_NO = SS.SUBSCRIBER_NO
                   and co3.soc_cd = sa1.soc
                --   and co3.soc_type = 'P'
                   )
           and co3.soc_cd = sa1.soc
           --and co3.soc_type = 'P'
           )           
   AND MO.ENTITY_ID = SS.SUBSCRIBER_NO
   AND MO.ENTITY_TYPE_ID = 6
   AND MO.MEMO_TYPE_ID = MOT.MEMO_TYPE_ID
  -- AND TRUNC(MO.SYS_CREATION_DATE) = (select TRUNC(LO.LOGICAL_DATE - 1) from lo)
 --  TRUNC(LO.LOGICAL_DATE - 1)
 --  AND LO.EXPIRATION_DATE IS NULL
  -- AND LO.LOGICAL_DATE_TYPE = 'B'
   --AND LO.EXPIRATION_DATE IS NULL
   AND (MOT.SHORT_DESC = 'BCN' OR MOT.SHORT_DESC = 'BCNM' OR
     ............
  )