Oracle EBS Concurrent Request ID, OS PID and Database SID

Sometime when tracing performance problem we need to identify which application process is running. We can spot Operating System PID easily, using task manager or top command. But what or which application or modules is running errand and causing troubles? This is an opposite way from the my previous post which help us find out OS PID from concurrent request ID in Oracle EBS.

For concurrent requests we can find the relation between OS PID (of database server) and Oracle EBS concurrent request ID using the following SQL statement:

select * from fnd_concurrent_requests
where 1=1 and oracle_process_id=&OS_PID
and trunc(request_date)=trunc(sysdate)

If you also need oracle database process ID, use the following statement:

select request_id, p.spid,s.* from apps.fnd_concurrent_requests r , v$process p , v$session s
where r.request_id = 
(select request_id from fnd_concurrent_requests fcr where 1=1
and fcr.oracle_process_id=&OS_PID
and trunc(fcr.request_date)=trunc(sysdate))
and p.SPID = r.ORACLE_PROCESS_ID
and p.ADDR = s.PADDR

If you already know the Oracle database process ID and need to relate to Oracle Concurrent Request ID, use the following:

select f.request_id, v.spid,s.sid, s.username,s.serial#, s.osuser, s.status 
from v$process v, v$session s, applsys.fnd_concurrent_requests f 
where 1=1 
and s.paddr=v.addr
and f.oracle_process_id=v.spid
and trunc(f.request_date)=trunc(sysdate)
and sid=&SID