Skip to content

Oracle Applications Concurrent Manager related scripts

Couple of Oracle Application Concurrent Manager related scripts which we use frequently.

A) UNDER WHICH MANAGER THE REQUEST WAS RUN

SELECT
b.user_concurrent_queue_name
FROM
fnd_concurrent_processes a
,fnd_concurrent_queues_vl b
,fnd_concurrent_requests c
WHERE 1=1
AND a.concurrent_queue_id = b.concurrent_queue_id
AND a.concurrent_process_id = c.controlling_manager
AND c.request_id = &request_id;

 

B) USER SESSION BY CONCURRENT MANAGER REQUEST ID

set linesize 132
col pid format 9999 heading 'PID'
col spid format a6 heading 'SERVER|PID'
col sid format 9999 heading 'SID'
col serial# format 99999 heading 'SERIAL'
col process format a6 heading 'CLIENT|PID'
col osuser format a8 heading 'OS|USERNAME'
col username format a10 heading 'ORACLE|USERNAME'
col log_per_sec format 999999 heading 'LOG|PER|SEC'
col logical format b9999999999 heading 'LOGICAL|READS'
col phy_per_sec format b9999 heading 'PHY|PER|SEC'
col physical_reads format b99999999 heading 'PHYSICAL|READS'
col audsid format b9999999 heading 'AUDIT|SESSION'
col program format a32 heading 'PROGRAM NAME'
col logon_time format a8 heading 'LOGON|TIME'
col duration format a8 heading 'DURATION'
col last_call_min format 9999 heading 'LAST|CALL|MIN'
col status format a1 heading 'S'
rem
rem break on report
rem compute sum of log_per_sec phy_per_sec on report
rem
select s.process,
p.spid,
s.sid,
s.serial#,
s.osuser,
s.username,
( i.block_gets + i.consistent_gets ) /
( ( sysdate - s.logon_time ) * 86400 ) log_per_sec,
i.block_gets + i.consistent_gets logical,
physical_reads /
( ( sysdate - s.logon_time ) * 86400 ) phy_per_sec,
i.physical_reads,
to_char( trunc(sysdate) + ( sysdate - s.logon_time ), 'hh24:mi:ss' ) duration,
s.last_call_et/60 last_call_min,
decode( s.status, 'ACTIVE', '*', 'INACTIVE', null, 'KILLED', 'K', '?' ) status,
s.module program
from v$process p, v$session s, v$sess_io i,
applsys.fnd_concurrent_requests r
where i.sid = s.sid
and s.paddr = p.addr
and r.oracle_session_id = s.audsid
and r.request_id = &request;

 

C) DETAILS OF ALL FND CONCURRENT QUEUES

set lines 132
 
col user_concurrent_queue_name format a30 heading 'QUEUE NAME'
col manager_type format a4 heading 'TYPE'
col batch_queue format 999 heading 'BATCH|QUEUE'
col tm_queue format 99 heading 'TM|QUEUE'
col running_processes format 99 heading 'RUN|JOB'
col max_processes format 99 heading 'MAX|JOB'
col min_processes format 99 heading 'MIN|JOB'
col target_processes format 99 heading 'TARGET|JOB'
col sleep_seconds format 999 heading 'SLEEP|SECS'
col sleep_fast format a1 heading 'F'
col cache_size format 9999 heading 'CACHE|SIZE'
rem
break on report
compute sum of batch_queue tm_queue running_processes max_processes on report
rem
select qt.user_concurrent_queue_name,
manager_type,
decode( manager_type, '1', running_processes ) batch_queue,
decode( manager_type, '3', running_processes ) tm_queue,
running_processes,
max_processes,
min_processes,
target_processes,
sleep_seconds,
decode( sleep_seconds, greatest( 30, sleep_seconds ), null, '*' ) sleep_fast,
cache_size
from applsys.fnd_concurrent_queues_tl qt,
applsys.fnd_concurrent_queues q
where q.application_id = qt.application_id
and q.concurrent_queue_id = qt.concurrent_queue_id
and userenv('lang') = qt.language
order by qt.user_concurrent_queue_name;

D) WHAT IS CURRENTLY RUNNING FOR ALL CONCURRENT MANAGER QUEUES

set lines 132
 
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;

 

 

[Post Views: 2134]

Brijesh Gogia

I’m an experienced Oracle Applications DBA with more than a decade of full-time DBA experience. I have gained a wide knowledge of the Oracle software stack and have worked on several big projects for multi-national companies. I enjoy working with the leading-edge technology and have passion for database performance and stability. Thankfully my work allows me time for researching new technologies (and to write about them).
  
You can connect with me on LinkedIn.

2 Comments

  1. RAJUKR RAJUKR

    Excellent Site and with Awesome info and with detailed steps. Thanks a lot for sharing this info with all of US.

    -Raju

  2. Nabi Nabi

    Superb…Very simple to understand yet very..very useful

Leave a Reply

Required fields are marked *