Long running concurrent request in R12.2:

 Before proceeding further Collect following basis level of information to development team:

1 Oracle seeded program/Custom program?

2 How much time it used earlier?

3 Is there any recent code change done in concurrent program?

4 Is this program fetching higher data compare to last run?

5 Does this job running any specific time/ It can be run any time?

6 Does this job fetching data using DB link?

7 Does the problem happen on both the test and production instance?

meanwhile we need to start basic sanity check in our environment.

Verify the status of the concurrent program:

select REQUEST_ID,phase_code,status_code,ORACLE_SESSION_ID from apps.fnd_concurrent_requests where request_id=’1234567’;

Find which concurrent manger ran a specific concurrent request:

select b.USER_CONCURRENT_QUEUE_NAME from fnd_concurrent_processes a,fnd_concurrent_queues_vl b, fnd_concurrent_requests c where a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID and a.CONCURRENT_PROCESS_ID = c.controlling_manager and c.request_id ='856272604';

Verify the actual and target of the CM:

Verify the concurrent manager status

Find SID for the concurrent request use below query:

set lines 200

set pages 200

col USERNAME for a10

col MODULE for a25

col ACTION for a25

col PROGRAM for a18

SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID,d.EVENT FROM apps.fnd_concurrent_requests a, apps.fnd_concurrent_processes b, v$process c, v$session d WHERE a.controlling_manager = b.concurrent_process_id AND c.pid = b.oracle_process_id AND b.session_id=d.audsid AND a.request_id = &Request_ID AND a.phase_code = 'R';

Find any blocking session for concurrent request use below query and verify concurrent program SID:

select (select username from v$session where sid=a.sid) blocker, a.sid, ' is blocking ' "IS BLOCKING", (select username from v$session where sid=b.sid) blockee,b.sid from v$lock a, v$lock b where a.block = 1 and b.request > 0 and a.id1 = b.id1 and a.id2 = b.id2;

If any blocking session there use below query to find the concurrent request id 

SELECT s.inst_id,a.request_id,s.sid,s.serial#,c.spid FROM apps.fnd_concurrent_requests a, gv$process c, gv$session s WHERE s.sid in ('&sid') AND s.paddr = c.addr AND a.oracle_process_id = c.spid AND a.phase_code = UPPER ('R');

Find client identifier and module, action for concurrent request

Then check with module owner with the concurrent request

select sid,serial#,USERNAME,CLIENT_IDENTIFIER,status,LAST_CALL_ET/60,SQL_HASH_VALUE,module,action,program from v$session where sid='&n';

Find which sql query is running 

select sql_text from v$sql where hash_value='12345678';

Find stale value of the tables which is used by the concurrent program:

select table_name, stale_stats, last_analyzed from dba_tab_statistics where stale_stats='YES';

You may have to run the gather stats against those tables which are having stale value.

Use OEM and monitor session ID

If development team ask tkprof,AWR,ASH report please generate and analysis.