======================================
select b.user_concurrent_program_name, cp.request_id, sc.MEANING,pc.MEANING ,
to_char(cp.actual_start_date,'dd-mon-yyyy hh24:mi'),
to_char(cp.actual_completion_date,'dd-mon-yyyy hh24:mi')
FROM apps.fnd_concurrent_requests cp ,apps.fnd_concurrent_programs_vl b, apps.fnd_lookups sc , apps.fnd_lookups pc
WHERE
cp.request_id between = ReqId and
cp.concurrent_program_id = b.concurrent_program_id and
b.application_id = cp.program_application_id and
sc.LOOKUP_TYPE = 'CP_STATUS_CODE' and sc.LOOKUP_CODE = cp.status_code
and
pc.LOOKUP_TYPE = 'CP_PHASE_CODE' and pc.LOOKUP_CODE = cp.phase_code
order by cp.request_id
Conc. Req. Detl's when Req.ID known
============================
select request_id,phase_code ,status_code ,oracle_process_id ,oracle_session_id ,
to_char( actual_start_date,'dd-mm-yyyy hh24:mi'),
to_char( actual_completion_date,'dd-mm-yyyy hh24:mi') ,
(actual_completion_date-actual_start_date) *24*60 MINS
from apps.fnd_concurrent_requests where
request_id in(
Conc. Req. Detl's History for Specific Programme
=====================================
select cp.oracle_process_id ,usr.USER_NAME,usr.EMAIL_ADDRESS,cp.request_id,cp.status_code,cp.phase_code ,to_char(cp.actual_start_date,'dd-mm-yyyy hh24:mi'),
to_char(cp.actual_completion_date,'dd-mm-yyyy hh24:mi')
FROM apps.fnd_concurrent_requests cp ,apps.fnd_concurrent_programs b ,apps.fnd_user usr
WHERE
lower(b.user_concurrent_program_name) like '%
trunc(cp.actual_start_date) >=trunc(sysdate-10) and
cp.concurrent_program_id = b.concurrent_program_id and
b.application_id = cp.program_application_id and
usr.USER_ID=cp.REQUESTED_BY;
Request which ran and which are scheduled
=================================
select b.user_concurrent_program_name, cp.oracle_process_id, cp.request_id, cp.status_code,cp.phase_code ,
to_char(cp.REQUESTED_START_DATE,'dd-mm-yyyy hh24:mi') Schedule_Date,to_char(cp.actual_start_date,'dd-mm-yyyy hh24:mi'),
to_char(cp.actual_completion_date,'dd-mm-yyyy hh24:mi')
FROM apps.fnd_concurrent_requests cp ,apps.fnd_concurrent_programs_vl b
WHERE
lower(b.user_concurrent_program_name) like '%
( trunc(cp.actual_start_date) >=trunc(sysdate-7) or cp.actual_start_date is null ) and
cp.concurrent_program_id = b.concurrent_program_id and
b.application_id = cp.program_application_id ;
Request and its Manager
==================
select r.request_id, pg.concurrent_program_name,r.os_process_id req_pid, pc.os_process_id mgr_pid,
q.concurrent_queue_name, pc.node_name node, r.logfile_name req_log, pc.logfile_name mgr_log from
apps.fnd_concurrent_requests r, apps.fnd_concurrent_programs pg, apps.fnd_concurrent_processes pc, apps.fnd_concurrent_queues q
where r.controlling_manager = pc.concurrent_process_id and
pc.queue_application_id = q.application_id and
pc.concurrent_queue_id = q.concurrent_queue_id and
r.program_application_id = pg.application_id and
r.concurrent_program_id = pg.concurrent_program_id
and r.request_id =
Long running requests
( Requests exceeding 240 Mins execution time and only run by STD Manager )
==============================
select q.concurrent_queue_name, pc.node_name node,b.user_concurrent_program_name, cp.request_id, cp.status_code,cp.phase_code ,
to_char(cp.actual_start_date,'dd-mon-yyyy hh24:mi'),
to_char(cp.actual_completion_date,'dd-mon-yyyy hh24:mi') ,
(cp.actual_completion_date-cp.actual_start_date) *24*60 MINS ,
(cp.actual_completion_date-cp.actual_start_date) *24 HRS
FROM apps.fnd_concurrent_requests cp ,apps.fnd_concurrent_programs_vl b,
apps.fnd_concurrent_processes pc, apps.fnd_concurrent_queues q
WHERE
cp.actual_start_date >= to_date(FromDate,'dd-mon-yy hh24:mi:ss') and
cp.actual_start_date <= to_date(ToDate,'dd-mon-yy hh24:mi:ss') and
cp.actual_completion_date >= to_date(FromDate,'dd-mon-yy hh24:mi:ss') and
cp.concurrent_program_id = b.concurrent_program_id and
b.application_id = cp.program_application_id and
q.concurrent_queue_name like '%STANDARD%' and
b.user_concurrent_program_name not in('Gather Schema Statistics','Gather Statistics for Non-Apps Schema') and
cp.status_code ='C' and cp.phase_code ='C' and
pc.queue_application_id = q.application_id and
pc.concurrent_queue_id = q.concurrent_queue_id and
cp.controlling_manager = pc.concurrent_process_id and
cp.program_application_id = b.application_id and
cp.concurrent_program_id = b.concurrent_program_id and
((cp.actual_completion_date-cp.actual_start_date) *24*60) >=240
order by 9 ;
Long running requests
( Requests which ran Max no of times )
==============================
select rpad(substr(b.user_concurrent_program_name,1,73),74,' ') ||count(1)
FROM apps.fnd_concurrent_requests cp ,apps.fnd_concurrent_programs_vl b,
apps.fnd_concurrent_processes pc, apps.fnd_concurrent_queues q
WHERE
cp.actual_start_date >= to_date(FromDate,'dd-mon-yy hh24:mi:ss') and
cp.actual_start_date <= to_date(ToDate,'dd-mon-yy hh24:mi:ss') and
cp.actual_completion_date >= to_date(FromDate,'dd-mon-yy hh24:mi:ss') and
and
cp.concurrent_program_id = b.concurrent_program_id and
b.application_id = cp.program_application_id and
q.concurrent_queue_name like '%STANDARD%' and
b.user_concurrent_program_name not in('Gather Schema Statistics','Gather Statistics for Non-Apps Schema') and
cp.status_code ='C' and cp.phase_code ='C' and
pc.queue_application_id = q.application_id and
pc.concurrent_queue_id = q.concurrent_queue_id and
cp.controlling_manager = pc.concurrent_process_id and
cp.program_application_id = b.application_id and
cp.concurrent_program_id = b.concurrent_program_id
group by b.user_concurrent_program_name having
count(1) >50 order by count(1);
Con Pgm and its executable
======================
select b.user_concurrent_program_name,a.EXECUTABLE_NAME,a.EXECUTION_FILE_PATH,a.EXECUTION_FILE_NAME from
apps.FND_EXECUTABLES_VL a, apps.fnd_concurrent_programs_vl b
where b.user_concurrent_program_name like '%MRP%' and b.EXECUTABLE_ID=a.EXECUTABLE_ID ;
Managers along with Status
======================
col user_concurrent_queue_name for a45
col node_name for a15
col node_name2 for a15
set linesize 150
select a.concurrent_queue_id id, b.user_concurrent_queue_name,
a.enabled_flag, a.node_name, a.node_name2 , MEANING, a.TARGET_NODE Current_Node
from apps.fnd_concurrent_queues a, apps.fnd_concurrent_queues_tl b ,apps.fnd_lookups c where
a.concurrent_queue_id=b.concurrent_queue_id
and b.language='US' and
a.control_code=c.LOOKUP_CODE and
c.LOOKUP_TYPE like 'CP_CONTROL_CODE';
Conc PGM Library Name
===================
select fcprocs.CONCURRENT_PROCESSOR_NAME Library, b.concurrent_queue_name,b.user_concurrent_queue_name,
a.enabled_flag, a.node_name, a.node_name2
from apps.fnd_concurrent_queues a, apps.fnd_concurrent_queues_tl b , apps.FND_CONCURRENT_PROCESSORS fcprocs where
a.concurrent_queue_id=b.concurrent_queue_id
and b.language='US' and
b.user_concurrent_queue_name like 'Standard Manager' and
fcprocs.CONCURRENT_PROCESSOR_ID = a.CONCURRENT_PROCESSOR_ID
Application Name : Manager along with its application
=========================================
column CONCURRENT_QUEUE_NAME format a15
column USER_CONCURRENT_QUEUE_NAME format a20
column LIBRARY a15
column LIBRARY format a15
column APPLICATION format a10
column APPLICATIONNAME format a30
select b.concurrent_queue_name,b.user_concurrent_queue_name,
fcprocs.CONCURRENT_PROCESSOR_NAME Library,
fappv.APPLICATION_SHORT_NAME Application, fappv.APPLICATION_NAME ApplicationName
from apps.fnd_concurrent_queues a, apps.fnd_concurrent_queues_tl b ,
apps.fnd_application fapp,apps.fnd_application_vl fappv ,
apps.FND_CONCURRENT_PROCESSORS fcprocs
where
a.concurrent_queue_id=b.concurrent_queue_id
and b.language='US' and
b.user_concurrent_queue_name like 'Standard Manager' and
fapp.APPLICATION_ID=a.APPLICATION_ID and
fappv.APPLICATION_ID=fapp.APPLICATION_ID and
fcprocs.CONCURRENT_PROCESSOR_ID = a.CONCURRENT_PROCESSOR_ID
Manager Types
============
LOOKUP_TYPE LOOKUP_CODE MEANING
---------------------------------------------------------
CP_MANAGER_TYPE 0 Internal Concurrent Manager
CP_MANAGER_TYPE 1 Concurrent Manager
CP_MANAGER_TYPE 2 Internal Monitor
CP_MANAGER_TYPE 3 Transaction Manager
CP_MANAGER_TYPE 4 Conflict Resolution Manager
CP_MANAGER_TYPE 5 Scheduler/Prereleaser Manager
CP_MANAGER_TYPE 6 Service Manager