Hello,

DB2 LUW v9.5 FP7 running Red Hat Enterprise Linux Server release 5.3

This rather simple merge query is behaving abnormally. We our
application executes it, our CPUs easily goes to 100%.

This is a consistent CPU consumption. VMSTAT / SAR indicates 100% CPU
usage throughout several minutes. It seems, somehow, that DB2 comes to
an infinite loop.

When we revoked UPDATE access on the base table, the CPU consumption
returned to normal baseline.

The table is very small (76938 rows) and I tried everything that
seemed reasonable: reorg, runstats. Nothing helped.

The application is not trying to execute the merge millions of times.
In a 1 minute window, my event monitor showed that the merge was
executed no more than 100 times. This is very low for a 8CPU
production server.

Here is the execution plan. Can anyone see anything wrong here?

Database Context:
----------------
Parallelism: None
CPU Speed: 2.401083e-07
Comm Speed: 0
Buffer Pool size: 1090194
Sort Heap size: 452
Database Heap size: 16384
Lock List size: 251595
Maximum Lock List: 98
Average Applications: 1
Locks Available: 15780038

Package Context:
---------------
SQL Type: Dynamic
Optimization Level: 5
Blocking: Block All Cursors
Isolation Level: Cursor Stability

---------------- STATEMENT 1 SECTION 203 ----------------
QUERYNO: 10
QUERYTAG: CLP
Statement Type: Merge
Updatable: Not Applicable
Deletable: Not Applicable
Query Degree: 1

Original Statement:
------------------
MERGE INTO ASSET.TBL_ASSET_NET_DETAIL AS E USING (VALUES (cast(? as
varchar(24)), cast(? as timestamp), cast(? as varchar(192)),
cast(?
as varchar(128)) , cast(? as varchar(75)) , cast(? as
character(1)),
cast(? as varchar(192)), cast(? as character(1)) )) AS INDATA
(
MACHINE_ID, MACHINE_LOCALTIME_INSERT, MACHINE_HOSTNAME,
MACHINE_IPDOMAIN , LOGIN_NAME , LAN_GROUP_TYPE,
LAN_GROUP_NAME,
FIRE_LOGIN_TRIGGERS) ON (E.MACHINE_ID = INDATA.MACHINE_ID)
WHEN MATCHED AND ((E.MACHINE_HOSTNAME <> INDATA.MACHINE_HOSTNAME OR
E.MACHINE_IPDOMAIN <> INDATA.MACHINE_IPDOMAIN OR E.LOGIN_NAME
<>
INDATA.LOGIN_NAME OR E.LAN_GROUP_TYPE <> INDATA.LAN_GROUP_TYPE
OR
E.LAN_GROUP_NAME <> INDATA.LAN_GROUP_NAME OR
E.FIRE_LOGIN_TRIGGERS <>
INDATA.FIRE_LOGIN_TRIGGERS))
THEN
UPDATE SET MACHINE_LOCALTIME_INSERT =
INDATA.MACHINE_LOCALTIME_INSERT,
MACHINE_HOSTNAME = INDATA.MACHINE_HOSTNAME, MACHINE_IPDOMAIN
=
INDATA.MACHINE_IPDOMAIN , LOGIN_NAME = INDATA.LOGIN_NAME ,
LAN_GROUP_TYPE = INDATA.LAN_GROUP_TYPE, LAN_GROUP_NAME =
INDATA.LAN_GROUP_NAME, FIRE_LOGIN_TRIGGERS =
INDATA.FIRE_LOGIN_TRIGGERS
WHEN NOT MATCHED
THEN
INSERT (MACHINE_ID, MACHINE_LOCALTIME_INSERT, MACHINE_HOSTNAME,
MACHINE_IPDOMAIN , LOGIN_NAME , LAN_GROUP_TYPE,
LAN_GROUP_NAME,
FIRE_LOGIN_TRIGGERS) VALUES (INDATA.MACHINE_ID,
INDATA.MACHINE_LOCALTIME_INSERT, INDATA.MACHINE_HOSTNAME,
INDATA.MACHINE_IPDOMAIN , INDATA.LOGIN_NAME ,
INDATA.LAN_GROUP_TYPE,
INDATA.LAN_GROUP_NAME, INDATA.FIRE_LOGIN_TRIGGERS)


Optimized Statement:
-------------------
INSERT INTO ASSET.TBL_ASSET_NET_DETAIL AS Q15
UPDATE ASSET.TBL_ASSET_NET_DETAIL AS Q1 SET (Q1.FIRE_LOGIN_TRIGGERS,
Q1.LAN_GROUP_NAME, Q1.LAN_GROUP_TYPE, Q1.LOGIN_NAME,
Q1.MACHINE_IPDOMAIN, Q1.MACHINE_HOSTNAME,
Q1.MACHINE_LOCALTIME_INSERT) =
SELECT Q12.$C2, :?, Q5.$C10, :?, Q5.$C9, Q5.$C8, Q5.$C7, :?,
CASE
WHEN (Q5.$C11 IS NOT NULL AND ((((((Q5.$C0 <> Q5.$C7) OR (Q5.$C1
<>
Q5.$C8)) OR (Q5.$C2 <> Q5.$C9)) OR (Q5.$C3 <> :?)) OR
(Q5.$C4 <>
Q5.$C10)) OR (Q5.$C5 <> :?)))
THEN 1
WHEN Q5.$C11 IS NULL
THEN 2
ELSE 0 END
FROM
(SELECT Q4.MACHINE_HOSTNAME, Q4.MACHINE_IPDOMAIN,
Q4.LOGIN_NAME,
Q4.LAN_GROUP_TYPE, Q4.LAN_GROUP_NAME,
Q4.FIRE_LOGIN_TRIGGERS,
Q3.$C0, Q3.$C2, Q3.$C3, Q3.$C4, Q3.$C6
FROM
(SELECT :?, :?, :?, :?, :?, :?, :?, :?
FROM (VALUES 1) AS Q2) AS Q3 LEFT OUTER JOIN
ASSET.TBL_ASSET_NET_DETAIL AS Q4 ON (Q4.MACHINE_ID
= :?)) AS Q5,
(SELECT NULL, NULL, Q5.$C6
FROM
(SELECT 1
FROM (VALUES 1) AS Q6) AS Q7
WHERE (CASE
WHEN (Q5.$C11 IS NOT NULL AND ((((((Q5.$C0 <> Q5.$C7) OR (Q5.$C1
<>
Q5.$C8)) OR (Q5.$C2 <> Q5.$C9)) OR (Q5.$C3 <> :?)) OR
(Q5.$C4
<> Q5.$C10)) OR (Q5.$C5 <> :?)))
THEN 1
WHEN Q5.$C11 IS NULL
THEN 2
ELSE 0 END = 2)
UNION ALL
SELECT NULL
FROM
(SELECT 1
FROM (VALUES 1) AS Q9) AS Q10
WHERE (CASE
WHEN (Q5.$C11 IS NOT NULL AND ((((((Q5.$C0 <> Q5.$C7) OR (Q5.$C1
<>
Q5.$C8)) OR (Q5.$C2 <> Q5.$C9)) OR (Q5.$C3 <> :?)) OR
(Q5.$C4
<> Q5.$C10)) OR (Q5.$C5 <> :?)))
THEN 1
WHEN Q5.$C11 IS NULL
THEN 2
ELSE 0 END = 1)) AS Q12

Access Plan:
-----------
Total Cost: 102.028
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
INSERT
( 2)
102.028
4.08
/-----+-----\
0.08 76938
UPDATE TABLE:
ASSET
( 3)
TBL_ASSET_NET_DETAIL
77.0251 Q15
3.08
/-----+-----\
0.08 76938
NLJOIN TABLE: ASSET
( 4) TBL_ASSET_NET_DETAIL
75.0248 Q1
3
/-------------------+--------------------\
1 0.08
75.021 0.00365819
3 0
/-------+--------\ /------+------
\
1 1 0.04
0.04
TBSCAN FETCH FILTER
FILTER
( 6) ( 7) ( 10)
( 12)
2.8813e-05 75.021 0.00171797
0.00171797
0 3
0 0
1 1
TABFNC: SYSIBM IXSCAN TABLE: ASSET TBSCAN
TBSCAN
GENROW ( 8) TBL_ASSET_NET_DETAIL ( 11)
( 13)
50.0173 Q4 2.8813e-05
2.8813e-05
2
0 0
1 1
INDEX: SYSIBM TABFNC: SYSIBM
TABFNC: SYSIBM
SQL110214080842620 GENROW
GENROW
Q4



Extended Diagnostic Information:
--------------------------------

Diagnostic Identifier: 1
Diagnostic Details: EXP0080W The current usage of the statement or
the
statement containing update, delete or insert or
constructs like sampling limits MQT matching.

Plan Details:
-------------


1) RETURN: (Return Result)
Cumulative Total Cost: 102.028
Cumulative CPU Cost: 117592
Cumulative I/O Cost: 4.08
Cumulative Re-Total Cost: 27.0102
Cumulative Re-CPU Cost: 42558
Cumulative Re-I/O Cost: 1.08
Cumulative First Row Cost: 102.028
Estimated Bufferpool Buffers: 4.08

Arguments:
---------
BLDLEVEL: (Build level)
DB2 v9.5.0.7 : s101129
HEAPUSE : (Maximum Statement Heap Usage)
192 Pages
PREPTIME: (Statement prepare time)
16 milliseconds
STMTHEAP: (Statement heap size)
16384

Input Streams:
-------------
19) From Operator #2

Estimated number of rows: 0.08
Number of columns: 0
Subquery predicate ID: Not Applicable


2) INSERT: (Insert)
Cumulative Total Cost: 102.028
Cumulative CPU Cost: 117592
Cumulative I/O Cost: 4.08
Cumulative Re-Total Cost: 27.0102
Cumulative Re-CPU Cost: 42558
Cumulative Re-I/O Cost: 1.08
Cumulative First Row Cost: 102.028
Estimated Bufferpool Buffers: 4.08

Input Streams:
-------------
17) From Operator #3

Estimated number of rows: 0.08
Number of columns: 9
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q14.$C8+Q14.$C0+Q14.LAN_GROUP_NAME+Q14.$C2
+Q14.LOGIN_NAME+Q14.MACHINE_IPDOMAIN
+Q14.MACHINE_HOSTNAME+Q14.$C6+Q14.$C7


Output Streams:
--------------
18) To Object ASSET.TBL_ASSET_NET_DETAIL

Estimated number of rows: 76938
Number of columns: 8
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q15.FIRE_LOGIN_TRIGGERS+Q15.LAN_GROUP_NAME
+Q15.LAN_GROUP_TYPE+Q15.LOGIN_NAME
+Q15.MACHINE_IPDOMAIN+Q15.MACHINE_HOSTNAME
+Q15.MACHINE_LOCALTIME_INSERT+Q15.MACHINE_ID

19) To Operator #1

Estimated number of rows: 0.08
Number of columns: 0
Subquery predicate ID: Not Applicable


3) UPDATE: (Update)
Cumulative Total Cost: 77.0251
Cumulative CPU Cost: 104592
Cumulative I/O Cost: 3.08
Cumulative Re-Total Cost: 2.0071
Cumulative Re-CPU Cost: 29558
Cumulative Re-I/O Cost: 0.08
Cumulative First Row Cost: 77.0251
Estimated Bufferpool Buffers: 3.08

Input Streams:
-------------
15) From Operator #4

Estimated number of rows: 0.08
Number of columns: 11
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q13.$C10+Q13.$C0+Q13.$C1+Q13.$C2+Q13.$C3
+Q13.LAN_GROUP_NAME+Q13.$C5+Q13.LOGIN_NAME
+Q13.MACHINE_IPDOMAIN+Q13.MACHINE_HOSTNAME
+Q13.$C9


Output Streams:
--------------
16) To Object ASSET.TBL_ASSET_NET_DETAIL

Estimated number of rows: 76938
Number of columns: 8
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q1.$RID$+Q1.FIRE_LOGIN_TRIGGERS
+Q1.LAN_GROUP_NAME+Q1.LAN_GROUP_TYPE
+Q1.LOGIN_NAME+Q1.MACHINE_IPDOMAIN
+Q1.MACHINE_HOSTNAME
+Q1.MACHINE_LOCALTIME_INSERT

17) To Operator #2

Estimated number of rows: 0.08
Number of columns: 9
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q14.$C8+Q14.$C0+Q14.LAN_GROUP_NAME+Q14.$C2
+Q14.LOGIN_NAME+Q14.MACHINE_IPDOMAIN
+Q14.MACHINE_HOSTNAME+Q14.$C6+Q14.$C7


4) NLJOIN: (Nested Loop Join)
Cumulative Total Cost: 75.0248
Cumulative CPU Cost: 103072
Cumulative I/O Cost: 3
Cumulative Re-Total Cost: 0.00673202
Cumulative Re-CPU Cost: 28037.4
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 75.0248
Estimated Bufferpool Buffers: 3

Arguments:
---------
EARLYOUT: (Early Out flag)
NONE
FETCHMAX: (Override for FETCH MAXPAGES)
IGNORE
ISCANMAX: (Override for ISCAN MAXPAGES)
IGNORE

Input Streams:
-------------
7) From Operator #5

Estimated number of rows: 1
Number of columns: 12
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q5.LAN_GROUP_NAME+Q5.LOGIN_NAME
+Q5.MACHINE_IPDOMAIN+Q5.MACHINE_HOSTNAME
+Q5.MACHINE_ID+Q5.FIRE_LOGIN_TRIGGERS
+Q5.LAN_GROUP_NAME+Q5.LAN_GROUP_TYPE
+Q5.LOGIN_NAME+Q5.MACHINE_IPDOMAIN
+Q5.MACHINE_HOSTNAME+Q5.$C11

14) From Operator #9

Estimated number of rows: 0.08
Number of columns: 3
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q12.$C1+Q12.$C0+Q12.$C2


Output Streams:
--------------
15) To Operator #3

Estimated number of rows: 0.08
Number of columns: 11
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q13.$C10+Q13.$C0+Q13.$C1+Q13.$C2+Q13.$C3
+Q13.LAN_GROUP_NAME+Q13.$C5+Q13.LOGIN_NAME
+Q13.MACHINE_IPDOMAIN+Q13.MACHINE_HOSTNAME
+Q13.$C9


5) NLJOIN: (Nested Loop Join)
Cumulative Total Cost: 75.021
Cumulative CPU Cost: 87355.6
Cumulative I/O Cost: 3
Cumulative Re-Total Cost: 0.00295843
Cumulative Re-CPU Cost: 12321.2
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 75.0208
Estimated Bufferpool Buffers: 3

Arguments:
---------
EARLYOUT: (Early Out flag)
NONE
FETCHMAX: (Override for FETCH MAXPAGES)
IGNORE
ISCANMAX: (Override for ISCAN MAXPAGES)
IGNORE
JN INPUT: (Join input leg)
OUTER
OUTERJN : (Outer Join type)
LEFT

Input Streams:
-------------
2) From Operator #6

Estimated number of rows: 1
Number of columns: 5
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q3.$C6+Q3.$C4+Q3.$C3+Q3.$C2+Q3.$C0

6) From Operator #7

Estimated number of rows: 1
Number of columns: 7
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q4.$RID$+Q4.FIRE_LOGIN_TRIGGERS
+Q4.LAN_GROUP_NAME+Q4.LAN_GROUP_TYPE
+Q4.LOGIN_NAME+Q4.MACHINE_IPDOMAIN
+Q4.MACHINE_HOSTNAME


Output Streams:
--------------
7) To Operator #4

Estimated number of rows: 1
Number of columns: 12
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q5.LAN_GROUP_NAME+Q5.LOGIN_NAME
+Q5.MACHINE_IPDOMAIN+Q5.MACHINE_HOSTNAME
+Q5.MACHINE_ID+Q5.FIRE_LOGIN_TRIGGERS
+Q5.LAN_GROUP_NAME+Q5.LAN_GROUP_TYPE
+Q5.LOGIN_NAME+Q5.MACHINE_IPDOMAIN
+Q5.MACHINE_HOSTNAME+Q5.$C11


6) TBSCAN: (Table Scan)
Cumulative Total Cost: 2.8813e-05
Cumulative CPU Cost: 120
Cumulative I/O Cost: 0
Cumulative Re-Total Cost: 2.8813e-05
Cumulative Re-CPU Cost: 120
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 2.16097e-05
Estimated Bufferpool Buffers: 0

Arguments:
---------
JN INPUT: (Join input leg)
OUTER
MAXPAGES: (Maximum pages for prefetch)
ALL
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
NEXT KEY SHARE
SCANDIR : (Scan Direction)
FORWARD
SKIP_DEL: (Skip Deleted Rows)
TRUE
SKIP_INS: (Skip Inserted Rows)
TRUE
TABLOCK : (Table Lock intent)
INTENT SHARE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY

Input Streams:
-------------
1) From Object SYSIBM.GENROW

Estimated number of rows: 1
Number of columns: 0
Subquery predicate ID: Not Applicable


Output Streams:
--------------
2) To Operator #5

Estimated number of rows: 1
Number of columns: 5
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q3.$C6+Q3.$C4+Q3.$C3+Q3.$C2+Q3.$C0


7) FETCH : (Fetch)
Cumulative Total Cost: 75.021
Cumulative CPU Cost: 87235.6
Cumulative I/O Cost: 3
Cumulative Re-Total Cost: 0.00292962
Cumulative Re-CPU Cost: 12201.2
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 75.0207
Estimated Bufferpool Buffers: 4

Arguments:
---------
EVALUNCO: (Evaluate Uncommitted Rows)
TRUE
JN INPUT: (Join input leg)
INNER
MAXPAGES: (Maximum pages for prefetch)
1
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
EXCLUSIVE
SKIP_DEL: (Skip Deleted Rows)
TRUE
SKIP_INS: (Skip Inserted Rows)
TRUE
TABLOCK : (Table Lock intent)
INTENT EXCLUSIVE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY

Input Streams:
-------------
4) From Operator #8

Estimated number of rows: 1
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q4.MACHINE_ID(A)+Q4.$RID$

5) From Object ASSET.TBL_ASSET_NET_DETAIL

Estimated number of rows: 76938
Number of columns: 7
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q4.FIRE_LOGIN_TRIGGERS+Q4.LAN_GROUP_NAME
+Q4.LAN_GROUP_TYPE+Q4.LOGIN_NAME
+Q4.MACHINE_IPDOMAIN+Q4.MACHINE_HOSTNAME
+Q4.MACHINE_LOCALTIME_INSERT


Output Streams:
--------------
6) To Operator #5

Estimated number of rows: 1
Number of columns: 7
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q4.$RID$+Q4.FIRE_LOGIN_TRIGGERS
+Q4.LAN_GROUP_NAME+Q4.LAN_GROUP_TYPE
+Q4.LOGIN_NAME+Q4.MACHINE_IPDOMAIN
+Q4.MACHINE_HOSTNAME


8) IXSCAN: (Index Scan)
Cumulative Total Cost: 50.0173
Cumulative CPU Cost: 71842.2
Cumulative I/O Cost: 2
Cumulative Re-Total Cost: 0.00249262
Cumulative Re-CPU Cost: 10381.2
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 50.0173
Estimated Bufferpool Buffers: 3

Arguments:
---------
MAXPAGES: (Maximum pages for prefetch)
1
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
EXCLUSIVE
SCANDIR : (Scan Direction)
FORWARD
SKIP_DEL: (Skip Deleted Rows)
TRUE
SKIP_INS: (Skip Inserted Rows)
TRUE
TABLOCK : (Table Lock intent)
INTENT EXCLUSIVE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY

Predicates:
----------
4) Start Key Predicate
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 1.29975e-05

Predicate Text:
--------------
(Q4.MACHINE_ID = :?)

4) Stop Key Predicate
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 1.29975e-05

Predicate Text:
--------------
(Q4.MACHINE_ID = :?)


Input Streams:
-------------
3) From Object SYSIBM.SQL110214080842620

Estimated number of rows: 76938
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q4.MACHINE_ID(A)+Q4.$RID$


Output Streams:
--------------
4) To Operator #7

Estimated number of rows: 1
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q4.MACHINE_ID(A)+Q4.$RID$


9) UNION : (Union)
Cumulative Total Cost: 0.00365819
Cumulative CPU Cost: 15235.6
Cumulative I/O Cost: 0
Cumulative Re-Total Cost: 0.00365819
Cumulative Re-CPU Cost: 15235.6
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 0.00357665
Estimated Bufferpool Buffers: 0

Arguments:
---------
JN INPUT: (Join input leg)
INNER

Input Streams:
-------------
10) From Operator #10

Estimated number of rows: 0.04
Number of columns: 3
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q8.$C0+Q8.$C1+Q8.MACHINE_ID

13) From Operator #12

Estimated number of rows: 0.04
Number of columns: 3
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q11.$C2+Q11.$C1+Q11.$C0


Output Streams:
--------------
14) To Operator #4

Estimated number of rows: 0.08
Number of columns: 3
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q12.$C1+Q12.$C0+Q12.$C2


10) FILTER: (Filter)
Cumulative Total Cost: 0.00171797
Cumulative CPU Cost: 7155
Cumulative I/O Cost: 0
Cumulative Re-Total Cost: 0.00171797
Cumulative Re-CPU Cost: 7155
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 0.00171797
Estimated Bufferpool Buffers: 0

Predicates:
----------
6) Residual Predicate
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.04

Predicate Text:
--------------
(
CASE
WHEN (Q5.$C11 IS NOT NULL AND ((((((Q5.$C0 <> Q5.$C7)
OR (Q5.$C1 <> Q5.$C8)) OR (Q5.$C2 <> Q5.$C9))
OR (Q5.$C3 <> :?)) OR (Q5.$C4 <> Q5.$C10)) OR
(Q5.$C5 <> :?)))
THEN 1
WHEN Q5.$C11 IS NULL
THEN 2
ELSE 0 END = 2)


Input Streams:
-------------
9) From Operator #11

Estimated number of rows: 1
Number of columns: 0
Subquery predicate ID: Not Applicable


Output Streams:
--------------
10) To Operator #9

Estimated number of rows: 0.04
Number of columns: 3
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q8.$C0+Q8.$C1+Q8.MACHINE_ID


11) TBSCAN: (Table Scan)
Cumulative Total Cost: 2.8813e-05
Cumulative CPU Cost: 120
Cumulative I/O Cost: 0
Cumulative Re-Total Cost: 2.8813e-05
Cumulative Re-CPU Cost: 120
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 2.16097e-05
Estimated Bufferpool Buffers: 0

Arguments:
---------
MAXPAGES: (Maximum pages for prefetch)
ALL
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
NEXT KEY SHARE
SCANDIR : (Scan Direction)
FORWARD
SKIP_DEL: (Skip Deleted Rows)
TRUE
SKIP_INS: (Skip Inserted Rows)
TRUE
TABLOCK : (Table Lock intent)
INTENT SHARE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY

Input Streams:
-------------
8) From Object SYSIBM.GENROW

Estimated number of rows: 1
Number of columns: 0
Subquery predicate ID: Not Applicable


Output Streams:
--------------
9) To Operator #10

Estimated number of rows: 1
Number of columns: 0
Subquery predicate ID: Not Applicable


12) FILTER: (Filter)
Cumulative Total Cost: 0.00171797
Cumulative CPU Cost: 7155
Cumulative I/O Cost: 0
Cumulative Re-Total Cost: 0.00171797
Cumulative Re-CPU Cost: 7155
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 0.00171797
Estimated Bufferpool Buffers: 0

Predicates:
----------
8) Residual Predicate
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.04

Predicate Text:
--------------
(
CASE
WHEN (Q5.$C11 IS NOT NULL AND ((((((Q5.$C0 <> Q5.$C7)
OR (Q5.$C1 <> Q5.$C8)) OR (Q5.$C2 <> Q5.$C9))
OR (Q5.$C3 <> :?)) OR (Q5.$C4 <> Q5.$C10)) OR
(Q5.$C5 <> :?)))
THEN 1
WHEN Q5.$C11 IS NULL
THEN 2
ELSE 0 END = 1)


Input Streams:
-------------
12) From Operator #13

Estimated number of rows: 1
Number of columns: 0
Subquery predicate ID: Not Applicable


Output Streams:
--------------
13) To Operator #9

Estimated number of rows: 0.04
Number of columns: 3
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q11.$C2+Q11.$C1+Q11.$C0


13) TBSCAN: (Table Scan)
Cumulative Total Cost: 2.8813e-05
Cumulative CPU Cost: 120
Cumulative I/O Cost: 0
Cumulative Re-Total Cost: 2.8813e-05
Cumulative Re-CPU Cost: 120
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 2.16097e-05
Estimated Bufferpool Buffers: 0

Arguments:
---------
MAXPAGES: (Maximum pages for prefetch)
ALL
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
NEXT KEY SHARE
SCANDIR : (Scan Direction)
FORWARD
SKIP_DEL: (Skip Deleted Rows)
TRUE
SKIP_INS: (Skip Inserted Rows)
TRUE
TABLOCK : (Table Lock intent)
INTENT SHARE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY

Input Streams:
-------------
11) From Object SYSIBM.GENROW

Estimated number of rows: 1
Number of columns: 0
Subquery predicate ID: Not Applicable


Output Streams:
--------------
12) To Operator #12

Estimated number of rows: 1
Number of columns: 0
Subquery predicate ID: Not Applicable


Objects Used in Access Plan:
---------------------------

Schema: SYSIBM
Name: SQL110214080842620
Type: Index
Time of creation: 2011-02-14-08.08.42.459461
Last statistics update: 2011-02-14-08.22.55.108845
Number of columns: 1
Number of rows: 76938
Width of rows: -1
Number of buffer pool pages: 522
Distinct row values: Yes
Tablespace name: IDX
Tablespace overhead: 24.100000
Tablespace transfer rate: 0.900000
Source for statistics: Single Node
Prefetch page count: 128
Container extent page count: 32
Index clustering statistic: 15.000000
Index leaf pages: 438
Index tree levels: 3
Index full key cardinality: 76938
Index first key cardinality: 76938
Index first 2 keys cardinality: -1
Index first 3 keys cardinality: -1
Index first 4 keys cardinality: -1
Index sequential pages: 437
Index page density: 99
Index avg sequential pages: 437
Index avg gap between sequences:0
Index avg random pages: 0
Fetch avg sequential pages: -1
Fetch avg gap between sequences:-1
Fetch avg random pages: -1
Index RID count: 76938
Index deleted RID count: 0
Index empty leaf pages: 0
Base Table Schema: ASSET
Base Table Name: TBL_ASSET_NET_DETAIL
Columns in index:
MACHINE_ID(A)

Schema: ASSET
Name: TBL_ASSET_NET_DETAIL
Type: Table
Time of creation: 2011-02-14-08.08.40.639535
Last statistics update: 2011-02-14-08.22.55.108845
Number of columns: 8
Number of rows: 76938
Width of rows: 102
Number of buffer pool pages: 522
Number of data partitions: 1
Distinct row values: No
Tablespace name: DAT
Tablespace overhead: 24.100000
Tablespace transfer rate: 0.900000
Source for statistics: Single Node
Prefetch page count: 128
Container extent page count: 32
Table overflow record count: 0
Table Active Blocks: -1
Average Row Compression Ratio: 1.95655
Percentage Rows Compressed: 100
Average Compressed Row Size: 53

Schema: SYSIBM
Name: GENROW
Type: Table Function
Time of creation:
Last statistics update:
Number of columns: 1
Number of rows: 1
Width of rows: 11
Number of buffer pool pages: -1
Distinct row values: No
Source for statistics: Single Node

On 2011-02-24 18:50, Michel Esber wrote:
What is the output of:

select NUM_EXECUTIONS, ROWS_READ, POOL_DATA_L_READS, POOL_DATA_P_READS,
POOL_INDEX_L_READS, POOL_INDEX_P_READS, TOTAL_EXEC_TIME,
TOTAL_USR_CPU_TIME
from sysibmadm.snapdyn_sql
where cast(stmt_text as varchar(2000)) like 'MERGE INTO
ASSET.TBL_ASSET_NET_DETAIL AS E USING (VALUES (%';

?

Just a thought,

WHEN MATCHED
AND ((E.MACHINE_HOSTNAME <> INDATA.MACHINE_HOSTNAME OR
E.MACHINE_IPDOMAIN <> INDATA.MACHINE_IPDOMAIN OR
E.LOGIN_NAME <> INDATA.LOGIN_NAME OR
E.LAN_GROUP_TYPE <> INDATA.LAN_GROUP_TYPE OR
E.LAN_GROUP_NAME <> INDATA.LAN_GROUP_NAME OR
E.FIRE_LOGIN_TRIGGERS <> INDATA.FIRE_LOGIN_TRIGGERS))
THEN

can be rewritten as:

WHEN MATCHED
AND NOT ((E.MACHINE_HOSTNAME = INDATA.MACHINE_HOSTNAME AND
E.MACHINE_IPDOMAIN = INDATA.MACHINE_IPDOMAIN AND
E.LOGIN_NAME = INDATA.LOGIN_NAME AND
E.LAN_GROUP_TYPE = INDATA.LAN_GROUP_TYPE AND
E.LAN_GROUP_NAME = INDATA.LAN_GROUP_NAME AND
E.FIRE_LOGIN_TRIGGERS = INDATA.FIRE_LOGIN_TRIGGERS))
THEN

Not sure if it will help though


/Lennart

Hi Lennart,

Thanks for the quick reply

privilege is revoked:

[db2inst1@host ~]$ sar 1 10
Linux 2.6.18-128.el5 02/24/2011 _x86_64_ (8 CPU)

04:39:04 PM CPU %user %nice %system %iowait
%steal %idle
04:39:05 PM all 57.80 0.12 4.87 15.61
0.00 21.60
04:39:06 PM all 55.75 0.00 5.12 16.00
0.00 23.12
04:39:07 PM all 45.62 0.00 14.62 19.62
0.00 20.12
04:39:08 PM all 4.50 0.00 0.75 33.62
0.00 61.12
04:39:09 PM all 0.37 0.00 0.25 37.28
0.00 62.09
04:39:10 PM all 0.37 0.12 0.37 56.43
0.00 42.70
04:39:11 PM all 5.50 0.00 0.12 39.88
0.00 54.50
04:39:12 PM all 12.61 0.00 0.12 30.71
0.00 56.55
04:39:13 PM all 24.97 0.00 8.49 31.09
0.00 35.46
04:39:14 PM all 63.80 0.00 6.62 19.60
0.00 9.99
Average: all 27.13 0.02 4.13 29.99
0.00 38.73


[db2inst1@host ~]$ db2 "grant update on ASSET.TBL_ASSET_NET_DETAIL to
user dbback"
DB20000I The SQL command completed successfully.

[db2inst1@host ~]$ sar 1 10
Linux 2.6.18-128.el5 02/24/2011 _x86_64_ (8 CPU)

04:39:33 PM CPU %user %nice %system %iowait
%steal %idle
04:39:34 PM all 90.40 0.00 1.94 4.01
0.00 3.65
04:39:35 PM all 99.67 0.00 0.33 0.00
0.00 0.00
04:39:36 PM all 98.38 0.00 1.62 0.00
0.00 0.00
04:39:37 PM all 99.67 0.00 0.22 0.00
0.00 0.11
04:39:38 PM all 99.19 0.00 0.81 0.00
0.00 0.00
04:39:40 PM all 98.96 0.10 0.94 0.00
0.00 0.00
04:39:41 PM all 99.25 0.00 0.75 0.00
0.00 0.00
04:39:42 PM all 96.38 0.00 1.62 1.62
0.00 0.37
04:39:43 PM all 95.88 0.00 2.62 1.50
0.00 0.00
04:39:44 PM all 95.14 0.12 2.12 0.75
0.00 1.87
Average: all 97.45 0.02 1.25 0.73
0.00 0.56


Here's what you have asked:

NUM_EXECUTIONS ROWS_READ POOL_DATA_L_READS
POOL_DATA_P_READS POOL_INDEX_L_READS POOL_INDEX_P_READS
TOTAL_EXEC_TIME TOTAL_USR_CPU_TIME
-------------------- -------------------- --------------------
-------------------- -------------------- --------------------
-------------------- --------------------
61 20792
20805 35 124409
19 94 3

This snapshot was taken based on a workload of a few seconds. This was
more than enough time to see my CPU consumption raise to nearly
100% ...

Thanks,

How about this?

WHEN MATCHED
THEN
UPDATE
SET MACHINE_LOCALTIME_INSERT
= CASE
WHEN E.MACHINE_HOSTNAME <> INDATA.MACHINE_HOSTNAME
OR E.MACHINE_IPDOMAIN <> INDATA.MACHINE_IPDOMAIN
OR E.LOGIN_NAME <> INDATA.LOGIN_NAME
OR E.LAN_GROUP_TYPE <> INDATA.LAN_GROUP_TYPE
OR E.LAN_GROUP_NAME <> INDATA.LAN_GROUP_NAME
OR E.FIRE_LOGIN_TRIGGERS <> INDATA.FIRE_LOGIN_TRIGGERS
THEN INDATA.MACHINE_LOCALTIME_INSERT
ELSE E. MACHINE_LOCALTIME_INSERT
END
, MACHINE_HOSTNAME = INDATA.MACHINE_HOSTNAME
, MACHINE_IPDOMAIN = INDATA.MACHINE_IPDOMAIN
, LOGIN_NAME = INDATA.LOGIN_NAME
, LAN_GROUP_TYPE = INDATA.LAN_GROUP_TYPE
, LAN_GROUP_NAME = INDATA.LAN_GROUP_NAME
, FIRE_LOGIN_TRIGGERS = INDATA.FIRE_LOGIN_TRIGGERS

On 2011-02-24 20:46, Michel Esber wrote:
[...]
Roughly 2000 logical index reads per execution, does not look that
extreme. What is the definition of the index used on
ASSET.TBL_ASSET_NET_DETAIL (SYSIBM.SQL110214080842620 ?) and what is
FIRSTKEYCARD, etc for that index?

/Lennart

Hi Tonkuma,

I need to avoid needless updates in my DB ...

I believe that in your scenario an UPDATE is always performed (since
you have a CASE WHEN ... ELSE ... END). SO there is always a new value
to be updated ( regardless if it ever changed or not).

However, the main problem is still with high CPU consumption.

As a final notice, with tried to replace the MERGE with simple
UPDATES. We also go the same behavior ... CPU ~ 99% all the time ...

Any ideas?

Thanks

Hi Lennart,

The index definition is (+MACHINE_ID) only, reverse_scans = 'Y'

Here are some other fields:

PCTFREE NLEAF NLEVELS FIRSTKEYCARD
FIRST2KEYCARD FIRST3KEYCARD FIRST4KEYCARD
FULLKEYCARD
------- -------------------- ------- --------------------
-------------------- -------------------- --------------------
--------------------
-1 432 3
75912 -1 -1
-1 75912

1 record(s) selected.


Thanks again.

on inefficient access path in your MERGE statement.

Was the access path still same by changing the MATCHED clause?
If access path was chnged,
you can compare waste of CPU by unnecessary update and CPU
consumption in your original MERGE statement.

By the way,
on DB2/MVS(old name of DB2 for z/OS),
if updated values were same as stored row values, DB2 avoid physical
update and logging.
(I don't know DB2 for LUW works similar way or not.)

Hi Tonkuma,

When we first detected the problem, the application originally had
simple UPDATE statements.

We then understood that forced update could be somehow a processing
overhead. We migrated it to MERGE statements, but the CPU consumption
was still very high.

I will change the merge to check whether the access plans changed.

Thanks,

I have had really bad luck with MERGE in the past - sometimes it is
beautiful, sometimes it takes forever, and I haven't personally been
able to track down why. In some cases, you have to live with it - it
is better than most of the alternatives.

If you are willing to try something a little more radical, you can
avoid the MERGE statement altogether, avoid extra updates, and you
should still be able to make it pretty fast. The below statement I
think will do what you are looking for without the MERGE statement:

with INDATA(MACHINE_ID, MACHINE_LOCALTIME_INSERT, MACHINE_HOSTNAME,
MACHINE_IPDOMAIN , LOGIN_NAME , LAN_GROUP_TYPE,
LAN_GROUP_NAME, FIRE_LOGIN_TRIGGERS)
AS (VALUES (cast(? as varchar(24)), cast(? as timestamp),
cast(? as varchar(192)), cast(? as varchar(128)) ,
cast(? as varchar(75)) , cast(? as character(1)),
cast(? as varchar(192)), cast(? as character(1)) ),
EXIST(YN) AS (select case when count(*) > 0 then 'Y' else 'N' end
FROM ASSET.TBL_ASSET_NET_DETAIL
WHERE MACHINE_ID = (SELECT MACHINE_ID FROM INDATA)),
INS(CNT) AS (SELECT FINAL TABLE(INSERT INTO ASSET.TBL_ASSET_NET_DETAIL
(MACHINE_ID, MACHINE_LOCALTIME_INSERT, MACHINE_HOSTNAME,
MACHINE_IPDOMAIN , LOGIN_NAME , LAN_GROUP_TYPE,
LAN_GROUP_NAME, FIRE_LOGIN_TRIGGERS)
SELECT MACHINE_ID, MACHINE_LOCALTIME_INSERT,
MACHINE_HOSTNAME,
MACHINE_IPDOMAIN , LOGIN_NAME , LAN_GROUP_TYPE,
LAN_GROUP_NAME, FIRE_LOGIN_TRIGGERS FROM INDATA
WHERE 'N' = (SELECT YN FROM EXIST)) t),
UPD(CNT) AS (SELECT COUNT(*) FROM FINAL TABLE(UPDATE
ASSET.TBL_ASSET_NET_DETAIL
SET (MACHINE_ID, MACHINE_LOCALTIME_INSERT, MACHINE_HOSTNAME,
MACHINE_IPDOMAIN , LOGIN_NAME , LAN_GROUP_TYPE,
LAN_GROUP_NAME, FIRE_LOGIN_TRIGGERS) =
(SELECT MACHINE_ID, MACHINE_LOCALTIME_INSERT,
MACHINE_HOSTNAME,
MACHINE_IPDOMAIN , LOGIN_NAME , LAN_GROUP_TYPE,
LAN_GROUP_NAME, FIRE_LOGIN_TRIGGERS FROM INDATA)
WHERE 'Y' = (SELECT YN FROM EXIST)
AND (MACHINE_ID, MACHINE_LOCALTIME_INSERT,
MACHINE_HOSTNAME,
MACHINE_IPDOMAIN , LOGIN_NAME , LAN_GROUP_TYPE,
LAN_GROUP_NAME, FIRE_LOGIN_TRIGGERS) NOT IN
(SELECT MACHINE_ID, MACHINE_LOCALTIME_INSERT,
MACHINE_HOSTNAME,
MACHINE_IPDOMAIN , LOGIN_NAME , LAN_GROUP_TYPE,
LAN_GROUP_NAME, FIRE_LOGIN_TRIGGERS FROM INDATA)
) t)
select sum(cnt) from (select cnt from upd union all select cnt from
ins) u

This was tested (on a different table) against a 9.5.7 DB2 database
and works - it should work for any 9+ database as well.

-Chris