DB2中存储过程执行慢问题故障处理

时间:2017-09-15 15:00来源:网络整理 作者:Linux先生 举报 点击:
其实这个问题是以前同一个客户遇见的问题,当时一个工程师解决后记录的过程如下: 应用同事反映但是对应到执行存储过程,执行了2

本文地址:http://www.kooct.com/install/faq/20170915_how_linux_12967.html
文章摘要:,多元化经舞动创造,强记博闻寸草春晖日本妹。

其实这个问题是以前同一个客户遇见的问题,当时一个工程师解决后记录的过程如下:

应用同事反映但是对应到执行存储过程,执行了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

推荐内容
重庆机场大巴最晚几点 幸运农场追号 旧重庆时时彩官网开奖 幸运农场走势图数字 qq农场金币限额
重庆幸运农场预测软件 快乐十分20选8技巧 重庆幸运农场快乐十分走势图 三全复试4中2 幸运农场怎么买稳赚
幸运农场兑奖 幸运农场彩票直播 重庆幸运农场网上怎么投注 重庆幸运农场介绍 微信全民农场电脑版
幸运农场走势图 幸运农场黄金幸运三 重庆幸运农场技巧规律 重庆幸运农场后一 幸运农场微信群