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: 2148]
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.
You can connect with me on LinkedIn.
Latest posts by Brijesh Gogia (see all)
- Oracle Cloud Infrastructure (OCI) – 3 – Concept of Compartments - January 22, 2019
- Oracle Cloud Infrastructure (OCI) – 2 – Console - January 21, 2019
- Oracle Cloud Infrastructure (OCI) – 1 – Fundamentals - January 21, 2019
Excellent Site and with Awesome info and with detailed steps. Thanks a lot for sharing this info with all of US.
-Raju
Superb…Very simple to understand yet very..very useful