Tuesday, July 10, 2012

Concurrent programs maximum, minimum and average execution times report

The following script will give you the maximum, minimum and average execution times of each concurrent program had been executed in your eBS. It will measure only the successfully completed concurrent requests.
SELECT P.CONCURRENT_PROGRAM_NAME,
         PT.USER_CONCURRENT_PROGRAM_NAME,
         COUNT (*),
            TRUNC (MAX (F.ACTUAL_COMPLETION_DATE - F.ACTUAL_START_DATE))
         || ' Days'
         || ' + '
         || TO_CHAR (
               TRUNC (SYSDATE)
               + NUMTODSINTERVAL (
                    MAX ( (F.ACTUAL_COMPLETION_DATE - F.ACTUAL_START_DATE))
                    * 86400,
                    'second'),
               'HH24:MI:SS')
            "MAXIMUM",
            TRUNC (MIN (F.ACTUAL_COMPLETION_DATE - F.ACTUAL_START_DATE))
         || ' Days'
         || ' + '
         || TO_CHAR (
               TRUNC (SYSDATE)
               + NUMTODSINTERVAL (
                    MIN ( (F.ACTUAL_COMPLETION_DATE - F.ACTUAL_START_DATE))
                    * 86400,
                    'second'),
               'HH24:MI:SS')
            "MINIMUM",
            TRUNC (AVG (F.ACTUAL_COMPLETION_DATE - F.ACTUAL_START_DATE))
         || ' Days'
         || ' + '
         || TO_CHAR (
               TRUNC (SYSDATE)
               + NUMTODSINTERVAL (
                    AVG ( (F.ACTUAL_COMPLETION_DATE - F.ACTUAL_START_DATE))
                    * 86400,
                    'second'),
               'HH24:MI:SS')
            "AVERAGE"
    FROM APPLSYS.FND_CONCURRENT_REQUESTS F,
         APPLSYS.FND_CONCURRENT_PROGRAMS P,
         APPLSYS.FND_USER A,
         APPLSYS.FND_CONCURRENT_PROGRAMS_TL PT
   WHERE     PHASE_CODE = 'C'
         AND STATUS_CODE = 'C'
         AND F.CONCURRENT_PROGRAM_ID = P.CONCURRENT_PROGRAM_ID
         AND P.CONCURRENT_PROGRAM_ID = PT.CONCURRENT_PROGRAM_ID
         AND A.USER_ID(+) = F.REQUESTED_BY
GROUP BY P.CONCURRENT_PROGRAM_NAME, PT.USER_CONCURRENT_PROGRAM_NAME;

CONCURRENT_PROGRAM_NAMEUSER_CONCURRENT_PROGRAM_NAMECOUNT (*)MAXIMUMMINIMUMAVERAGE
XXI_UPD_CONF_ORDER_STATUSXXI : Update Order Status for Confirmed Orders330760 Days + 00:10:510 Days + 00:00:040 Days + 00:00:11
FNDWFBGWorkflow Background Process489058 Days + 13:34:250 Days + 00:00:000 Days + 00:02:42
FAPROJDepreciation Projection370 Days + 03:12:100 Days + 00:00:350 Days + 00:45:31
XXIEX_RUN_STRATEGIESRequest Set OTE Run Strategies1080 Days + 19:42:550 Days + 07:17:490 Days + 12:51:35
..................

1 comment: