Display Oracle concurrent manager processes

Oracle Database Tips by Donald Burleson

This articles describes tuning techniques for the Oracle Concurrent Manager, but you can use this easy script to display the status of an Oracle concurrent manager:

select
concurrent_queue_name,
running_processes

from
   applsys.fnd_concurrent_queues
where
   concurrent_queue_name = '&myqueuename'
and
   running_processes > $mythreshold;

Also, this script by Colin Lowe claims to show the concurrent manager job status:

set linesize 178
set verify off
SET VERIFY   OFF

COLUMN request_id        FORMAT 999999999 HEADING 'Request|ID';
COLUMN Req_Date          FORMAT        a20 HEADING 'Req|Date';
COLUMN oracle_process_id FORMAT    9999999 HEADING 'PID';
COLUMN session_id        FORMAT    9999999 HEADING 'Session ID';
COLUMN oracle_id         FORMAT    9999999 HEADING 'Oracle ID';
COLUMN os_process_id     FORMAT        a10 HEADING 'OS PID';
COLUMN requested_by      FORMAT    9999999 HEADING 'Requested By';
COLUMN phase_code        FORMAT         a6 HEADING "Phase|Code"
COLUMN status_code       FORMAT         a6 HEADING "Status|Code"
COLUMN completion_text   FORMAT        a35 HEADING 'Text';
COLUMN user_id           FORMAT    9999999 HEADING 'User ID';
COLUMN user_name         FORMAT        a10 HEADING 'User|Name';
COLUMN Req_Date          FORMAT        a20 HEADING 'Req|Date';

SELECT
    a.request_id
, to_char(a.REQUEST_DATE,'DD-MON-YYYY HH24:MI:SS') Req_Date
, b.user_name
, a.phase_code
, a.status_code
, c.os_process_id
, a.oracle_id
, a.requested_by
, a.completion_text
FROM
    applsys.fnd_concurrent_requests a
, applsys.fnd_user b
, applsys.fnd_concurrent_processes c
WHERE
      a.requested_by = b.user_id
AND c.concurrent_process_id = a.controlling_manager
AND a.phase_code in ('R', 'T')
ORDER BY
a.request_id, c.os_process_id
/

set linesize 178
set verify off

COLUMN start_time FORMAT a18
COLUMN program_name FORMAT a50
COLUMN reqid FORMAT 999999999 HEADING 'Request|ID'
COLUMN tot_mins FORMAT 9999999 HeaDING "Total|Run-Time|in Mins"
COLUMN hrs FORMAT 99999 HEADING "Running|Hrs"
COLUMN mins FORMAT 99999 HEADING "Running|Mins"
COLUMN secs FORMAT 99999 HEADING "Running|Secs"
COLUMN user_name FORMAT a12
COLUMN sid FORMAT 99999
COLUMN serial# FORMAT 9999999
COLUMN phase FORMAT a5 HEADING "Phase|Code"
COLUMN status FORMAT a6 HEADING "Status|Code"

select r.request_id reqid,
to_char(r.actual_start_date, 'DD-MON-YY HH24:MI:SS') start_time,
u.user_name,
r.phase_code phase,
r.status_code status,
floor(((SYSDATE - r.actual_start_date)*24*60*60)/3600) hrs,
floor((((SYSDATE - r.actual_start_date)*24*60*60) - floor(((SYSDATE - r.actual_start_date)*24*60*60)/3600)*3600)/60) mins,
round((((SYSDATE - r.actual_start_date)*24*60*60) - floor(((SYSDATE - r.actual_start_date)*24*60*60)/3600)*3600 - (floor((((SYSDATE - r.actual_start_date)*24*60*60) - floor(((SYSDATE - r.actual_start_date)*24*60*60)/3600)*3600)/60)*60) )) secs,
(SYSDATE - r.actual_start_date)*24*60 Tot_Mins,
/* p.concurrent_program_id progid,*/
decode(p.user_concurrent_program_name,
       'Request Set Stage', 'RSS - '||r.description,
       'Report Set', 'RS - '||r.description,
       p.user_concurrent_program_name ) program_name,
       s.sid, s.serial#
from   v$session s,
       apps.fnd_user u,
       apps.fnd_concurrent_processes pr,
       apps.fnd_concurrent_programs_vl p,
       apps.fnd_concurrent_requests r
where s.process = pr.os_process_id
and pr.concurrent_process_id = r.controlling_manager
and r.phase_code = 'R' -- and r.status_code = 'R'
and r.requested_by = u.user_id
and p.concurrent_program_id = r.concurrent_program_id
order by 1
/

column start_time clear
column program_name clear
column reqid clear
column user_name clear
column sid clear
column serial# clear

! cat ~/scripts/conc_manager_user_query.lst

exit;

echo "Running conc_manager_user_query.sql"
echo
echo "" >> ~/scripts/conc_manager_user_query.lst
echo "" >> ~/scripts/conc_manager_user_query.lst
echo "The phase_code and its meaning for fnd_concurrent_requests" >> ~/scripts/conc_manager_user_query.lst
echo "LOOKUP_CODE                    MEANING" >> ~/scripts/conc_manager_user_query.lst
echo "---------------------------------------------------------------------------" >> ~/scripts/conc_manager_user_query.lst
echo "C                              Completed" >> ~/scripts/conc_manager_user_query.lst
echo "I                              Inactive" >> ~/scripts/conc_manager_user_query.lst
echo "P                              Pending" >> ~/scripts/conc_manager_user_query.lst
echo "R                              Running" >> ~/scripts/conc_manager_user_query.lst
echo "" >> ~/scripts/conc_manager_user_query.lst

echo "The status_code and its meaning for fnd_concurrent_requests" >> ~/scripts/conc_manager_user_query.lst
echo "LOOKUP_CODE                    MEANING" >> ~/scripts/conc_manager_user_query.lst
echo "---------------------------------------------------------------------------" >> ~/scripts/conc_manager_user_query.lst
echo "A                              Waiting" >> ~/scripts/conc_manager_user_query.lst
echo "B                              Resuming" >> ~/scripts/conc_manager_user_query.lst
echo "C                              Normal" >> ~/scripts/conc_manager_user_query.lst
echo "D                              Cancelled ">> ~/scripts/conc_manager_user_query.lst
echo "E                              Error" >> ~/scripts/conc_manager_user_query.lst
echo "F                              Scheduled" >> ~/scripts/conc_manager_user_query.lst
echo "G                              Warning" >> ~/scripts/conc_manager_user_query.lst
echo "H                              On Hold" >> ~/scripts/conc_manager_user_query.lst
echo "I                              Normal" >> ~/scripts/conc_manager_user_query.lst
echo "M                              No Manager">> ~/scripts/conc_manager_user_query.lst
echo "Q                              Standby" >> ~/scripts/conc_manager_user_query.lst
echo "R                              Normal" >> ~/scripts/conc_manager_user_query.lst
echo "S                              Suspended" >> ~/scripts/conc_manager_user_query.lst
echo "T                              Terminating">> ~/scripts/conc_manager_user_query.lst
echo "U                              Disabled" >> ~/scripts/conc_manager_user_query.lst
echo "W                              Paused" >> ~/scripts/conc_manager_user_query.lst
echo "X                              Terminated" >> ~/scripts/conc_manager_user_query.lst
echo "Z                              Waiting" >> ~/scripts/conc_manager_user_query.lst
echo "" >> ~/scripts/conc_manager_user_query.lst

echo "The status_code and its meaning for fnd_concurrent_processes" >> ~/scripts/conc_manager_user_query.lst
echo "LOOKUP_CODE                    MEANING" >> ~/scripts/conc_manager_user_query.lst
echo "---------------------------------------------------------------------------" >> ~/scripts/conc_manager_user_query.lst
echo "A                              Active" >> ~/scripts/conc_manager_user_query.lst
echo "G                              Awaiting Discovery" >> ~/scripts/conc_manager_user_query.lst
echo "C                              Connecting ">> ~/scripts/conc_manager_user_query.lst
echo "S                              Deactivated " >> ~/scripts/conc_manager_user_query.lst
echo "D                              Deactiviating" >> ~/scripts/conc_manager_user_query.lst
echo "Z                              Initializing" >> ~/scripts/conc_manager_user_query.lst
echo "M                              Migrating" >> ~/scripts/conc_manager_user_query.lst
echo "R                              Running" >> ~/scripts/conc_manager_user_query.lst
echo "P                              Suspended" >> ~/scripts/conc_manager_user_query.lst
echo "K                              Terminated" >> ~/scripts/conc_manager_user_query.lst
echo "T                              Terminating" >> ~/scripts/conc_manager_user_query.lst
echo "" >> ~/scripts/conc_manager_user_query.lst

cat ~/scripts/conc_manager_user_query.lst

exit;

��

Display Oracle concurrent manager processes Oracle Database Tips by Donald Burleson

Display Oracle concurrent manager processes

Oracle Database Tips by Donald Burleson