REM RMINC_high_requests.sql
set echo off;
set heading off;
set feedback off;
REM This program, run by CRON, tells us if the number of concurrent
REM requests waiting in any queue is higher than 30, suggesting that
REM we may have a problem with a job that either was incorrectly set
REM up to pull a large amount of data back, causing a performance
REM problem, or a job that should be run during off hours for the 
REM same reason.
REM You'll want to add in appropriate directory structures for where files should be saved
REM This can be run by applsys or sys.
REM Copyright 2000, All Rights Reserved, Reed-Matthews, Inc.
REM 

spool RMINC_high_requests_results1.lst
col v1 new_value v1

select v.user_concurrent_queue_name,COUNT(phase_code) v1
   from  apps.fnd_concurrent_queues_vl v,
         apps.fnd_concurrent_worker_requests r
   where r.queue_application_id = 0
     and r.phase_code = 'P'                -- Pending
     and r.hold_flag != 'Y'                -- not on hold
     and r.requested_start_date <= SYSDATE -- No Future jobs
     and r.concurrent_queue_id=v.concurrent_queue_id
   group by v.user_concurrent_queue_name
   having COUNT (phase_code) >=30 
;

spool off
host echo "&v1" > RMINC_high_requests_results2.lst
exit
&v1
exit