Monday, August 12, 2013

SQL to identify the query being executed by the concurrent program currently

How do you identify which SQL is running behind the concurrent program at a particular instant?
   
The SQL given below lists out the request id, SID, concurrent program name and the concurrent program start time currently being executed in the instance.

  SELECT fcr.request_id,
                  
vs.SID,
                  
fcpt.user_concurrent_program_name,
         TO_CHAR (fcr.actual_start_date,
                  'mm/dd/rrrr hh24:mi:ss') time_started
    FROM fnd_concurrent_requests fcr,

                  
fnd_concurrent_programs_tl fcpt,
                  
v$session vs,
                  
v$process vp
   WHERE fcpt.concurrent_program_id = fcr.concurrent_program_id

     AND fcpt.application_id = fcr.program_application_id
     AND fcpt.LANGUAGE 'US'
     AND phase_code 'R'
     AND status_code 'R'
     AND vs.audsid = oracle_session_id
     AND vp.addr = vs.paddr

ORDER BY actual_start_date;



Get the SID for the concurrent program for which you want to see the SQL currently being executed and pass it to the SQL below.

  SELECT sql_text
    FROM SYS.v_$sqltext
   WHERE hash_value = (SELECT sql_hash_value
                         FROM SYS.v_$session
                        WHERE SID = &v_sid)

ORDER BY piece;

No comments:

Post a Comment