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 sectionOptimizer 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 = 8Statement:

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 sectionOptimizer 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 如何检查ASM磁盘空间使用情况关于AIX的LV的状态问题相关资讯 DB2

RHEL5 下安装 DB2 V9.7 数据库（今 09:39）
DB2常用脚本整理（01月19日）
Linux下DB2SQL1024N A database （01月12日）

DB2中REVERSE函数的实现（01月19日）
使用 IBM Data Studio 创建和管理（01月12日）
TOAD连接DB2报错SQL1460N解决（01月12日）

本文评论查看全部评论（0）