How to find all the concurrent managers status from sql prompt?
To find this I need to launch oracle application forms which almost takes 5minutes for me. So I was thinking a way which gives the info quicker and also without launching forms.
Below is the query somehow I found somewhere and I want to share this with you all.
To find this I need to launch oracle application forms which almost takes 5minutes for me. So I was thinking a way which gives the info quicker and also without launching forms.
Below is the query somehow I found somewhere and I want to share this with you all.
select q.concurrent_queue_name || ' - ' || target_node qname,
q.running_processes actual,
q.max_processes target,
sum(decode(r.phase_code,'R',1,0)) - sum(decode(r.status_code,'W',1,0)) running,
sum(decode(r.phase_code,'P',1,0)) pending,
nvl(sum(decode(r.status_code,'W',1,'H',1,'S',1,'A',1,'Z',1,0)),0) paused,
nvl(sum(decode(r.status_code,'F',1,'Q',1,'B',1,'T',0)),0) influx,
avg((nvl(r.actual_start_date,r.requested_start_date) - r.requested_start_date)*1440) avgqtime
from applsys.fnd_concurrent_requests r,
applsys.fnd_concurrent_processes p,
applsys.fnd_concurrent_queues q
where r.controlling_manager (+) = p.concurrent_process_id
and p.queue_application_id = q.application_id
and p.concurrent_queue_id = q.concurrent_queue_id
and q.max_processes > 0
group by q.concurrent_queue_name || ' - ' || target_node, q.running_processes, q.max_processes;
Activated Concurrent Queue Actual Target Running Pending Paused InFlux AvgQtime
----------------------------------- ------- ------- -------- -------- -------- -------- ---------
INVMGR - DEVCM 3 3 0 0 0 0 .56
Alerts - DEVCM 4 4 0 0 0 0 248.44
PODAMGR - DEVCM 6 6 0 0 0 0
INVTMRPM - DEVCM 5 5 0 0 0 0
RCVOLTM - DEVCM 3 3 0 0 0 0
IEU_WL_CS - DEVCM 1 1 0 0 0 0
FNDICM - DEVCM 1 1 0 0 0 0
WFMLRSVC - DEVCM 1 1 0 0 0 0
Debug_Service - DEVCM 1 1 0 0 0 0
MRPMGR - DEVCM 1 1 0 1 0 1 -1307.38
WFALSNRSVC - DEVCM 1 1 0 0 0 0
OAMCOLMGR - DEVCM 1 1 0 0 0 0
IEXCONMGR - DEVCM 1 1 0 0 0 0
XDP_Q_EVENT_SVC - DEVCM 1 1 0 0 0 0
FNDCRM - DEVCM 1 1 0 0 0 0
XDP_MANAGER - DEVCM 2 2 0 0 0 0 279.66
FNDCPOPP - DEVCM 5 5 0 0 0 0
XDP_Q_IN_MSG_SVC - DEVCM 1 1 0 0 0 0
STANDARD - DEVCM 30 30 0 1 0 0 61.78
CEMLIQM - DEVCM 10 10 0 0 0 0 .17
IEU_SH_CS - DEVCM 1 1 0 0 0 0
WFWSSVC - DEVCM 1 1 0 0 0 0
PASMGR - DEVCM 1 1 0 0 0 0 -12.54
C_AQCT_SVC - DEVCM 1 1 0 0 0 0
FNDSM_DEVCM - DEVCM 1 1 0 0 0 0
------- ------- -------- --------
sum 84 84 0 2
