Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

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;


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational