生產環境sql語句調優實戰第八篇

生產環境中的sql語句執行時間是很關鍵的效能指標,如果某個sql語句執行幾個小時,最佳化以後幾分鐘，幾十秒的話。會有很大的成就感，同時如果某個sql語句執行10秒，能夠最佳化到1秒，感覺提升的幅度不是很大，但是如果這條語句執行極為頻繁的話，那這種調優還是更有成就感的。
執行時間是sql調優的一把標尺，但是同時也需要考慮到系統資源的平衡。
今天在系統中發現一條sql語句執行時間很長。平均一個查詢要執行一個半小時左右，而且系統的資源消耗極大。
需要說明的service_details 是資料量過億的表。ch_distribute 是千萬級的表，subscriber是百萬級的表。
payment是千萬級的表,paychannel是百萬級的表。
檢視執行計劃，倒看不出有明顯的異常，這也就是執行計劃的一個誤區了，我們不能總是參考執行計劃來進行調優，很多時候發現執行計劃幾乎是完美的，但是執行效率卻很長。

sql語句如下所示。
SELECT cd.target_pcn, se.agreement_no, s.subscriber_no, s.prim_resource_val
  FROM ch_distribute CD, service_details SE, subscriber S
 WHERE cd.target_pcn IN
       (SELECT 
         cp.pym_channel_no
          FROM paychannel cp, payment pym
         WHERE cp.pym_channel_no IN
               (SELECT cd.target_pcn
                  FROM ch_distribute cd
                 WHERE (cd.agreement_no, cd.soc, cd.soc_seq_no) IN
                       (SELECT sg.agreement_no, sg.soc, sg.soc_seq_no
                          FROM service_details sg
                         WHERE sg.soc_status = 'A'
                           AND sg.agreement_no IN
                               (SELECT 
                                 sg.agreement_no
                                  FROM service_details sg, subscriber s
                                 WHERE s.subscriber_no = sg.agreement_no
                                   AND sg.soc = 50412
                                   AND sg.soc_status = 'A'
                                   AND sg.soc_sts_rsn_cd =
                                       (SELECT param_values
                                          FROM small_table
                                         WHERE param_name =
                                               'XXXXXXXX1'
                                           AND job_name = 'XXXXXX')   --bottleneck 
                                   AND s.subscriber_type IN
                                       (SELECT param_values
                                          FROM small_table
                                         WHERE param_name = 'XXXXXXXX2'
                                           AND job_name = 'XXXXXX')
                                           ))
                   AND expiration_date IS NULL)
           AND cp.ban = pym.account_id
           AND (pym.transaction_id >
               (SELECT param_values
                   FROM small_table
                  WHERE param_name = 'XXXXXXXX3'
                    AND job_name = 'XXXXXX') AND
               pym.transaction_id <= 255004442))
   AND se.agreement_no = cd.agreement_no
   AND s.subscriber_no = se.agreement_no
 GROUP BY cd.target_pcn,
          se.agreement_no,
          s.subscriber_no,
          s.prim_resource_val
          

猛一看這個查詢語句還是挺臃腫的,可以明顯的看到反覆引用了大表service_details,chg_distribute.
我先把這個問題發給一個效能調優的哥們，他在不改動sql語句的前提下，加了幾個Hint,執行時間就從1個半小時降低到4分鐘左右，猛一看這是一個極大的提升，看似不用修改sql語句了。
我看了下他建議的hint,從執行時間來說，是很大的提升，但是從系統的資源消耗來看，還存在一定的隱患，建議的Hint如下：
SELECT /*+parallel(pym,4) full(pym) use_hash(pym)*/
         cp.pym_channel_no

SELECT /*+PARALLEL(S,4) full(S) FULL(SG) PARALLEL(SG,4) USE_HASH(S,SG)*/
                                 sg.agreement_no
                                  FROM service_details sg, subscriber s

兩個Hint本身也沒有什麼問題，對於大表的關聯用hash_join效率比nested loop要高很多。加上並行，如果查詢執行不夠頻繁，涉及的表不多，確實是很好的選擇。
我個人的觀點還是從語句本身入手，先來看看有什麼可以從結構中的改進，先在頭腦中有一個基本的思路，然後主要查詢資料的效能瓶頸到底在哪，因為根據在備份庫上的測試，這個查詢返回的資料條數在幾千條左右，從上億條，上千萬的資料中排查出幾千條肯定是有一些關鍵的過濾條件。
使用並行固然好，如果在不使用並行的條件下，高效的使用索引是更好的選擇。如果實在條件所限，對個別做表全表掃描速度也是很快的。
在分析了資料的統計資訊，索引情況之後，在備份庫中進行了簡單的資料篩查。
首先定位了效能瓶頸，是如下的這個查詢條件。透過如下的條件能夠過濾掉99%以上的資料，剩下的資料和其它大表關聯，都是可以使用到索引的，速度就會快很多。
select *from agreement_no from service_details  sg
        where 
     sg.soc = 50412 and sg.soc_status = 'A' 

有了這個思路，修改起來就輕鬆多了.
有了主要的改進，其他的改進就可以錦上添花了。
還有兩個需要修改的部分。
一個是簡化sql語句的表關聯，可以看到很多的表出現了多次，這對查詢本身來說也不是必須的，個人認為這個sql語句是在開發人員邊開發，變修改導致了查詢語句巢狀了很多重複的關聯。
一個是關於子查詢的最佳化。有幾個子查詢會關聯到一個小表，對小表中的資料進行反覆關聯。這對子查詢而言，執行頻率是極高的。
SELECT param_values
                                          FROM small_table
                                         WHERE param_name =
                                               'XXXXXXXX1'
                                           AND job_name = 'XXXXXX'
對於這種特別的子查詢，可以考慮使用with語句來替代。改進後的語句如下，這樣看就清晰多了。
with ssrc as (SELECT param_values
                   FROM small_table
                  WHERE param_name = 'XXXXXXXX1'
                    AND job_name = 'XXXXXX') ,
       sub_type as (SELECT param_values
                   FROM small_table
                  WHERE param_name = 'XXXXXXXX2'
                    AND job_name = 'XXXXXX'),
      hr_pay_trx as (SELECT param_values
                   FROM small_table
                  WHERE param_name = 'XXXXXXXX3'
                    AND job_name = 'XXXXXX')     
SELECT
           cp.pym_channel_no,sg.agreement_no, s.subscriber_no, s.prim_resource_val
           FROM service_details sg, subscriber s,chg_distribute chg,paychannel cp,sub_type,ssrc
          WHERE s.subscriber_no = sg.agreement_no
            AND sg.soc = 50412
            AND sg.soc_status = 'A'
            AND sg.soc_sts_rsn_cd =
                ssrc.param_values --bottleneck 
            AND s.subscriber_type =sub_type.param_values
            and sg.agreement_no=chg.agreement_no
            and sg.soc=chg.soc
            and sg.soc_seq_no=chg.soc_seq_no
            and chg.expiration_date is null
            and cp.pym_channel_no=chg.target_pcn
            and exists(
            select 1 from ar1_payment pym,hr_pay_trx            
            where cp.ban = pym.account_id
           AND (pym.transaction_id > hr_pay_trx.param_values
                AND
               pym.transaction_id <= :1))

可以看到from後面跟了好幾個大表，但是效能瓶頸在service_details上所以為了保險起見，我們可以使用hint來指定表的訪問順序。先過濾到99%以上的資料，剩下的就可以自然的走索引掃描了。
新增的Hint如下,對於表service_details,因為沒法使用到索引，所以就對這一個表進行全表掃描，走個並行。
SELECT /*+leading(ssrc,sub_type,sg,s,chg,cp) parallel(sg 4) full(sg)*/
           cp.pym_channel_no,sg.agreement_no, s.subscriber_no, s.prim_resource_val

最後在備份庫中測試，效果果然很明顯，備份庫中速度從最佳化後的4分鐘降低到2分鐘。
在生產環境中執行，速度更快，穩定在40秒左右。