Long Running Concurrent requests
1.) Get the list of long running concurrent request from below
queries .
Long Running concurrent request -1
SELECT fcr.oracle_session_id
,fcr.request_id rqst_id
,fcr.requested_by rqst_by
,fu.user_name
,fr.responsibility_name
,fcr.concurrent_program_id cp_id
,fcp.user_concurrent_program_name cp_name
,TO_CHAR (fcr.actual_start_date, 'DD-MON-YYYY HH24:MI:SS')act_start_datetime
,DECODE (fcr.status_code, 'R', 'R:Running', fcr.status_code) status
,ROUND (((SYSDATE - fcr.actual_start_date) * 60 * 24), 2) runtime_min
,ROUND (((SYSDATE - fcr.actual_start_date) * 60 * 60 * 24), 2)runtime_sec
,fcr.oracle_process_id "oracle_pid/SPID"
,fcr.os_process_id os_pid
,fcr.argument_text
,fcr.outfile_name
,fcr.logfile_name
,fcr.enable_trace
FROM
apps.fnd_concurrent_requests fcr
,apps.fnd_user fu
,apps.fnd_responsibility_tl fr
,apps.fnd_concurrent_programs_tl fcp
WHERE
fcr.status_code LIKE 'R'
AND
fu.user_id = fcr.requested_by
AND
fr.responsibility_id = fcr.responsibility_id
AND
fcr.concurrent_program_id = fcp.concurrent_program_id
AND
fcr.program_application_id = fcp.application_id
AND ROUND
(((SYSDATE - fcr.actual_start_date) * 60 * 24), 2) > 1
ORDER BY
fcr.concurrent_program_id
,request_id DESC;
Long Running concurrent
request -1 ( more than 30 minutes )
select r.request_id
"Request ID",
round((sysdate-r.actual_start_date)*1440,0) "Runtime(Mins)",
p.concurrent_program_name "Conc Program Short Name",
p.user_concurrent_program_name "Concurrent Program Name",
to_char(r.requested_start_date, 'DD-MON-YYYY HH24:MI:SS') "Requested
Start Date",
to_char(r.actual_start_date, 'DD-MON-YYYY HH24:MI:SS') "Actual Start
Date",
TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') "System Date",
u.user_name,
u.description,
argument_text,
s.sid,
r.os_process_id,
r.oracle_process_id
from
applsys.fnd_concurrent_requests r,
apps.fnd_concurrent_programs_vl p,
v$session s,
applsys.fnd_user u
where
r.program_application_id = p.application_id
and
r.concurrent_program_id = p.concurrent_program_id
and
r.requested_by = u.user_id
and r.phase_code
= 'R'
and
r.status_code = 'R'
and
to_char(r.os_process_id) = s.process(+)
and s.action(+)
= 'Concurrent Request'
and
p.concurrent_class_id is null
and round((sysdate-r.actual_start_date)*1440,0)
> 30
order by
"Runtime(Mins)" desc
/
---------------------------
2.) SQL
will give you all request ID’s related to Request
-- Get the run history of the request
SELECT
f.request_id,
pt.user_concurrent_program_name user_conc_program_name,
f.actual_start_date start_on,
f.actual_completion_date end_on,
FLOOR (
( (f.actual_completion_date -
f.actual_start_date)
* 24
* 60
* 60)
/ 3600)
|| ' HOURS '
|| FLOOR (
( ( (f.actual_completion_date -
f.actual_start_date)
* 24
* 60
* 60)
- FLOOR (
(
(f.actual_completion_date - f.actual_start_date)
* 24
* 60
* 60)
/ 3600)
* 3600)
/ 60)
|| ' MINUTES '
|| ROUND (
( ( (f.actual_completion_date -
f.actual_start_date)
* 24
* 60
* 60)
- FLOOR (
(
(f.actual_completion_date - f.actual_start_date)
* 24
* 60
* 60)
/ 3600)
* 3600
- ( FLOOR (
( (
(f.actual_completion_date - f.actual_start_date)
*
24
*
60
*
60)
-
FLOOR (
( ( f.actual_completion_date
- f.actual_start_date)
* 24
* 60
* 60)
/ 3600)
*
3600)
/ 60)
* 60)))
|| ' SECS '
time_difference,
p.concurrent_program_name concurrent_program_name,
DECODE (f.phase_code,
'R', 'Running',
'C', 'Complete',
f.phase_code)
Phase,
f.status_code,
f.Argument_text
FROM
apps.fnd_concurrent_programs p,
apps.fnd_concurrent_programs_tl pt,
apps.fnd_concurrent_requests f
WHERE
f.concurrent_program_id = p.concurrent_program_id
AND f.program_application_id = p.application_id
AND f.concurrent_program_id = pt.concurrent_program_id
AND f.program_application_id = pt.application_id
AND pt.USER_CONCURRENT_PROGRAM_NAME like '%LOG%'
AND pt.language = USERENV ('Lang')
AND f.actual_start_date IS NOT NULL
ORDER BY f.actual_start_date
DESC;
------------------------------
3.)-- Need
request id to find out child requests
--
Find all child requests for a request set (OR if no children, just get details
on an individual request)
--
REQUIRED VALUE - Enter the Request ID that launched the Request set being
investigated
select /*+ ORDERED USE_NL(x
fcr fcp fcptl)*/
fcr.request_id "Request ID",
fcr.requested_by
"User",
substr(DECODE
(FCR.DESCRIPTION, NULL,
FCPTL.USER_CONCURRENT_PROGRAM_NAME,
FCR.DESCRIPTION||'
('||FCPTL.USER_CONCURRENT_PROGRAM_NAME||')'),1,80)"Program Name",
(fcr.actual_completion_date
- fcr.actual_start_date)*1440 "Elapsed Time",
oracle_process_id
"Trace File ID" ,
fcr.phase_code "Phase",
fcr.status_code
"Status",
to_char(fcr.request_date,'DD-MON-YYYY
HH24:MI:SS') "Submitted",
(fcr.actual_start_date
- fcr.request_date)*1440 "Delay",
to_char(fcr.actual_start_date,'DD-MON-YYYY
HH24:MI:SS') "Start Time",
to_char(fcr.actual_completion_date,
'DD-MON-YYYY HH24:MI:SS') "End Time",
fcr.argument_text
"Parameters"
from (select /*+ index (fcr1 FND_CONCURRENT_REQUESTS_N3) */
fcr1.request_id
from
fnd_concurrent_requests fcr1
where 1=1
start with fcr1.request_id = &parent_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 1;
--------------------------------
4.) SQL
Need request id to find out SQLID
SELECT a.request_id,
d.sid,
d.serial#,
d.status,
d.osuser,
d.process
FROM
apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE
a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id = d.audsid
AND a.request_id = &Request_ID
AND a.phase_code = 'R';
************
SELECT fcr.REQUEST_ID,
fcr.PHASE_CODE,
fcr.STATUS_CODE,
fcr.OS_PROCESS_ID,
vs.sid,
fcr.USER_CONCURRENT_PROGRAM_NAME,
vs.SQL_ID
FROM
apps.fnd_conc_requests_form_v fcr, gv$session vs
WHERE
fcr.REQUEST_ID = '128335339'
---fcr.USER_CONCURRENT_PROGRAM_NAME
= 'Workflow Background Process'
AND fcr.OS_PROCESS_ID = VS.PROCESS;
