Thursday, January 9, 2014

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 toher purposes.

No comments:

Post a Comment