DB2中存储过程执行慢问题故障处理
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
评论暂时关闭