rem queue_status.sql
rem
ttitle 'Concurrent Manager Queue Status'
rem
col request_class_name format a20 heading 'REQUEST CLASS NAME'
col program format a26 trunc heading 'Program'
col manager format a16 trunc heading 'Manager|Queue'
col phase_code format a1 heading 'P'
col status_code format a1 heading 'S'
col concurrent_program_name format a12 heading 'PROGRAM NAME'
col phase_code_r format b999 heading 'RUNNING|JOBS'
col running_gt_30_min format b999 heading 'RUN|> 30|MIN'
col running_gt_10_min format b999 heading 'RUN|> 10|MIN'
col running_lt_1_min format b999 heading 'RUN|< 1|MIN'
col phase_code_p format b999 heading 'PENDING|JOBS'
col status_code_q format b999 heading 'STANDBY|JOBS'
col status_code_i format b999 heading 'TOTAL|WAIT|JOBS'
col pending_gt_1_min format b999 heading 'WAIT|> 1|MIN'
col pending_gt_5_min format b999 heading 'WAIT|> 5|MIN'
col pending_gt_5_min format b999 heading 'WAIT|> 5|MIN'
col pending_gt_30_min format b999 heading 'WAIT|> 30|MIN'
rem
break on report
compute sum -
of phase_code_r running_gt_30_min running_gt_10_min running_lt_1_min phase_code_p -
status_code_q status_code_i pending_gt_1_min pending_gt_5_min pending_gt_30_min -
on report
rem
select nvl( c.request_class_name, 'STANDARD' ) request_class_name,
count( decode( phase_code, 'R', 'R' ) ) phase_code_r,
count( decode( phase_code, 'R',
decode( 1/1440, greatest( 1/1440, sysdate - r.actual_start_date ),
'R' ) ) ) running_lt_1_min,
count( decode( phase_code, 'R',
decode( 10/1440, least( 10/1440, sysdate - r.actual_start_date ),
'R' ) ) ) running_gt_10_min,
count( decode( phase_code, 'R',
decode( 30/1440, least( 30/1440, sysdate - r.actual_start_date ),
'R' ) ) ) running_gt_30_min,
count( decode( phase_code, 'P', 'P' ) ) phase_code_p,
count( decode( status_code, 'Q', 'Q' ) ) status_code_q,
count( decode( status_code, 'I', 'I' ) ) status_code_i,
count( decode( status_code, 'I',
decode( 1/1440, greatest( 1/1440, sysdate - greatest( r.requested_start_date, r.request_date ) ),
null, 'I' ) ) ) pending_gt_1_min,
count( decode( status_code, 'I',
decode( 5/1440, greatest( 5/1440, sysdate - greatest( r.requested_start_date, r.request_date ) ),
null, 'I' ) ) ) pending_gt_5_min,
count( decode( status_code, 'I',
decode( 30/1440, greatest( 30/1440, sysdate - greatest( r.requested_start_date, r.request_date ) ),
null, 'I' ) ) ) pending_gt_30_min
from applsys.fnd_concurrent_requests r,
applsys.fnd_concurrent_request_class c
where r.request_class_application_id = c.application_id(+)
and r.concurrent_request_class_id = c.request_class_id(+)
and phase_code in ( 'P', 'R' )
and status_code not in ( 'W' )
and r.hold_flag = 'N'
and greatest( r.requested_start_date, r.request_date ) <= sysdate
group by c.request_class_name
order by 1;