Long Running Concurrent Request

Everynow and then you will get asked to look at a concurrent request which seems to be taking to long or is 'stuck'. This could be a job scheduled to run frequently or sometimes during a large month end job like a payroll run.

First thing to do is get the SID for the concurrent request and then use this to find if any SQL is running

-- GET THE CURRENT SQL STATEMENT RUNNING FOR A CONCURRENT REQUEST

SELECT A.REQUEST_ID, D.SID, D.SERIAL#, D.OSUSER, D.PROCESS, C.SPID, E.SQL_TEXT

FROM APPS.FND_CONCURRENT_REQUESTS A, APPS.FND_CONCURRENT_PROCESSES B, V$PROCESS C, V$SESSION D, V$SQL E

WHERE A.CONTROLLING_MANAGER = B.CONCURRENT_PROCESS_ID   

AND C.PID = B.ORACLE_PROCESS_ID   

AND B.SESSION_ID = D.AUDSID   

AND D.SQL_ADDRESS = E.ADDRESS

AND A.REQUEST_ID = &REQUEST_ID;

 

SELECT sql_text
FROM v$session a, v$sqltext b
WHERE a.sql_address = b.address
AND a.sql_hash_value = b.hash_value
AND a.sid = &SID
ORDER BY b.piece;

 

--SQL STATEMENTS RUNNING BY A USER , FIND OUT WHO 

SELECT A.SID, A.SERIAL#, B.SQL_TEXT F

FROM V$SESSION A, V$SQLAREA B

WHERE A.SQL_ADDRESS = B.ADDRESS

AND A.USERNAME = 'APPS';

 

-- GET THE BLOCKING SESSIONS FOR GIVEN SID

SELECT BLOCKING_SESSION, SID, SERIAL#, WAIT_CLASS, SECONDS_IN_WAIT FROM V$SESSION

WHERE BLOCKING_SESSION IS NOT NULL

AND SID = &SID

ORDER BY BLOCKING_SESSION;

 

Original SQL for this was found at :

http://erpschools.com/scripts/get-sql-for-a-concurrent-request-id