記一次卓有成效的SQL優化

1.1      環境

Oracle 9i 9.2.0.4 64 bit

Linux as 4.7

1.2      優化物件：一個過程

pkg_lr_dt01.p_update_tb021

業務非常複雜，程式碼很長，這裡就不貼了。

這個過程中的SQL大致的意思是通過檢視v_jt_tb021來更新表tb021中的資料，v_jgb_tb021中有7個表關聯，表中的記錄數：
select count(*) from  tb_pub103;--145938
select count(*) from  tb003;--302457
select count(*) from  tb008;--304167
select count(*) from  tb011;--335287
select count(*) from  tb_pub205;--49460
select count(*) from  tb002;--7140
select count(*) from  tb010;--806

通過v_jt_tb021中的各個欄位與已生成的表tb021現有記錄進行各個欄位的一一比較，看是否需要更新tb021中的記錄，或插入，或更新，或刪除，比較的SQL有3個。這3個SQL大致的包含以下幾個重要的操作

有8個排序操作，其中6個"LAG OVER"，一個"MIN OVER",還有一個"ROW_NUMBER OVER"，通過v$sessstat和v$statname跟蹤發現每次執行需要排序的工作量“sorts (rows) 3355663“。

1.3      優化過程

1.3.1    原始執行計劃

SELECT STATEMENT, GOAL = CHOOSE           Cost=49092 Cardinality=1 IO cost=49092

 HASH JOIN           Cost=49092 Cardinality=1 IO cost=49092

  TABLE ACCESS FULL  Object wner=JDB  Object name=TB021 Cost=923   Cardinality=159136   IO cost=923

  VIEW Object wner=JDB     Cost=39969 Cardinality=330413   

   WINDOW SORT PUSHED RANK         Cost=39969 Cardinality=330413   IO cost=39969

    WINDOW SORT         Cost=39969 Cardinality=330413   IO cost=39969

     HASH JOIN          Cost=7202  Cardinality=330413   IO cost=7202

      TABLE ACCESS FULL Object wner=JDB  Object name=TB010 Cost=3 Cardinality=797   IO cost=3

      HASH JOIN OUTER          Cost=7175  Cardinality=293087   IO cost=7175

       HASH JOIN OUTER         Cost=5739  Cardinality=235732   IO cost=5739

        HASH JOIN           Cost=4760  Cardinality=218201   IO cost=4760

         TABLE ACCESS FULL  Object wner=JDB  Object name=TB_PUB103    Cost=710   Cardinality=72923   IO cost=710

         HASH JOIN          Cost=3198  Cardinality=210086   IO cost=3198

          TABLE ACCESS FULL Object wner=JDB  Object name=TB003 Cost=1684  Cardinality=149810  IO cost=1684

          HASH JOIN         Cost=882   Cardinality=169211   IO cost=882

           TABLE ACCESS FULL   Object wner=JDB  Object name=PUB205   Cost=183   Cardinality=38050   IO cost=183

           TABLE ACCESS FULL   Object wner=JDB  Object name=TB011 Cost=504   Cardinality=164180  IO cost=504

        TABLE ACCESS FULL   Object wner=JDB  Object name=TB002 Cost=19    Cardinality=6690    IO cost=19

       TABLE ACCESS FULL    Object wner=JDB  Object name=TB008 Cost=231   Cardinality=149596  IO cost=231

通過執行計劃發現幾個表之間的關聯採用的時hash_join的連線方式，單個SQL的執行時間也不是很長，在10秒內能執行完，各個表都用了全表掃描，原因很簡單，因為過濾條件很少，要有的話過濾的資料不多，所以用全表掃描的方式效率相對較高，考慮到是每5分鐘執行一次，頻率有點高，所以先把表快取到快取中，執行如下操作：“alter table table_name storage(buffer_pool keep);”

1.3.2    快取表之後

經過上述快取之後，發現仍然有大量的物理讀，每次執行都在16W多，對磁碟壓力還是很大，通過10053跟蹤看到有大量的'direct path write' 和'direct path read'等待，具體如下： 

WAIT #1: nam='direct path write' ela= 9 p1=501 p2=3237513 p3=31

WAIT #1: nam='direct path write' ela= 3 p1=501 p2=3237575 p3=31

WAIT #1: nam='direct path write' ela= 4 p1=501 p2=3400713 p3=31

WAIT #1: nam='direct path write' ela= 3 p1=501 p2=3400775 p3=31

......

WAIT #1: nam='direct path read' ela= 2 p1=501 p2=3454887 p3=1

WAIT #1: nam='direct path read' ela= 2 p1=501 p2=3454888 p3=1

WAIT #1: nam='direct path read' ela= 1 p1=501 p2=3198665 p3=1

WAIT #1: nam='direct path read' ela= 1 p1=501 p2=3198666 p3=1

果然驗證了當初的想法，排序引起了大量的物理讀和邏輯讀，而對錶本身的查詢並沒有產生物理讀。

1.3.3    對session設定workarea_size_policy=MANUAL

回顧一下基礎，在oracle 9i後，workarea_size_policy預設為"auto",在workarea_size_policy=AUTO的情況下，所有會話的UGA共用pga_aggregate_target定義的記憶體，不必單獨設定sort_area_size,單個session最大能申請到5%的pga，比如我們的pga是2.4G，那麼意味著一個session最大能能用到120M的記憶體，但這裡有“sorts (rows) 3355663“，記憶體不夠用，所以只能先輸出到temp上去了，等下用的時候還得把temp上的結果“read”回來，所以產生了大量的物理讀寫。

因此對session設定私有的sort_area_size，即在procedure中設定：
procedure開始的地方：
   EXECUTE IMMEDIATE 'ALTER session SET workarea_size_policy=manual';
     EXECUTE IMMEDIATE 'ALTER session SET sort_area_size=419430400';
procedure結束的地方：
    EXECUTE IMMEDIATE 'ALTER session SET sort_area_size=0';
       EXECUTE IMMEDIATE 'ALTER session SET workarea_size_policy=auto';
PS:結束的地方也可以不設定，因為會話結束後會自動釋放，但是手動釋放相對嚴謹。

 

再次執行過程跟蹤發現物理讀寫都為0（v$sessstat和v$statname關聯起來可以看），通過iostat監控發現tps大幅降低，

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 171.19 805.30 1486.09 2432 4488
sda1 0.00 0.00 0.00 0 0
sda2 0.00 0.00 0.00 0 0
sda3 171.19 805.30 1486.09 2432 4488

avg-cpu: %user %nice %sys %iowait %idle
          20.75 0.00 4.17 0.42 74.67

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 236.79 588.63 2145.82 1760 6416
sda1 0.00 0.00 0.00 0 0
sda2 0.00 0.00 0.00 0 0
sda3 236.79 588.63 2145.82 1760 6416

avg-cpu: %user %nice %sys %iowait %idle
          16.83 0.00 3.50 0.33 79.34

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 102.66 252.49 1142.86 760 3440
sda1 0.00 0.00 0.00 0 0
sda2 0.00 0.00 0.00 0 0
sda3 102.66 252.49 1142.86 760 3440
原來空的時候都要400~800質檢，忙的時候要過1500，現在總算有點暗淡了。

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