IBM Support

Several scan threads with cond wait await_MCx causing performance bottlenecks

Troubleshooting


Problem

There are several Decisition Support System (DSS) threads, like group_1.1, nljoin_2.0, scan_3.0, hjoin_1.1:
9921446 1368412b0 111bcb418 1 cond wait await_MC1 17cpu group_1.1
9921447 1351ffcd0 11a8a5988 1 cond wait await_MC2 10cpu nljoin_2.0
9921448 1368417c0 11a8ad6a8 1 cond wait await_MC2 20cpu nljoin_2.1
9921449 137006cd0 1189de238 1 cond wait await_MC2 14cpu nljoin_2.2
9921450 137006028 11768d7d8 1 cond wait await_MC2 20cpu nljoin_2.3
9921451 1228d4028 117695de8 1 cond wait await_MC2 16cpu nljoin_2.4

Symptom

You can even see it running/waiting in the Memory Grant Manager in the Active and Ready Queries queues:
Active Queries:
---------------
Session Query Priority Thread Memory Scans Gate
2251146 13cca2758 30 304/14571 4/4 -
2251148 13c29a758 30 304/14571 4/4 -
2251150 12988e758 30 304/14571 4/4 -
Ready Queries:
--------------
Session Query Priority Thread Memory Scans Gate
2251154 1344d8758 30 140e362b0 0/14571 0/4 1
2251149 120ba0758 30 136841a48 0/14571 0/4 3
2250893 11b2d98b8 30 128934cd0 0/0 0/1 3
2251153 12a187758 30 13851d2b0 0/14571 0/4 3
2251156 117f73758 30 13851dcd0 0/14571 0/4 3
2251157 126988758 30 1370062b0 0/14571 0/4 3
2250969 1206e58b8 30 13fd4c028 0/0 0/1 3
2251159 1375c1758 30 13d1fccd0 0/14571 0/1 3
2251158 1237ec758 30 13755dcd0 0/14571 0/1 3
In the onstat -g con output, which shows the threads in conditions with waiters, we see several threads stuck:
Conditions with waiters:
cid addr name waiter waittime
716 111fae3e0 ReadAhead 148 9
895 10a15dd50 bp_cond 193 505
1001 112719bf8 await_MC1 9921328 215
 9921329 215
1321 112a339f8 await_MC3 9921319 215
 9921317 215
 9921316 215
 9921318 215
1833 112bae5f8 await_MC5 9921360 215
2221 112bdc7c0 await_MC2 9921449 211
 
You can't find any particular session causing it, but several, and they're all waiting for a condition (Y flag in the 2nd column):
address flags sessid user tty wait tout locks nreads nwrites
11a8ba438 Y------ 2251149 rdh - 1292a4e68 0 3 0 0
11a8bb618 Y------ 2251174 rdh - 113a44760 0 1 0 0
11a8bd9d8 Y------ 2251174 rdh - 1167ea2c8 0 1 0 0
11a8be2c8 Y------ 2251149 rdh - 113e34ec8 0 3 0 0
11a8bebb8 Y------ 2251154 rdh - 1212ae720 0 3 0 0
11a8bfd98 Y------ 2251154 rdh - 12b6dd4e8 0 3 0 0

Document Location

Worldwide

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSGU8G","label":"Informix Servers"},"ARM Category":[{"code":"a8m0z0000001ivdAAA","label":"Performance-\u003EPDQ"}],"ARM Case Number":"TS003728936","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Version(s)","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Log InLog in to view more of this document

This document has the abstract of a technical article that is available to authorized users once you have logged on. Please use Log in button above to access the full document. After log in, if you do not have the right authorization for this document, there will be instructions on what to do next.

Document Information

Modified date:
21 December 2020

UID

ibm16219150

Page Feedback