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

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
| | eva luate 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 I