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