concurrent.sql

set lines 200 pages 50000
col USER_CONCURRENT_QUEUE_NAME for a60
col TARGET_NODE for a12
select fcqt.user_concurrent_queue_name USER_CONCURRENT_QUEUE_NAME,
       fcq.target_node TARGET_NODE,
       fcq.max_processes TARGET_PROCESSES,
       fcq.running_processes RUNNING_PROCESSES,
       nvl(r.running_process, 0) RUNNING_PROCESS,
       nvl(p.pending_process, 0) PENDING_PROCESS
  from apps.fnd_concurrent_processors fcp,
       apps.FND_CONCURRENT_QUEUES fcq,
       apps.FND_CONCURRENT_QUEUES_TL fcqt,
       (select fcwr1.Concurrent_Queue_Name,
               count(fcwr1.REQUEST_ID) as pending_process
          from apps.Fnd_Concurrent_Worker_Requests fcwr1
         where fcwr1.PHASE_CODE = 'P'
           and fcwr1.hold_flag != 'Y'
           and fcwr1.REQUESTED_START_DATE <= sysdate
         group by fcwr1.Concurrent_Queue_Name) p,
       (select fcwr2.Concurrent_Queue_Name,
               count(fcwr2.REQUEST_ID) as running_process
          from apps.Fnd_Concurrent_Worker_Requests fcwr2
         where fcwr2.PHASE_CODE = 'R'
           and fcwr2.hold_flag != 'Y'
           and fcwr2.REQUESTED_START_DATE <= sysdate
         group by fcwr2.Concurrent_Queue_Name) R
 where fcq.concurrent_queue_id = fcqt.concurrent_queue_id
   and fcq.concurrent_processor_id = fcp.concurrent_processor_id
   and fcqt.language = 'ZHS'
   and fcqt.concurrent_queue_name = p.Concurrent_Queue_Name(+)
   and fcq.enabled_flag = 'Y'
   and nvl(fcq.control_code, '999') <> 'E'
   and fcqt.concurrent_queue_name = R.Concurrent_Queue_Name(+)
   and (fcq.target_processes <> 0 or fcq.Concurrent_Queue_Name = 'FNDICM' or
       fcq.Concurrent_Queue_Name like 'FNDSM%' or
       fcq.Concurrent_Queue_Name like 'FNDIM%');

set lines 200 pages 50000
select ses.sql_id,count(*)
from gv$session ses,gv$process pro
where ses.paddr=pro.addr
and ses.inst_id=pro.inst_id
and pro.spid in
(select oracle_process_id from applsys.fnd_concurrent_requests
where request_id in (select request_id from cuxsysmoniter.cux_fnd_request_running_info_v))
group by ses.sql_id
order by 2;

set lines 200 pages 9999
select INST_ID, SID, TYPE, ID1, ID2, LMODE, REQUEST, CTIME, BLOCK,
DECODE (BLOCK, 0, '', 'blocker') blocker,
DECODE (request, 0, '', 'waiter') waiter
from gv$lock where (ID1,ID2,TYPE) in
(select ID1,ID2,TYPE from gv$lock where request>0)
order by blocker,ctime desc;