To Find Latest Query Executing/Running in Database for Specific Concurrent Request

Find Latest Query Executing/Running in Database for Specific Concurrent Request

If one concurrent request is running  and you want to find currently which query of that request is running in session then you can run below query in toad or sql developer by putting request ifd of that concurrent request-

select

   to_char(inst_id) rac_inst_id,

   to_char(sid) sid,

   to_char(serial#) serial#,

   sql_text

from

   apps.fnd_concurrent_requests fcr,

   gv$session vs,

   v$sqltext vq

where

   vs.process = fcr.os_process_id

   and vs.sql_address = vq.address

   and fcr.status_code = 'R'

   and fcr.phase_code = 'R'

   and request_id in

  (select fcr.request_id

  from (select /*+ index (fcr1 FND_CONCURRENT_REQUESTS_N3) */

          fcr1.request_id

       from fnd_concurrent_requests fcr1

         where 1=1

         start with fcr1.request_id = &request_id

          connect by prior fcr1.request_id = fcr1.parent_request_id) x,

          fnd_concurrent_requests fcr,

          fnd_concurrent_programs fcp,

          fnd_concurrent_programs_tl fcptl

          where fcr.request_id = x.request_id

          and fcr.concurrent_program_id = fcp.concurrent_program_id

          and fcr.program_application_id = fcp.application_id

          and fcp.application_id = fcptl.application_id

          and fcp.concurrent_program_id = fcptl.concurrent_program_id

          and fcptl.language = 'US')

Order By Serial#;

This will be very useful for debugging purposes means we can derive what part of code is taking time or where program got stuck and for other purposes.