Preventive Service Planning
Abstract
When INTRA_PARALLEL parameter is enabled, query optimizer has to choose parallel execution strategies that maintain data balance among subagents and keep them equally busy to better use multi-core processors. It sometime can cause wait time for FCM send (FCM_SEND_WAIT_TIME) and receive (FCM_RECV_WAIT_TIME). In such case it will be good to know if optimizer is not spending much time on FCM send and WAIT internal requests.
Content
1. To monitor FCM send and receive wait time you can run dbsummary() procedure. The procedure generates a text-formatted monitoring report.
db2 "call monreport.dbsummary()"
Partial output from monreport.dbsummary() -
-- Detailed breakdown of TOTAL_WAIT_TIME --
% Total
--- ---------------------------------------------
TOTAL_WAIT_TIME 100 11670
I/O wait time
POOL_READ_TIME 40 4710
POOL_WRITE_TIME 0 0
DIRECT_READ_TIME 0 0
DIRECT_WRITE_TIME 0 0
LOG_DISK_WAIT_TIME 0 0
LOCK_WAIT_TIME 0 0
AGENT_WAIT_TIME 0 0
Network and FCM
TCPIP_SEND_WAIT_TIME 0 0
TCPIP_RECV_WAIT_TIME 0 0
IPC_SEND_WAIT_TIME 0 0
IPC_RECV_WAIT_TIME 0 0
FCM_SEND_WAIT_TIME 5 610
FCM_RECV_WAIT_TIME 54 6348
WLM_QUEUE_TIME_TOTAL 0 0
2. Another way to monitor FCM send and receive wait time at connection level is using MON_GET_CONNECTION_DETAILS table function
db2 "select * from table(mon_get_connection(cast(null as bigint), -2)) as t order by rows_returned desc"
Partial output of the query executed every 60 seconds -
APPLICATION_HANDLE APPLICATION_NAME .... FCM_RECV_VOLUME FCM_RECVS_TOTAL FCM_SEND_VOLUME FCM_SENDS_TOTAL FCM_RECV_WAIT_TIME FCM_SEND_WAIT_TIME ....
-------------------- ---------------- .... -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- ....
51 db2bp 3458041 3205 3492525 3438 18818 642 ....
51 db2bp 5522895 5562 5575879 5920 53304 642 ....
51 db2bp 162802654 605356 162874878 605844 63235 1637 ....
51 db2bp 162898753 605724 162970977 606212 63379 1637 ....
To resolve the FCM send and receive wait time, try running the same workload with INTRA_PARALLEL disabled.
db2 update dbm cfg using INTRA_PARALLEL no
DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully.
SQL1362W One or more of the parameters submitted for immediate modification
were not changed dynamically. Client changes will not be effective until the
next time the application is started or the TERMINATE command has been issued.
Server changes will not be effective until the next DB2START command.
db2stop
db2start
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21983291