Top ten worst transaction times
The following query reports the top 10 worst response times:
SELECT TRAN,
TIME(START) AS "Start Time",
DEC(RESPONSE_TIME,10,4) AS "Response Time",
DEC(CPU_TIME,10,4) AS "CPU Time",
DEC(SUSPEND_TIME,10,4) AS "Suspend Time",
DEC(DISPATCH_TIME,10,4) AS "Dispatch Time"
FROM CPADB.AORLIST
ORDER BY RESPONSE_TIME DESC
FETCH FIRST 10 ROWS ONLY
OPTIMIZE FOR 10 ROWS
This query produces output like the following:
Figure 1. SQL query listing top 10 worst response
times
TRAN Start Time Response Time CPU Time Suspend Time Dispatch Time
---- ---------- ------------- ----------- ------------ -------------
CSOL 13.14.34 1887.6433 0.0004 1887.6428 0.0005
CQRY 14.26.57 11.1696 0.0008 11.1636 0.0060
MV02 14.09.45 10.8949 0.0176 10.8724 0.0225
TANS 13.47.03 9.1463 0.3634 8.6515 0.4948
TANS 14.16.50 7.6264 0.3534 7.1469 0.4795
MV14 14.25.33 6.0772 0.0216 6.0395 0.0377
ADBQ 12.00:40 4.0492 0.0023 0.0011 0.0012
CDAA 14.25.33 3.0232 0.0153 0.0120 0.0129
BINS 11.12.54 2.0112 0.0022 0.0221 0.0177
CFIM 12.11.31 1.0938 0.0153 0.0122 0.0032