REM RMINC_usage_sum.sql
REM written by Barb Matthews
REM You'll need to enter the date from which you want this
REM report to start calculating
REM This report helps us look for performance problems. It
REM reports the total  number of concurrent requests that were
REM run each day and summarizes the day's average run time and average
REM wait time.  It also lists the day of the week to make it easier for us
REM to spot trends like heavy month-end processing and differences in
REM system usage on different days of the week.
spool RMINC_usage_sum.lis
set verify off
accept edate prompt 'Enter start date: '
set pagesize 59
set linesize 180
set newpage 0
set recsep off
column today new_value _date noprint;
select to_char(SYSDATE,'DD-MON-YY') today from dual;
column startdate format a15 heading 'START|DATE';
column edate NOPRINT format a18;
ttitle "Daily Concurrent Manager Performance History from: " edate " to " _date skip 2;
break on actual_start_date skip 1;
compute sum of cnt on actual_start_date;
compute sum of elapsed on actual_start_date;
compute sum of average on actual_start_date;
compute sum of waited on actual_start_date;
compute sum of avewait on actual_start_date;
column elapsed format 99999.99 heading 'TOTAL|HOURS';
column average format 9999.99 heading 'AVG.|HOURS';
column waited format 99999999.99 heading 'WAITED|HOURS';
column avewait format 9999.99 heading 'AVG.|WAIT';
column cnt format 999,999 heading 'COUNT';
select
 to_char(trunc(actual_start_date),'DD-MON-YY DY') startdate,
 count(*) cnt,
 sum(actual_completion_date - actual_start_date) * 24 elapsed,
 avg(actual_completion_date - actual_start_date) * 24 average,
 sum(actual_start_date - requested_start_date) * 24 waited,
 avg(actual_start_date - requested_start_date) * 24 avewait
 from fnd_concurrent_programs 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')
 and actual_completion_date > '&&edate'
 group by 
 trunc(actual_start_date) 
 ;