Summary
Use this script to find how the concurrent managers (rather than the concurrent programs) are performing. Don't run it for longer than you save logs in your concurrent manager tables. So if you run the
Purge Concurrent Requests and/or Manager Data for everything older than 7 days, then you should only run this for 7 days worth of information. You might schedule this to run every week, save the results and compare them over time to look for patterns.
SELECT Q.USER_CONCURRENT_QUEUE_NAME MANAGER, COUNT(*) CNT,
ROUND(SUM(R.ACTUAL_COMPLETION_DATE - R.ACTUAL_START_DATE) * 24, 2) RUNNING_HOURS,
ROUND(AVG(R.ACTUAL_COMPLETION_DATE - R.ACTUAL_START_DATE) * 24, 2) AVG_RUNNING_HOURS,
ROUND(SUM(R.ACTUAL_START_DATE - R.REQUESTED_START_DATE) * 24, 2) WAITED_HOURS,
ROUND(AVG(R.ACTUAL_START_DATE - R.REQUESTED_START_DATE) * 24, 2) AVG_WAITED_HOURS
FROM APPS.FND_CONCURRENT_PROGRAMS P, APPS.FND_CONCURRENT_REQUESTS R,
APPS.FND_CONCURRENT_QUEUES_TL Q, APPS.FND_CONCURRENT_PROCESSES P
WHERE R.PROGRAM_APPLICATION_ID = P.APPLICATION_ID
AND R.CONCURRENT_PROGRAM_ID = P.CONCURRENT_PROGRAM_ID
AND R.STATUS_CODE IN ('C','G')
AND R.CONTROLLING_MANAGER=P.CONCURRENT_PROCESS_ID
AND Q.CONCURRENT_QUEUE_ID=P.CONCURRENT_QUEUE_ID
AND R.CONCURRENT_PROGRAM_ID=P.CONCURRENT_PROGRAM_ID
AND TRUNC(ACTUAL_START_DATE) > TRUNC(SYSDATE-5)
GROUP BY Q.USER_CONCURRENT_QUEUE_NAME;
MANAGER COUNT RUNNING_HOURS AVG_RUNNING_HOURS PENDING_HOURS AVG_PENDING_HOURS
------------------------------ ---------- ------------- ----------------- ------------- -----------------
OTE-Payables Manager 138 2.47 .02 .37 0
XXACC Reports Manager 22 8.34 .38 122.65 5.58
OTE_Posting Manager 676 274.95 .41 78.63 .12
OTE2 GL Accounting 474 70.14 .15 70.51 .15
OTE1 GL Accounting 1274 17.69 .01 5.33 0
OTE-Standard Manager 63144 1409.21 .02 6865.78 .11
PA Streamline Manager 4 .44 .11 -.44 -.11
Standard Manager 71506 1968.7 .03 6119.29 .09
OTE_Autoinvoice_Lockbox Manage 1296 685.18 .53 4178.43 3.22
