Avarage time for completed Concurrent Reguests
                                                 Last update (2010-11-02 16:37:22)
                                                                                                                   Date added (2008-02-18 15:25:31)

Summary
To have a feeling of how much time gets a request to finish one good idea is to calculate the average time taken by requests to run.
SELECT /*+ index(r FND_CONCURRENT_REQUESTS_N6) */ f.application_short_name apps, p.concurrent_program_name, 
   p.user_concurrent_program_name description, COUNT(*) COUNT,
   DECODE(TRUNC(AVG(actual_completion_date - actual_start_date)), 0, NULL, TRUNC(AVG(actual_completion_date - actual_start_date)) || ' Days' || ' + ') || 
   TO_CHAR(TO_DATE(TRUNC(MOD(AVG(actual_completion_date - actual_start_date),1) * 86400), 'SSSSS'), 'HH24:MI:SS') average,
   DECODE(TRUNC(MAX(actual_completion_date - actual_start_date)), 0, NULL, TRUNC(MAX(actual_completion_date - actual_start_date)) || ' Days' || ' + ') || 
   TO_CHAR(TO_DATE(TRUNC(MOD(MAX(actual_completion_date - actual_start_date),1) * 86400), 'SSSSS'), 'HH24:MI:SS') MAX,
   DECODE(TRUNC(MIN(actual_completion_date - actual_start_date)), 0, NULL, TRUNC(MIN(actual_completion_date - actual_start_date)) || ' Days' || ' + ') || 
   TO_CHAR(TO_DATE(TRUNC(MOD(MIN(actual_completion_date - actual_start_date),1) * 86400), 'SSSSS'), 'HH24:MI:SS') MIN
FROM applsys.fnd_concurrent_request_class c,
     applsys.fnd_application f,
     apps.fnd_concurrent_programs_vl p,
     applsys.fnd_concurrent_requests r
WHERE r.program_application_id = p.application_id
   AND r.concurrent_program_id = p.concurrent_program_id
   AND r.phase_code='C' AND r.status_code = 'C' 
   AND actual_completion_date BETWEEN SYSDATE-2 AND SYSDATE
   AND p.application_id = f.application_id
   AND r.program_application_id = f.application_id
   AND r.request_class_application_id = c.application_id(+)
   AND r.concurrent_request_class_id = c.request_class_id(+)
-- AND p.concurrent_program_name LIKE '%CC_VOICE_OM_PROMITHEAS%'
-- AND p.user_concurrent_program_name like '%RAX%'
GROUP BY c.request_class_name, f.application_short_name,
   p.concurrent_program_name, p.user_concurrent_program_name, r.priority
ORDER BY average DESC, APPS ASC;
For example output:
AX Posting - Transfer To GL run: 10 times
Maximum time:01:47:08
Minimum time:00:07:44
Average time:01:01:57
APPS CONCURRENT_PROGRAM_NAME DESCRIPTION COUNT AVERAGE MAX MIN
XXACC XXACC_IICM_TO_AR XXACC Reconciliation Report Settlements (IICM To AR) 2 12:28:34 15:12:15 09:44:53
XXGR XXGR_CASH_CLEAR_REP OTE AR - Cash Clearing Report(1) 7 08:42:09 13:22:39 07:19:34
XXACC XXACC_LOCKBOX XXACC Reconciliation Report for Lockbox Interface (GNV-AR) 1 07:29:04 07:29:04 07:29:04
XXACC XXACC_PAY_MATCH_INTERF XXACC Payment Matching Interface 1 02:41:08 02:41:08 02:41:08
XXACC XXACC_OTEDUNN_PARALLEL XXACC OTEDunn Interface (Parallel) 3 02:34:12 03:04:04 02:14:14
AX XXACC_AXXPSMAN XXACC - Posting Manager Custom 4 02:17:56 06:33:45 00:27:17
PO OTE_PO_SYMVASEIS_EA ΟΤΕ-ΠΑΡΑΚΟΛΟΥΘΗΣΗ ΣΥΜΒΑΣΕΩΝ - ΕΝΤΟΛΩΝ ΑΓΟΡΑΣ - ΑΠΟΔΕΣΜΕΥΣΕΩΝ 2 01:45:44 01:46:47 01:44:41
AR XXOCGARDC OTE - Control feeding AR - GNV revenue data 2 01:24:17 01:34:40 01:13:53
AR RAXMTR Autoinvoice Master Program 2 01:14:44 02:29:25 00:00:03
AX XXACC_TRANSFERTOGL XXACC - AX Posting - Transfer To GL 10 01:01:57 01:47:08 00:07:44
SQLGL GLCCON Consolidation Transfer 1 01:01:45 01:01:45 01:01:45
AR RAXTRX Autoinvoice Import Program 234 00:55:27 12:59:27 00:19:23
AR XXACC_TEAM_ACC_REFRESH XXACC Team Account Source Table Refresh 3 00:54:44 01:26:45 00:23:32
XXACC XXACC_ARLPLB XXACC - Process Lockboxes 1.0 28 00:50:30 02:13:13 00:01:50
PA OTE_KAT_ERGON TEST-REPORTS1 2 00:38:33 00:48:45 00:28:21
SQLGL EURO_GL_ISOZ_ALL EURO-ΟΤΕ ΑΝΑΛΥΤΙΚΟ ΙΣΟΖΥΓΙΟ ΓΕΝ.ΛΟΓΙΣΤΙΚΗΣ 34 00:35:20 07:12:33 00:00:03
AX XXACC_EVENTTRN XXACC - Event Translation 95 00:27:48 02:25:45 00:00:02
XXI XXI_LLU_UPDATE_WCRM_STATUS XXI : Update WCRM status for LLU Orders 76 00:27:20 01:30:48 00:16:57
XXGR XXGR_CREATE_ATM_DD_CODE XXGR Create ATM DD Code 1 00:25:36 00:25:36 00:25:36
AR XXOCGNVD OTE - Feeding GNV revenue data 1 00:24:51 00:24:51 00:24:51
Reviews
Filters
Search