Concurrent Manager Queries

/* Managers with their corresponding Oracle and System Process Id''s*/


SELECT DISTINCT concurrent_process_id cpid, gvs.inst_id INSTANCE,
gvp.pid opid, os_process_id osid,
q.user_concurrent_queue_name manager, p.node_name node,
TO_CHAR (p.process_start_date,
'MM-DD-YY HH:MI:SSAM'
) started_at
FROM fnd_concurrent_processes p,
fnd_concurrent_queues_vl q,
gv$process gvp,
gv$session gvs
WHERE q.application_id = queue_application_id
AND (q.concurrent_queue_id = p.concurrent_queue_id)
AND (gvs.process = os_process_id)
AND (gvp.addr = gvs.paddr)
AND process_status_code NOT IN ('K', 'S')
ORDER BY gvs.inst_id,
p.node_name,
concurrent_process_id,
q.user_concurrent_queue_name;


/*Managers that are having problems*/

SELECT DISTINCT concurrent_process_id cpid, oracle_process_id opid,
os_process_id osid, user_concurrent_queue_name manager,
p.node_name node,
TO_CHAR (p.process_start_date,
'MM-DD-YY HH:MI:SSAM'
) started_at
FROM fnd_concurrent_processes p, fnd_concurrent_queues_vl q
WHERE q.application_id = queue_application_id
AND q.concurrent_queue_id = p.concurrent_queue_id
AND p.concurrent_queue_id <> 1
AND (os_process_id) NOT IN (SELECT gvs.process
FROM gv$session gvs
WHERE gvs.process IS NOT NULL)
AND ( process_status_code = 'A'
OR process_status_code = 'R'
OR process_status_code = 'T'
)
ORDER BY os_process_id,
concurrent_process_id,
q.user_concurrent_queue_name;



/*Managers that are not active at this instant*/

SELECT application_name, concurrent_queue_id qid,
user_concurrent_queue_name manager, node_name node
FROM fnd_concurrent_queues_vl q, fnd_application_vl a
WHERE a.application_id = q.application_id
AND max_processes = 0
AND running_processes = 0
AND q.concurrent_queue_id <> 1
ORDER BY application_name, user_concurrent_queue_name;