Which of today’s UBEs ran slower than usual?

Wouldn’t it be nice to have a list of jobs that ran today, but ran slower / faster than they do on average.   Perhaps know how much slower and how many there were.  Over time, this should give you some good statistical information on how JDE performance is tracking.

The invention of F986114 and F986114A are integral to being able to get this information out of the system.  A fairly basic oracle query over these tables can give you information on averages, increases and or decreases.

 

SELECT JCPID, JCVERS, JCJOBNBR, INNERCOUNT, INNERAVERAGE, INNERMIN, INNERMAX, (86400*(JCETDTIM-JCSTDTIM)) AS RUNTIME, TRUNC((86400*(JCETDTIM-JCSTDTIM))/INNERAVERAGE*100,2) AS "PERCENT INCREASE"
FROM SVM812.F986114,
(SELECT JCPID as INNERPID, JCVERS as INNERVERS, count(1) as INNERCOUNT, Avg(86400*(JCETDTIM-JCSTDTIM)) as INNERAVERAGE, min(86400*(JCETDTIM-JCSTDTIM)) AS INNERMIN, max(86400*(JCETDTIM-JCSTDTIM)) AS INNERMAX
from svm812.f986114
group by jcpid, JCVERS)
WHERE TO_CHAR(JCETDTIM, 'DDMMYYYY') = TO_CHAR(sysdate, 'DDMMYYYY')
AND JCPID = INNERPID and JCVERS=INNERVERS
order by 9 desc
;

JCVERS    JCJOBNBR    INNERCOUNT    INNERAVERAGE    INNERMIN    INNERMAX    RUNTIME   %INCREASE R55093021P    AALL001       351200    488    2.80737704918033    0    22    14    498.68
R55093021P    AALL001       351122    488    2.80737704918033    0    22    13    463.06
R5604572OZ    VHWT001       351154    3794    3.57564575645756    2    51    11    307.63
R55093021P    AALL001       351278    488    2.80737704918033    0    22    7    249.34
R5503B413C    AALL002       351008    1243    16.8833467417538    1    191    39    230.99
R551010Z      AALL002S      351260    5053    5.20878685929151    2    18    11    211.18
R12855        AALL001       351217    60    107.466666666667    10    315    223    207.5

The first two lines show that R55093021P has run 498% longer than average, although when we look at this – it’s taken 14 seconds, when it generally averages 2.8 for the 488 times it’s run! Wow that is very cool statistical information.

But are we faster or slower, or just an average day?

SELECT count(1) as slower
FROM SVM812.F986114,
(SELECT JCPID as INNERPID, JCVERS as INNERVERS, count(1) as INNERCOUNT, Avg(86400*(JCETDTIM-JCSTDTIM)) as INNERAVERAGE, min(86400*(JCETDTIM-JCSTDTIM)) AS INNERMIN, max(86400*(JCETDTIM-JCSTDTIM)) AS INNERMAX
from svm812.f986114
group by jcpid, JCVERS)
WHERE TO_CHAR(JCETDTIM, 'DDMMYYYY') = TO_CHAR(sysdate, 'DDMMYYYY')
AND JCPID = INNERPID and JCVERS=INNERVERS
AND TRUNC((86400*(JCETDTIM-JCSTDTIM))/INNERAVERAGE*100,2) > 100
;
  SELECT count(1) as faster
FROM SVM812.F986114,
(SELECT JCPID as INNERPID, JCVERS as INNERVERS, count(1) as INNERCOUNT, Avg(86400*(JCETDTIM-JCSTDTIM)) as INNERAVERAGE, min(86400*(JCETDTIM-JCSTDTIM)) AS INNERMIN, max(86400*(JCETDTIM-JCSTDTIM)) AS INNERMAX
from svm812.f986114
group by jcpid, JCVERS)
WHERE TO_CHAR(JCETDTIM, 'DDMMYYYY') = TO_CHAR(sysdate, 'DDMMYYYY')
AND JCPID = INNERPID and JCVERS=INNERVERS
AND TRUNC((86400*(JCETDTIM-JCSTDTIM))/INNERAVERAGE*100,2) <= 100

The combination of the above two queries (I’m sure there is a better way) shows how many jobs are slower than average (69 in my example) and how many are faster (140).  So on average, today is a faster day!