Use below SQL statement to find who is running concurrent request and how long it has been running.
SQL> SELECT fcr.request_id req_id,
to_char(fcr.actual_start_date,'mm/dd hh24:mi') start_time,
TRUNC(((sysdate - fcr.actual_start_date)/(1/24))*60) elap_in_Mins,
substr(fcq.concurrent_queue_name, 1, 20) queue,
substr(fcp.user_concurrent_program_name, 1, 45) name,
substr(fu.user_name, 1, 9 ) u_name,
-- round((sysdate - actual_start_date) *24, 2) elap,
-- TRUNC(((sysdate - fcr.actual_start_date)/(1/24))*60) elap,
substr(decode( fcr.status_code, 'A', 'WAITING', 'B', 'RESUMING',
'C', 'NORMAL', 'D', 'CANCELLED', 'E', 'ERROR', 'F', 'SCHEDULED',
'G', 'WARNING', 'H', 'ON HOLD', 'I', 'NORMAL', 'M', 'NO MANAGER',
'Q', 'STANDBY', 'R', 'NORMAL', 'S', 'SUSPENDED', 'T', 'TERMINATED',
'U', 'DISABLED', 'W', 'PAUSED', 'X', 'TERMINATED', 'Z', 'WAITING',
'UNKNOWN'), 1, 10) status
FROM applsys.fnd_concurrent_queues fcq,
applsys.fnd_concurrent_processes fcp, applsys.fnd_user fu,
apps.fnd_concurrent_programs_vl fcp,
applsys.fnd_concurrent_requests fcr
WHERE fcp.concurrent_queue_id = fcq.concurrent_queue_id
and fcp.queue_application_id = fcq.application_id
and fcr.controlling_manager = fcp.concurrent_process_id
and fcr.requested_by = fu.user_id
and fcr.concurrent_program_id = fcp.concurrent_program_id
and fcr.program_application_id = fcp.application_id
-- and round((sysdate -fcr.actual_start_date) *24, 2) >= 1
-- TRUNC(((sysdate - fcr.actual_start_date)/(1/24))*60) > 5
and fcr.phase_code = 'R'
ORDER BY round((sysdate -actual_start_date) *24, 2) DESC, fu.user_name,
fcr.request_id, to_char(fcr.actual_start_date,'dd-mon-yy hh24:mi:ss'),
fcp.user_concurrent_program_name
;
References:
Doc ID 152209.1 (STATUS_CODE and PHASE_CODE Columns of FND_CONCURRENT_REQUESTS Table)
Useful queries:
- Find the OS process IDs of a concurrent job
SQL> SELECT b.sid, b.serial#, client_identifier, oracle_process_id DB_OS_PID, os_process_id EBS_PID
FROM fnd_concurrent_requests a , v$session b
WHERE a.ORACLE_SESSION_ID = b.AUDSID and
a.request_id=5510725;
- Check the performance trend on one concurrent program/job
SQL> SELECT
fcr.request_id request_id,
round(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) time_in_Minutes,
fcr.actual_start_date start_date,
status_code,
fu.user_name user_name,
fcpt.user_concurrent_program_name user_conc_prog,
fcr.argument_text parameters
FROM
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpt,
fnd_concurrent_requests fcr,
fnd_user fu
WHERE fcr.concurrent_program_id = fcp.concurrent_program_id
and fcr.program_application_id = fcp.application_id
and fcr.concurrent_program_id = fcpt.concurrent_program_id
and fcr.program_application_id = fcpt.application_id
and fcpt.language = USERENV('Lang')
and fcr.requested_by = fu.user_id and fcr.actual_start_date > sysdate - 30 -- within 30 days
and fcp.concurrent_program_name = 'FNDGSCST' -- Program name: on statistics
-- and fcr.argument_text like 'ALL%'
-- and status_code != 'E'
ORDER BY start_date desc;
- Check if parameter has been changed in defining a concurrent job
SELECT
fcr.request_id request_id,fcr.argument_text,
TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) time_in_mins,
fcr.actual_start_date start_date,
fu.user_name user_name,
fcpt.user_concurrent_program_name user_conc_prog
FROM
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpt,
fnd_concurrent_requests fcr,
fnd_user fu
WHERE fcr.concurrent_program_id = fcp.concurrent_program_id
and fcr.program_application_id = fcp.application_id
and fcr.concurrent_program_id = fcpt.concurrent_program_id
and fcr.program_application_id = fcpt.application_id
and fcpt.language = USERENV('Lang')
and fcr.requested_by = fu.user_id and fcr.actual_start_date > sysdate - 21 -- within 21 days
and fcp.concurrent_program_name = 'CSTRBICR5G' -- Program name: on BOM
ORDER BY start_date desc;
