一次sql優化小記

 

問題提出：某一應用db伺服器遷移到idc後，總部基礎資料同步到這臺db上整個同步耗時在4個小時以上，使用者難以接受，觀察到這個現象後，即著手對同步基礎資料的儲存過程做優化調整，下面的調整優化以同步中的某一個大表ka_detail為例，列出優化的步驟和方法，最終的調整結果：過程執行時間由2個小時以上，優化為現在的30秒左右。記錄文件，供參考。

 

檢查同步過程，發現因業務系統的限制，同步時間主要消耗在本地table和通過dblink的遠端table的delete，update，insert上，此外遠端table上有trigger，也是一個因素;整個同步時間耗時過長，和資料倉儲db每天的停機冷備份衝突，最終可能會導致基礎資料還沒有同步結束的時候，資料倉儲db停機做冷備份，進而導致同步過程失敗，影響下游系統的作業。

 

一：檢視當前狀況下同步ka單店過程執行的時間

14:10:44 SQL> exec syn_fenxiao_ka_detail;

start syn_fenxiao_ka_detail time0 is:20071219 14:14:09

start syn_fenxiao_ka_detail time1 is:20071219 14:14:11

insert and update of ka_detail synchronize is unsuccessful!!!-0008-SUG0632

begin syn_fenxiao_ka_detail_before; end;

ORA-01013: user requested cancel of current operation

16:38:34 SQL>

 

同步過程exec syn_fenxiao_ka_detail 執行了2個多小時還沒有完成，手工停止。

 

 

二:第一次優化過程，用merge語句代替insert，update 語句

檢視ka_detail 表的記錄數

SQL> select count(*) from ka_detail;

 

  COUNT(*)

----------

     11822

該表有近12000筆記錄，為基礎資料中最大的一個表。分析同步該表的過程syn_fenxiao_ka_detail ，過程中的update和insert語句可以用merge 語句代替，只需要一次全表掃描，將會較大程度的提高過程的執行效果(oracle 9i 新增的merge 語句，對於資料倉儲中經常用的資料批量匯入更新，可以較好的提高sql語句的效率)，過程修改後的執行情況如下

10:48:53 SQL> exec syn_fenxiao_ka_detail;

start syn_fenxiao_ka_detail time0 is:20071219 10:52:23

start syn_fenxiao_ka_detail time1 is:20071219 11:29:48

start syn_fenxiao_ka_detail time2 is:20071219 11:29:55

start syn_fenxiao_ka_detail time2 is:20071219 11:29:55

syn_fenxiao_ka_detail synchronize successful!!!

可以看到，第一次優化後的執行時間將在40分鐘左右，過程的執行效果有了很大提高

 

三：第二次優化過程

再次分析過程syn_fenxiao_ka_detail，對程式進行分段測試，確定40分鐘的耗時在每個子程式段的分佈情況（可以採用分段測試，增加dbms_output.put_line,來確定過程執行的瓶頸正在那個子程式段上，或者利用作業系統的topas/top，結合v$session,v$process,v$sqltext ，可以確定耗用資源最多的，佔用時間最長的sql語句，對此語句進行優化）。

start syn_fenxiao_ka_detail time0 is:20071219 10:52:23

start syn_fenxiao_ka_detail time1 is:20071219 11:29:48

觀察上面的測試輸出，找到對應的程式段，可以發現執行時間絕大部分為delete語句。

找到對應的語句，分析該語句的執行計劃和統計資訊，如下

SQL>  delete ka_detail@lfxpub a where not Exists

  2   (select 'x' from ka_detail b where a.ka_detail=b.ka_detail);

 

已刪除0行。

 

 

統計資訊

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

      23628  recursive calls

          0  db block gets

      23624  consistent gets

          0  physical reads

          0  redo size

        518  bytes sent via SQL*Net to client

        394  bytes received via SQL*Net from client

          4  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

          0  rows processed

 

consisten  read 非常大，這麼大的邏輯讀是造成同步耗時的主要原因，應考慮優化（需要參考sql的執行計劃）。最終優化後的語句的執行計劃和統計資訊如下

SQL> delete ka_detail@lfxpub where ka_detail  in

  2  (

  3  select ka_detail from ka_detail@lfxpub

  4  minus

  5  select ka_detail from ka_detail

  6  );

已刪除0行。

統計資訊

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

          6  recursive calls

          0  db block gets

         93  consistent gets

          0  physical reads

          0  redo size

        532  bytes sent via SQL*Net to client

        414  bytes received via SQL*Net from client

          4  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

          0  rows processed

 

可以看到，優化後sql的consisten read 降低了大概200倍，效率有了極大提高。

 

至此，表ka_detail的同步過程優化結束，其他過程，亦可參照處理。

 

SQL> set serveroutput on;

SQL> set time  on;

14:24:59 SQL> exec syn_pesd_ka_detail;

 

insert and update of ka_detail synchronize successful!!!

 

PL/SQL procedure successfully completed

 

14:25:31 SQL>

 

 

 

 

 

 

 

 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10159839/viewspace-149075/，如需轉載，請註明出處，否則將追究法律責任。