REM RMINC_rptsump.sql
REM 
REM Copyright 2000, All Rights Reserved, Reed-Matthews, Inc.
REM 
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.
REM
REM 
set timing off
set heading on
set verify off
set show off
set echo off
set feedback off
set pagesize 59 
ttitle CENTER 'Report Summary by Program' 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 a55;
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 'TYPE'
column priority format 999 heading 'PRI'
column concurrent_queue_name format a14 heading 'Concurrent Mgr' trunc
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_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(+)
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
/