/***********************开始实现业务******************************/
/*******/
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 PKGNAME 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
参考:
REBIND 实用程序和 FLUSH PACKAGE CACHE 命令:
在执行数据库应用程序或任何 SQL 语句之前,必须先由 DB2 对它们进行预编译,并生成一个包。包是一种数据库对象,其中含有应用程序源文件中所使用的已编译 SQL 语句。DB2 使用该包来访问 SQL 语句中引用的数据。DB2 优化器如何为这些包选择数据存取方案呢?它依靠包创建时的数据库统计信息。
对于静态 SQL 语句,包在编译时创建,并且被绑定到数据库上。如果对统计信息进行了更新,以反映物理数据库特征,那么也应该更新现有的包。REBIND 实用程序允许您重新创建包,以便可以利用当前的数据库统计信息。命令十分简单:
REBIND PACKAGE package_name
不过,如果您要更改应用程序源代码,那么就得显式地删除现有的相关包,然后重新创建包。REBIND 实用程序不用于这一目的。这里,我们之所以就此对您加以提醒,是因为 DBA 经常误解了 REBIND 的用法。
对于动态 SQL 语句,它们是在运行时预编译的,而且被存储在包高速缓存中。如果更新了统计信息,那么您可能会刷新高速缓存,以便重新编译动态 SQL 语句,从而获取更新的统计信息。命令类似下面的样子:
FLUSH PACKAGE CACHE DYNAMIC
静态sql动态sql的比较可参考:
添加新评论17 条评论
2014-02-08 15:51
2013-12-26 14:29
2013-12-26 14:29
2013-12-18 13:27
2013-12-18 11:10
2013-12-10 09:27
2012-11-14 11:17
2012-09-19 23:45
2012-07-09 23:30
2012-05-04 09:07
2012-04-14 21:04
Call sysproc.rebind_routine_package('P', 'papp.p_debug','ANY')
2012-04-12 17:40
2012-04-12 17:36
2012-03-27 22:37
2012-03-20 08:33
2012-03-16 09:15
2012-03-14 15:58