Query to find long-run concurrent jobs

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;