REM Copyright 2003, All Rights Reserved, Reed-Matthews, Inc.
REM $RMINC_TOP/sql/RMINC_rptsump.sql
REM Concurrent Request Performance Summary (RMINC)
REM If you haven't gone to the trouble of creating a historical
REM table to save older fnd_concurrent_request data, then
REM use the version of RMINC_rptsump.sql available under
REM 'How Manage the Concurrent Manager (or How to Herd Cats).
REM It uses just what is available in the fnd_concurrent_requests
REM table

REM This report shows the elapsed time history
REM for all the concurrent requests that have
REM run during a given time period.  You can
REM use it to help assign requests that always
REM run fast to one queue, while assigning
REM requests that always run slow to a slow
REM queue, and leaving the inconsistent ones in
REM the standard queue.

set timing off
set heading on
set verify off
set show off
set echo off
set feedback off
set pagesize 59
set linesize 180

REM &1 start date
REM &2 end date
REM &3 first order by 
REM &4 second order by
 
ttitle CENTER 'Concurrent Request Performance Summary' SKIP 1 -
CENTER 'All Times are Elapsed Time - Ordered by  &&3, &&4 from &&1 thru: &&2' -
RIGHT ' Page: ' FORMAT 999 sql.pno SKIP 2
column elapsed format 999.99 heading 'TOTAL|HOURS';
column average format 99.99 heading 'AVG|HOURS';
column max     format 99.99 heading 'MAX|HOURS';
column min     format 99.99 heading 'MIN|HOURS';
column waited  format 99999.99 heading '#WAITED|HOURS';
column avewait format 9999.99 heading 'AVG|WAIT';
column program format a20 heading 'PROGRAM';
column description format a40;
column cnt format 999,999 heading '#TIMES|RUN';
column app format a6 heading 'APP';
compute sum of waited on actual_start_date;
compute sum of avewait on actual_start_date;
column stddev format 999.99 heading 'RUN|STDDEV';
column wstddev format 999.99 heading 'WAIT|STDDEV'
column type format a15 heading 'REQ|TYPE'
column priority format 999 heading 'PRI'

select
   f.application_short_name app,
   substr(p.user_concurrent_program_name,1,55) description,
   substr(p.concurrent_program_name,1,20) program,
   r.priority,
   count(*) cnt,
   sum(actual_completion_date - actual_start_date) * 24 elapsed,
   avg(actual_completion_date - actual_start_date) * 24 average,
   max(actual_completion_date - actual_start_date) * 24 max,
   min(actual_completion_date - actual_start_date) * 24 min,
   stddev(actual_completion_date - actual_start_date) * 24 stddev,
   stddev(actual_start_date - requested_start_date) * 24 wstddev,
   sum(actual_start_date - requested_start_date) * 24 waited,
   avg(actual_start_date - requested_start_date) * 24 avewait,
   c.request_class_name type
from fnd_concurrent_queues fcq,
     fnd_concurrent_queue_content fcqc,
     fnd_concurrent_request_class c,
     fnd_application f,
     fnd_concurrent_programs_vl p,
     fnd_concurrent_requests r
where r.program_application_id = p.application_id
   and r.concurrent_program_id = p.concurrent_program_id
   and r.status_code in ('C','G','E')
   and actual_completion_date between '&&1' and '&&2'
   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 r.request_class_application_id = fcqc.type_application_id(+)
   and r.concurrent_request_class_id = fcqc.type_id(+)
   and fcqc.queue_application_id = fcq.application_id(+)
   and fcqc.concurrent_queue_id = fcq.concurrent_queue_id(+)
group by
   c.request_class_name,
   f.application_short_name,
   p.concurrent_program_name,
   p.user_concurrent_program_name,
   r.priority
order by &&3 desc,&&4 desc
/