DB2中預存程序執行慢問題故障處理

其實這個問題是以前同一個客戶遇見的問題，當時一個工程師解決後記錄的過程如下：

應用同事反映但是對應到執行預存程序，執行了2，3個小時了，還沒出來結果。

預存程序主要是執行一條update sql語句，單獨將語句拿出來，clp命令列執行很快，2－3s即可執行完成。
 
執行的SP：
call pdw.P_OCS_ACTIVE_UPDATE('20120304',?)
 
預存程序主要業務SQL：
 /***********************開始實現業務******************************/
   /*******/
SET vn_Step=1;--
call papp.p_debug(txdate,vn_PrcName,vn_Step,vv_sql); --
  UPDATE PODS.T_ODS_PAR_CUSTMSG a
  SET run_code='UU'
WHERE sm_code in ('o3','os','om','ol','ox') AND NOT EXISTS
  (SELECT * FROM PODS.T_ODS_PAR_OCSACTIVEMSG b where a.ID_NO=b.ID_NO );
 
COMMIT;--
 /************************結束商務邏輯******************************/
 
 
根據瞭解，這個預存程序有些時候了，最近無改動，以前都正常。
 
根據具體情況，第一反應是這個預存程序中語句的執行計畫不正確了。靜態sql的訪問計劃是在第一次編譯後儲存在資料庫的包中的，之後運行都是使用包中的執行計畫。解決辦法很簡單，對預存程序包重新綁定一下，使用最新的資料庫統計資訊產生最新的訪問計劃。
 
先查出此預存程序對應的包：
SELECT   bname,
         pkgname,
         BSCHEMA
FROM     syscat.packagedep
WHERE    btype='T'
AND      pkgname in(select bname from sysibm.sysdependencies where dname in (select specificname from syscat.procedures where procname='P_OCS_ACTIVE_UPDATE'
        AND PROCSCHEMA='PDW'))
[DWE3:/tmp]db2 "SELECT   bname,
>          pkgname,
>          BSCHEMA
> FROM     syscat.packagedep
> WHERE    btype='T'
> AND      pkgname in(select bname from sysibm.sysdependencies where dname in (select specificname from syscat.procedures where procname='P_OCS_ACTIVE_UPDATE'
>         AND PROCSCHEMA='PDW'))
> "
BNAME                         BSCHEMA   
--------------------------------------------------------------------------------
T_ODS_PAR_CUSTMSG             P8414315                             PODS                               
T_ODS_PAR_OCSACTIVEMSG        P8414315                             PODS                                                                                                                          
 2 record(s) selected.

對包執行rebind，操作前需連庫
[DWE3:/tmp]time db2 rebind package pdw.P8414315
DB20000I  The REBIND PACKAGE command completed successfully.
real    0m0.63s
user    0m0.02s
sys     0m0.00s
 
對預存程序包重新綁定後，預存程序幾秒鐘即執行完成，恢複正常。
 
 
拿出sql單獨執行計畫：
[DWE3:/newdb2home/db2inst3/fengsh]more 2.out
DB2 Universal Database Version 9.1, 5622-044 (c) Copyright IBM Corp. 1991, 2006
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool
******************** DYNAMIC ***************************************
==================== STATEMENT ==========================================
        Isolation Level          = Cursor Stability
        Blocking                 = Block Unambiguous Cursors
        Query Optimization Class = 5
        Partition Parallel       = Yes
        Intra-Partition Parallel = No
        SQL Path                 = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM",
                                   "DB2INST3"

Statement:
 
  UPDATE PODS.T_ODS_PAR_CUSTMSG a SET run_code='UU'
  WHERE sm_code in ('o3' , 'os' , 'om' , 'ol' , 'ox' )AND NOT EXISTS
     (SELECT *
     FROM PODS.T_ODS_PAR_OCSACTIVEMSG b
     where a.ID_NO=b.ID_NO )

Section Code Page = 1386
Estimated Cost = 1512832.000000
Estimated Cardinality = 183703.968750
Coordinator Subsection - Main Processing:
   Distribute Subsection #1
   |  Broadcast to Node List
   |  |  Nodes = 1, 2, 3, 4, 5, 6, 7
Subsection #1:
   Access Table Name = PODS.T_ODS_PAR_OCSACTIVEMSG  ID = 11,3174
   |  #Columns = 1
   |  Skip Inserted Rows
   |  Skip Deleted Rows
   |  Relation Scan
   |  |  Prefetch: Eligible
   |  Lock Intents
   |  |  Table: Intent Share
   |  |  Row  : Next Key Share
   |  Sargable Predicate(s)
   |  |  Process Build Table for Hash Join
   Anti Left Outer Hash Join
   |  Early Out: Single Match Per Outer Row
   |  Estimated Build Size: 7584000
   |  Estimated Probe Size: 6960000
   |  Access Table Name = PODS.T_ODS_PAR_CUSTMSG  ID = 11,155
   |  |  #Columns = 3
   |  |  Skip Inserted Rows
   |  |  Skip Deleted Rows
   |  |  Evaluate Block/Data Predicates Before Locking Row
   |  |  Relation Scan
   |  |  |  Prefetch: Eligible
Isolation Level: Read Stability
   |  |  Lock Intents
   |  |  |  Table: Intent Exclusive
   |  |  |  Row  : Update
   |  |  Sargable Predicate(s)
   |  |  |  #Predicates = 1
   |  |  |  Process Probe Table for Hash Join
   Establish Row Position
   |  Access Table Name = PODS.T_ODS_PAR_CUSTMSG  ID = 11,155
   Update:  Table Name = PODS.T_ODS_PAR_CUSTMSG  ID = 11,155
End of section

Optimizer Plan:
                                                  UPDATE
                                                  (   2)
                                       /---------/      \
                                 FETCH                 Table:          
                                 (   3)                PODS            
                        /-------/      \               T_ODS_PAR_CUSTMSG
                  HSJOIN            Table:          
                  (   4)            PODS            
             /---/      \--\        T_ODS_PAR_CUSTMSG
       TBSCAN               TBSCAN
       (   5)               (   6)
         |                    |  
 Table:             Table:               
 PODS               PODS                 
 T_ODS_PAR_CUSTMSG  T_ODS_PAR_OCSACTIVEMSG
 
 
rebind後預存程序中此update語句執行計畫：
db2expln -d newdssdb -g -c pdw -p P8414315 -s 0 -t>2.explain_rebind

-------------------- SECTION ---------------------------------------
Section = 8

Statement:
 
  UPDATE PODS.T_ODS_PAR_CUSTMSG A
         SET RUN_CODE='UU'
        WHERE
          SM_CODE in ('o3' , 'os' , 'om' , 'ol' , 'ox' )AND NOT
          EXISTS
       
     (SELECT *
     FROM PODS.T_ODS_PAR_OCSACTIVEMSG B
     where A.ID_NO=B.ID_NO )

Section Code Page = 1386
Estimated Cost = 1512832.000000
Estimated Cardinality = 183703.968750
Coordinator Subsection - Main Processing:
   Distribute Subsection #1
   |  Broadcast to Node List
   |  |  Nodes = 1, 2, 3, 4, 5, 6, 7
Subsection #1:
   Access Table Name = PODS.T_ODS_PAR_OCSACTIVEMSG  ID = 11,3174
   |  #Columns = 1
   |  Skip Inserted Rows
   |  Skip Deleted Rows
   |  Relation Scan
   |  |  Prefetch: Eligible
   |  Lock Intents
   |  |  Table: Intent Share
   |  |  Row  : Next Key Share
   |  Sargable Predicate(s)
   |  |  Process Build Table for Hash Join
   Anti Left Outer Hash Join
   |  Early Out: Single Match Per Outer Row
   |  Estimated Build Size: 7584000
   |  Estimated Probe Size: 6960000
   |  Access Table Name = PODS.T_ODS_PAR_CUSTMSG  ID = 11,155
   |  |  #Columns = 3
   |  |  Skip Inserted Rows
   |  |  Skip Deleted Rows
   |  |  Evaluate Block/Data Predicates Before Locking Row
   |  |  Relation Scan
   |  |  |  Prefetch: Eligible
Isolation Level: Read Stability
   |  |  Lock Intents
   |  |  |  Table: Intent Exclusive
   |  |  |  Row  : Update
   |  |  Sargable Predicate(s)
   |  |  |  #Predicates = 1
   |  |  |  Process Probe Table for Hash Join
   Establish Row Position
   |  Access Table Name = PODS.T_ODS_PAR_CUSTMSG  ID = 11,155
   Update:  Table Name = PODS.T_ODS_PAR_CUSTMSG  ID = 11,155
End of section

Optimizer Plan:
                                                  UPDATE
                                                  (   2)
                                       /---------/      \
                                 FETCH                 Table:          
                                 (   3)                PODS            
                        /-------/      \               T_ODS_PAR_CUSTMSG
                  HSJOIN            Table:          
                  (   4)            PODS            
             /---/      \--\        T_ODS_PAR_CUSTMSG
       TBSCAN               TBSCAN
       (   5)               (   6)
         |                    |  
 Table:             Table:               
 PODS               PODS                 

 T_ODS_PAR_CUSTMSG  T_ODS_PAR_OCSACTIVEMSG