Find session details sid/pid from concurrent request id

Oracle Performance Analysis - Long running Concurrent Requests

The following query can be used to find the sid,pid, spid for a specific concurrent request. If a program is running for long time and user requests to verify the root cause, this will help DBA to find the sid and then dba can go beyond this and look for sqlid, plan etc. This will be helpful while doing performance analysis

SELECT a.request_id,

       d.sid,

       d.serial#,

       d.sql_id,

       d.osuser,

       d.process,

       d.client_identifier "requestor" ,

       c.SPID

  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';