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
|
0.08
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
>NLJOIN UNION
( 5) ( 9)
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 76938
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
|
| |
76938
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
|
|
0
|
|
|
|
Reply
|
 Michel
|
2/24/2011 5:50:40 PM |
|
On 2011-02-24 18:50, Michel Esber wrote:
> 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?
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
>
> 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
> |
> 0.08
> 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
> >NLJOIN UNION
> ( 5) ( 9)
> 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 76938
> 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
> |
> | |
> 76938
> 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
>
>
|
|
|
0
|
|
|
|
Reply
|
Lennart
|
2/24/2011 6:28:40 PM
|
|
> What is the output of:
>
> select NUM_EXECUTIONS, ROWS_READ, POOL_DATA_L_READS, POOL_DATA_P_READS,
> =A0 =A0 =A0 =A0POOL_INDEX_L_READS, POOL_INDEX_P_READS, TOTAL_EXEC_TIME,
> =A0 =A0 =A0 =A0TOTAL_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 (%';
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
>> WATCH THE AVERAGE %USER TIME difference !!!!
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,
|
|
|
0
|
|
|
|
Reply
|
Michel
|
2/24/2011 7:46:32 PM
|
|
>
> Original Statement:
> ------------------
> MERGE INTO ASSET.TBL_ASSET_NET_DETAIL AS E USING (VALUES (cast(? as
> ...
> ...
> WHEN MATCHED AND ((E.MACHINE_HOSTNAME <> INDATA.MACHINE_HOSTNAME OR
> =A0 =A0 =A0 =A0 E.MACHINE_IPDOMAIN <> INDATA.MACHINE_IPDOMAIN OR E.LOGIN_=
NAME
> <>
> =A0 =A0 =A0 =A0 INDATA.LOGIN_NAME OR E.LAN_GROUP_TYPE <> INDATA.LAN_GROUP=
_TYPE
> OR
> =A0 =A0 =A0 =A0 E.LAN_GROUP_NAME <> INDATA.LAN_GROUP_NAME OR
> E.FIRE_LOGIN_TRIGGERS <>
> =A0 =A0 =A0 =A0 INDATA.FIRE_LOGIN_TRIGGERS))
> THEN
> UPDATE SET MACHINE_LOCALTIME_INSERT =3D
> INDATA.MACHINE_LOCALTIME_INSERT,
> =A0 =A0 =A0 =A0 MACHINE_HOSTNAME =3D INDATA.MACHINE_HOSTNAME, MACHINE_IPD=
OMAIN
> =3D
> =A0 =A0 =A0 =A0 INDATA.MACHINE_IPDOMAIN , LOGIN_NAME =3D INDATA.LOGIN_NAM=
E ,
> =A0 =A0 =A0 =A0 LAN_GROUP_TYPE =3D INDATA.LAN_GROUP_TYPE, LAN_GROUP_NAME =
=3D
> =A0 =A0 =A0 =A0 INDATA.LAN_GROUP_NAME, FIRE_LOGIN_TRIGGERS =3D
> =A0 =A0 =A0 =A0 INDATA.FIRE_LOGIN_TRIGGERS
How about this?
WHEN MATCHED
THEN
UPDATE
SET MACHINE_LOCALTIME_INSERT
=3D 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 =3D INDATA.MACHINE_HOSTNAME
, MACHINE_IPDOMAIN =3D INDATA.MACHINE_IPDOMAIN
, LOGIN_NAME =3D INDATA.LOGIN_NAME
, LAN_GROUP_TYPE =3D INDATA.LAN_GROUP_TYPE
, LAN_GROUP_NAME =3D INDATA.LAN_GROUP_NAME
, FIRE_LOGIN_TRIGGERS =3D INDATA.FIRE_LOGIN_TRIGGERS
|
|
|
0
|
|
|
|
Reply
|
Tonkuma
|
2/24/2011 9:17:27 PM
|
|
On 2011-02-24 20:46, Michel Esber wrote:
[...]
>
>>> WATCH THE AVERAGE %USER TIME difference !!!!
>
>
> 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% ...
>
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
|
|
|
0
|
|
|
|
Reply
|
Lennart
|
2/24/2011 9:44:41 PM
|
|
> How about this?
>
> WHEN MATCHED
> THEN
> UPDATE
> =A0 =A0SET MACHINE_LOCALTIME_INSERT
> =A0 =A0 =A0 =A0=3D CASE
> =A0 =A0 =A0 =A0 =A0WHEN E.MACHINE_HOSTNAME =A0 =A0<> INDATA.MACHINE_HOSTN=
AME
> =A0 =A0 =A0 =A0 =A0 =A0OR E.MACHINE_IPDOMAIN =A0 =A0<> INDATA.MACHINE_IPD=
OMAIN
> =A0 =A0 =A0 =A0 =A0 =A0OR E.LOGIN_NAME =A0 =A0 =A0 =A0 =A0<> INDATA.LOGIN=
_NAME
> =A0 =A0 =A0 =A0 =A0 =A0OR E.LAN_GROUP_TYPE =A0 =A0 =A0<> INDATA.LAN_GROUP=
_TYPE
> =A0 =A0 =A0 =A0 =A0 =A0OR E.LAN_GROUP_NAME =A0 =A0 =A0<> INDATA.LAN_GROUP=
_NAME
> =A0 =A0 =A0 =A0 =A0 =A0OR E.FIRE_LOGIN_TRIGGERS <> INDATA.FIRE_LOGIN_TRIG=
GERS
> =A0 =A0 =A0 =A0 =A0THEN INDATA.MACHINE_LOCALTIME_INSERT
> =A0 =A0 =A0 =A0 =A0ELSE E. =A0 =A0 MACHINE_LOCALTIME_INSERT
> =A0 =A0 =A0 =A0 =A0END
> =A0 =A0 =A0, MACHINE_HOSTNAME =A0 =A0=3D INDATA.MACHINE_HOSTNAME
> =A0 =A0 =A0, MACHINE_IPDOMAIN =A0 =A0=3D INDATA.MACHINE_IPDOMAIN
> =A0 =A0 =A0, LOGIN_NAME =A0 =A0 =A0 =A0 =A0=3D INDATA.LOGIN_NAME
> =A0 =A0 =A0, LAN_GROUP_TYPE =A0 =A0 =A0=3D INDATA.LAN_GROUP_TYPE
> =A0 =A0 =A0, LAN_GROUP_NAME =A0 =A0 =A0=3D INDATA.LAN_GROUP_NAME
> =A0 =A0 =A0, FIRE_LOGIN_TRIGGERS =3D INDATA.FIRE_LOGIN_TRIGGERS
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
|
|
|
0
|
|
|
|
Reply
|
Michel
|
2/24/2011 9:48:49 PM
|
|
> 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 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.
|
|
|
0
|
|
|
|
Reply
|
Michel
|
2/24/2011 10:20:47 PM
|
|
>
> 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 you may be able to avoid high CPU consumption (perhaps)based
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.)
|
|
|
0
|
|
|
|
Reply
|
Tonkuma
|
2/24/2011 10:37:15 PM
|
|
> However you may be able to avoid high CPU consumption (perhaps)based
> on inefficient access path in your MERGE statement.
>
> Was the access path still same by changing the MATCHED clause?
> If access path was chnged,
> =A0you can compare waste of CPU by unnecessary update and CPU
> consumption in your original MERGE statement.
>
> By the way,
> =A0on DB2/MVS(old name of DB2 for z/OS),
> =A0if 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,
|
|
|
0
|
|
|
|
Reply
|
Michel
|
2/24/2011 10:45:23 PM
|
|
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
|
|
|
0
|
|
|
|
Reply
|
ChrisC
|
2/25/2011 12:49:48 AM
|
|
On 2011-02-24 23:20, 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 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
>
I'm a bit puzzled. The index is almost unique, so I don't understand the
amount of index logical reads (it's not biljons of them, so I'm not sure
that they are the real problem). Do you have a testsystem where you can
try to exchange:
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
with
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
do a testrun and execute:
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 (%';
once again?
|
|
|
0
|
|
|
|
Reply
|
Lennart
|
2/25/2011 7:35:04 AM
|
|
> with
>
> WHEN MATCHED
> =A0AND NOT ((E.MACHINE_HOSTNAME =3D INDATA.MACHINE_HOSTNAME AND
> =A0 =A0 =A0 =A0 =A0 =A0E.MACHINE_IPDOMAIN =3D INDATA.MACHINE_IPDOMAIN AND
> =A0 =A0 =A0 =A0 =A0 =A0E.LOGIN_NAME =3D INDATA.LOGIN_NAME AND
> =A0 =A0 =A0 =A0 =A0 =A0E.LAN_GROUP_TYPE =3D INDATA.LAN_GROUP_TYPE AND
> =A0 =A0 =A0 =A0 =A0 =A0E.LAN_GROUP_NAME =3D INDATA.LAN_GROUP_NAME AND
> =A0 =A0 =A0 =A0 =A0 =A0E.FIRE_LOGIN_TRIGGERS =3D INDATA.FIRE_LOGIN_TRIGGE=
RS))
> THEN
>
> do a testrun and execute:
>
> select NUM_EXECUTIONS, ROWS_READ, POOL_DATA_L_READS, POOL_DATA_P_READS,
> =A0 =A0 =A0 =A0POOL_INDEX_L_READS, POOL_INDEX_P_READS, TOTAL_EXEC_TIME,
> =A0 =A0 =A0 =A0TOTAL_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 (%';
>
> once again?
Hi Lennart,
We have updated our application to use the NOT (A and B) instead of (A
or B), and we will send it to production servers very soon.
Also, we have opened a service request with IBM to help troubleshoot
the problem.
I will post more info about this very soon.
Thanks again,
|
|
|
0
|
|
|
|
Reply
|
Michel
|
2/26/2011 12:29:52 AM
|
|
> We have updated our application to use the NOT (A and B) instead of (A
> or B), and we will send it to production servers very soon.
Hi,
I have updated the application and here's the new info:
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
-------------------- -------------------- --------------------
-------------------- -------------------- --------------------
-------------------- --------------------
612 3414
3814 178 17019
125 51 0
Something I have noticed in the new access plan (which is similar to
the previous one I posted here):
Plan Details:
-------------
1) RETURN: (Return Result)
Cumulative Total Cost: 102.029
Cumulative CPU Cost: 117586
Cumulative I/O Cost: 4.08001
Cumulative Re-Total Cost: 27.0102
Cumulative Re-CPU Cost: 42551.5
Cumulative Re-I/O Cost: 1.08
Cumulative First Row Cost: 102.029
Estimated Bufferpool Buffers: 4.08001
Cumulative Total Cost: 102
Cumulative CPU cost: 117586 ...
Any thoughts?
|
|
|
0
|
|
|
|
Reply
|
Michel
|
2/28/2011 5:42:09 PM
|
|
Michael,
The plan for this MERGE statement looks squeaky clean as far as I can tell.
Sometimes people run into issues with MEREG because
a) DB2 can't prove that only one row in teh USINg mathes any given row
in the target.. You would see an ugly stack of operators above the
bottom join and the join with the UNION ALL in that case
b) DB2 decides to do HSJOIN which scans and U-locks the target table
c) Temps to avoid Halloween
You plan has none of these.
I do not believe this to be a compiler/optimizer problem.
Could it be that your updates kick of some nasty index maintenance?
Home many indexes on the table. (Doesn't explain maxing out CPU though)
Cheers
Serge
--
Serge Rielau
SQL Architect DB2 for LUW, IBM Toronto Lab
Blog: tinyurl.com/SQLTips4DB2
Wiki: tinyurl.com/Oracle2DB2Wiki
Twitter: srielau
|
|
|
0
|
|
|
|
Reply
|
Serge
|
2/28/2011 7:20:19 PM
|
|
Try using the following statement as the RUNSTATS for
ASSET.TBL_ASSET_NET_DETAIL
RUNSTATS ON TABLE ASSET.TBL_ASSET_NET_DETAIL ON ALL COLUMNS WITH
DISTRIBUTION AND DETAILED INDEXES ALL ;
See if this changes the plan. If it has the desired effect, I would
expect the query cost to go up slightly, but the actual CPU usage
might go down.
Keep us posted on the final findings.
Cheers -
Sanjuro
On Feb 28, 12:42=A0pm, Michel Esber <smes...@gmail.com> wrote:
> > We have updated our application to use the NOT (A and B) instead of (A
> > or B), and we will send it to production servers very soon.
>
> Hi,
>
> I have updated the application and here's the new info:
>
> NUM_EXECUTIONS =A0 =A0 =A0 ROWS_READ =A0 =A0 =A0 =A0 =A0 =A0POOL_DATA_L_R=
EADS
> POOL_DATA_P_READS =A0 =A0POOL_INDEX_L_READS =A0 POOL_INDEX_P_READS
> TOTAL_EXEC_TIME =A0 =A0 =A0TOTAL_USR_CPU_TIME
> -------------------- -------------------- --------------------
> -------------------- -------------------- --------------------
> -------------------- --------------------
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0612 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 34=
14
> 3814 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0178 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A017019
> 125 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 51 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A00
>
> Something I have noticed in the new access plan (which is similar to
> the previous one I posted here):
>
> Plan Details:
> -------------
>
> =A0 =A0 =A0 =A0 1) RETURN: (Return Result)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Cumulative Total Cost: =A0 =A0 =A0 =A0 =
=A0102.029
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Cumulative CPU Cost: =A0 =A0 =A0 =A0 =A0 =
=A0117586
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Cumulative I/O Cost: =A0 =A0 =A0 =A0 =A0 =
=A04.08001
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Cumulative Re-Total Cost: =A0 =A0 =A0 27.=
0102
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Cumulative Re-CPU Cost: =A0 =A0 =A0 =A0 4=
2551.5
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Cumulative Re-I/O Cost: =A0 =A0 =A0 =A0 1=
..08
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Cumulative First Row Cost: =A0 =A0 =A0102=
..029
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Estimated Bufferpool Buffers: =A0 4.08001
>
> Cumulative Total Cost: 102
> Cumulative CPU cost: 117586 ...
>
> Any thoughts?
|
|
|
0
|
|
|
|
Reply
|
Sanjuro
|
2/28/2011 8:34:47 PM
|
|
On 2011-02-28 18:42, Michel Esber wrote:
>> We have updated our application to use the NOT (A and B) instead of (A
>> or B), and we will send it to production servers very soon.
>
> Hi,
>
> I have updated the application and here's the new info:
>
> 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
> -------------------- -------------------- --------------------
> -------------------- -------------------- --------------------
> -------------------- --------------------
> 612 3414
> 3814 178 17019
> 125 51 0
>
> Something I have noticed in the new access plan (which is similar to
> the previous one I posted here):
>
> Plan Details:
> -------------
>
>
> 1) RETURN: (Return Result)
> Cumulative Total Cost: 102.029
> Cumulative CPU Cost: 117586
> Cumulative I/O Cost: 4.08001
> Cumulative Re-Total Cost: 27.0102
> Cumulative Re-CPU Cost: 42551.5
> Cumulative Re-I/O Cost: 1.08
> Cumulative First Row Cost: 102.029
> Estimated Bufferpool Buffers: 4.08001
>
>
> Cumulative Total Cost: 102
> Cumulative CPU cost: 117586 ...
>
> Any thoughts?
Do you still experience that the stmt is hogging your cpu? Looking at
the snapshot data we now have 6 rows_read per execution compared to 340
before. Logical data reads went from 341 to 6 and logical index reads
from 2000 to 28. Also total_exec_time went from 1.54 to 0.08, so I would
have guessed that the statement is more cpu friendly by now?
/Lennart
|
|
|
0
|
|
|
|
Reply
|
Lennart
|
2/28/2011 8:37:25 PM
|
|
> Do you still experience that the stmt is hogging your cpu? Looking at
> the snapshot data we now have 6 rows_read per execution compared to 340
> before. Logical data reads went from 341 to 6 and logical index reads
> from 2000 to 28. Also total_exec_time went from 1.54 to 0.08, so I would
> have guessed that the statement is more cpu friendly by now?
Hi Lennart,
Not exactly. I still get CPU ~100%.
I've received a reply from IBM. Just thought I'd share it with the
group:
"
Plan for the query looks good. I see no issues with the plan itself.
Going back and looking @ the data from last Friday, vmstat shows tons
of threads in run queue and blocked queue.
There are 4 merges and 1 grant on table TBL_ASSET_NET_DETAIL seen in
the dynamic sql snapshots. None of those are using a lot of CPU.
There are a total of 322 applications. Of those 120 are in Compiling
state, while 75 executing, 29 in disconnect pending. Rest in uow-
waiting or connect completed.
The 120 compiling apps do show some CPU usage.
Looking @ the stacks collected for all the edus from that time,
(i) I see a set of stacks in latch contention primarily
when allocating memory and during compilation. This may explains the
apps in compiling state.
0x00000035F46BA057 __sched_yield + 0x0007
0x00002AAAABF8DF55 sqloSpinLockConflict + 0x0159
0x00002AAAABFA6C1C _ZN7SMemSet12captureLatchEv + 0x00ba
0x00002AAAABF9B6E8 sqloCreateMemoryPoolEx + 0x0254
0x00002AAAABDE05C7
_Z10sqlnn_cmplP8sqeAgentP11sqlrrstrings17sqlnn_compileModesP14sqlrr_cmpl
_enviiPP9sqlnq_qur + 0x040d
0x00002AAAABF8DF55 sqloSpinLockConflict + 0x0159
0x00002AAAABFA6C1C _ZN7SMemSet12captureLatchEv + 0x00ba
0x00002AAAABFABD30
_ZN12SMemBasePool19getNewChunkSubgroupEmjmP13SQLO_MEM_POOLP12SMemLogEven
tPP17SqloChunkSubgroupbPP11SRawChunkCB
+ 0x016c
0x00002AAAADE63FA9
_ZN13SQLO_MEM_POOL19allocateMemoryBlockEmmmjmPPvPP17SqloChunkSubgroupPP1
2SMemLostNodeP12SMemLogEvent + 0x029d
0x00002AAAADE61B1F sqlogmblkEx + 0x0461
0x00002AAAACB8105F _Z15sqlno_alloc_aubP13sqlno_globalsP10sqlno_apcbm
+
0x00d5
0x00002AAAACB809AD _Z15sqlno_grow_poolP13sqlno_globalsP10sqlno_apcbmi
+
0x0071
(ii) I see various EDUs in the OS system calls.
0x00000035F46CFF7A mmap64 + 0x000a
0x00000035F4660B3B _IO_file_doallocate + 0x007b
0x00000035F466D53C _IO_doallocbuf + 0x002c
0x00000035F466CC50 _IO_file_underflow + 0x0180
0x00000035F466D57E _IO_default_uflow + 0x000e
0x00000035F4668E3B getc + 0x00ab
0x00002AAAABF994A0 sqloGetCPUUsage + 0x031e
0x00002AAAAC92F0BA
_ZN10sqlmon_acb16agent_stmt_startEjPKhS1_S1_tjtttjjiP8sqlrr_cb +
0x0746
I suspect the threads in the OS system call is what is causing the
slowdown and threads in run queue and block queue.
Please ask the customer to test the following :-
1. unset [i] DB2_SYSTEM_MONITOR_SETTINGS=NULL
2. Disable statement monitor switch
Statement (DFT_MON_STMT) = ON
db2set DB2_SYSTEM_MONITOR_SETTINGS=
db2 update dbm cfg using DFT_MON_STMT OFF
db2 terminate
db2stop
db2start
Run the workload and let us know the results.
"
I did follow the instructions, but the CPU usage was still around
100% ...
|
|
|
0
|
|
|
|
Reply
|
Michel
|
3/2/2011 9:46:08 PM
|
|
On 2011-03-02 22:46, Michel Esber wrote:
>> Do you still experience that the stmt is hogging your cpu? Looking at
>> the snapshot data we now have 6 rows_read per execution compared to 340
>> before. Logical data reads went from 341 to 6 and logical index reads
>> from 2000 to 28. Also total_exec_time went from 1.54 to 0.08, so I would
>> have guessed that the statement is more cpu friendly by now?
>
> Hi Lennart,
>
> Not exactly. I still get CPU ~100%.
>
> I've received a reply from IBM. Just thought I'd share it with the
> group:
>
Michael, thanks for sharing the info. If you manage to solve the
problem, it would be interesting to hear about it.
/Lennart
[...]
|
|
|
0
|
|
|
|
Reply
|
Lennart
|
3/3/2011 5:28:33 AM
|
|
|
17 Replies
370 Views
(page loaded in 0.265 seconds)
|
Search |
Post Question |
Groups |
Stream |
About |
Register
163 articles. 
0 followers. 