Concurrent Request Queries
Concurrent Request Queries
HISTORY OF CONCURRENT REQUEST (PROGRAM WISE)
SELECT DISTINCT t.user_concurrent_program_name,
r.request_id,
To_char(r.actual_start_date, ‘dd-mm-yy hh24:mi:ss’)
“Started at”,
To_char(r.actual_completion_date, ‘dd-mm-yy hh24:mi:ss’)
“Completed at”,
Decode(r.phase_code, ‘C’, ‘Completed’,
‘I’, ‘Inactive’,
‘P ‘, ‘Pending’,
‘R’, ‘Running’,
‘NA’)
phasecode,
Decode(r.status_code, ‘A’, ‘Waiting’,
‘B’, ‘Resuming’,
‘C’, ‘Normal’,
‘D’, ‘Cancelled’,
‘E’, ‘Error’,
‘F’, ‘Scheduled’,
‘G’, ‘Warning’,
‘H’, ‘On Hold’,
‘I’, ‘Normal’,
‘M’, ‘No Manager’,
‘Q’, ‘Standby’,
‘R’, ‘Normal’,
‘S’, ‘Suspended’,
‘T’, ‘Terminating’,
‘U’, ‘Disabled’,
‘W’, ‘Paused’,
‘X’, ‘Terminated’,
‘Z’, ‘Waiting’)
“Status”,
r.argument_text
“Parameters”,
Substr(u.description, 1, 25)
“Who submitted”,
Round(( ( Nvl(v.actual_completion_date, SYSDATE) – v.actual_start_date ) * 24 * 60 )) Etime
FROM apps.fnd_concurrent_requests r,
apps.fnd_concurrent_programs p,
apps.fnd_concurrent_programs_tl t,
apps.fnd_user u,
apps.fnd_conc_req_summary_v v
WHERE r.concurrent_program_id = p.concurrent_program_id
AND r.actual_start_date >= ( SYSDATE – 30 )
–AND r.requested_by=32126
AND r.program_application_id = p.application_id
AND t.concurrent_program_id = r.concurrent_program_id
AND r.requested_by = u.user_id
AND v.request_id = r.request_id
–AND r.request_id =’8840058′ in (‘35645887′,’45824565’)
AND t.user_concurrent_program_name LIKE ‘%%’
ORDER BY To_char(r.actual_completion_date, ‘dd-mm-yy hh24:mi:ss’);
Requests Completion With Date Details
SELECT request_id,
To_char(request_date, ‘DD-MON-YYYY HH24:MI:SS’) request_date,
To_char(requested_start_date, ‘DD-MON-YYYY HH24:MI:SS’)
requested_start_date,
To_char(actual_start_date, ‘DD-MON-YYYY HH24:MI:SS’)
actual_start_date,
To_char(actual_completion_date, ‘DD-MON-YYYY HH24:MI:SS’)
actual_completion_date,
To_char(SYSDATE, ‘DD-MON-YYYY HH24:MI:SS’) current_date,
Round(( Nvl(actual_completion_date, SYSDATE) – actual_start_date ) * 24,
2)
duration
FROM fnd_concurrent_requests
WHERE request_id = To_number(‘&p_request_id’);
Fetch Request id from SID
SELECT a.request_id,
a.phase_code,
a.status_code,
d.sid AS Oracle_SID,
d.serial#,
d.osuser,
d.process,
c.spid AS OS_Process_ID
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.phase_code = ‘R’
AND a.status_code = ‘R’
AND d.sid = &sid;
Determine Which Manager Ran a Specific Concurrent Request
SELECT b.user_concurrent_queue_name
FROM fnd_concurrent_processes a,
fnd_concurrent_queues_vl b,
fnd_concurrent_requests c
WHERE a.concurrent_queue_id = b.concurrent_queue_id
AND a.concurrent_process_id = c.controlling_manager
AND c.request_id = ‘&Request_id’;
Concurrent request status for a given sid?
SELECT s.inst_id,
s.sid,
s.serial#,
p.spid os_pid,
s.status,
s.osuser,
s.username,
s.machine,
s.MODULE,
s.schemaname,
s.action
FROM gv$session s,
gv$process p
WHERE s.paddr = p.addr
AND s.sid = ‘&oracle_sid’;
Find out request id from Oracle_Process Id
SELECT request_id,
oracle_process_id,
os_process_id
FROM apps.fnd_concurrent_requests
WHERE oracle_process_id = ‘&Process_ID’;
Find Concurrent Program name,phase code,status code for a given Request ID
SELECT request_id,
user_concurrent_program_name,
Decode(phase_code, ‘C’, ‘Completed’,
phase_code) phase_code,
Decode(status_code, ‘D’, ‘Cancelled’,
‘E’, ‘Error’,
‘G’, ‘Warning’,
‘H’, ‘On Hold’,
‘T’, ‘Terminating’,
‘M’, ‘No Manager’,
‘X’, ‘Terminated’,
‘C’, ‘Normal’,
status_code) status_code,
To_char(actual_start_date, ‘dd-mon-yy:hh24:mi:ss’) Start_Date,
To_char(actual_completion_date, ‘dd-mon-yy:hh24:mi:ss’),
completion_text
FROM apps.fnd_conc_req_summary_v
WHERE request_id = ‘&Request_ID’
ORDER BY 6 DESC;
Find the SQL Query for a given Concurrent Request through SID
SELECT sid,
sql_text
FROM gv$session ses,
gv$sqlarea SQL
WHERE ses.sql_hash_value = SQL.hash_value(+)
AND ses.sql_address = SQL.address(+)
AND ses.sid = ‘&oracle_SID’ ;
Find Child Requests for Parent Request ID
SELECT SUM.request_id,
req.parent_request_id,
SUM.user_concurrent_program_name,
Decode(SUM.phase_code, ‘C’, ‘Completed’,
SUM.phase_code) phase_code,
Decode(SUM.status_code, ‘D’, ‘Cancelled’,
‘E’, ‘Error’,
‘G’, ‘Warning’,
‘H’, ‘On Hold’,
‘T’, ‘Terminating’,
‘M’, ‘No Manager’,
‘X’, ‘Terminated’,
‘C’, ‘Normal’,
SUM.status_code) status_code,
SUM.actual_start_date,
SUM.actual_completion_date,
SUM.completion_text
FROM apps.fnd_conc_req_summary_v SUM,
apps.fnd_concurrent_requests req
WHERE req.request_id = SUM.request_id
AND req.parent_request_id = ‘&Parent_Request_ID’;
SELECT has_sub_request,
is_sub_request,
parent_request_id,
oracle_process_id,
oracle_session_id,
os_process_id
FROM fnd_concurrent_requests
WHERE request_id = ‘&Req_ID’; –Enter ID from Above Query Result
Cancelling Concurrent request From Backend
–By request id
UPDATE fnd_concurrent_requests
SET status_code = ‘D’,
phase_code = ‘C’
WHERE request_id = &req_id;
–By program_id
UPDATE fnd_concurrent_requests
SET status_code = ‘D’,
phase_code = ‘C’
WHERE concurrent_program_id = &prg_id;
Terminate the all concurrent requests Module wise
SELECT ‘ALTER SYSTEM KILL SESSION ”’
||sid
||‘,’
||serial#
||”’ immediate;’
FROM gv$session
WHERE MODULE LIKE ‘GLPREV’;
–This will create an script for terminating all the concurrent request.
History of concurrent requests which are error out
SELECT a.request_id “Req Id”,
a.phase_code,
a.status_code,
actual_start_date,
actual_completion_date,
c.concurrent_program_name
|| ‘: ‘
|| ctl.user_concurrent_program_name “program”
FROM applsys.fnd_concurrent_requests a,
applsys.fnd_concurrent_processes b,
applsys.fnd_concurrent_queues q,
applsys.fnd_concurrent_programs c,
applsys.fnd_concurrent_programs_tl ctl
WHERE a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND a.status_code = ‘E’
AND a.phase_code = ‘C’
AND actual_start_date > SYSDATE – 2
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND ctl.LANGUAGE = ‘US’
ORDER BY 5 DESC;
Find out Concurrent Program which enable with trace
SELECT A.concurrent_program_name “Program_Name”,
Substr(A.user_concurrent_program_name, 1, 40) “User_Program_Name”,
Substr(B.user_name, 1, 15) “Last_Updated_By”,
Substr(B.description, 1, 25) DESCRIPTION
FROM apps.fnd_concurrent_programs_vl A,
applsys.fnd_user B
WHERE A.enable_trace = ‘Y’
AND A.last_updated_by = B.user_id;
Concurrent Program count under QUEUE
SELECT user_concurrent_program_name “PROGRAM NAME”,
concurrent_queue_name “QUEUE NAME”,
priority,
Decode(phase_code, ‘P’, ‘Pending’) “PHASE”,
Decode(status_code, ‘A’, ‘Waiting’,
‘B’, ‘Resuming’,
‘C’, ‘Normal’,
‘D’, ‘Cancelled’,
‘E’, ‘Error’,
‘F’, ‘Scheduled’,
‘G’, ‘Warning’,
‘H’, ‘On Hold’,
‘I’, ‘Normal’,
‘M’, ‘No Manager’,
‘Q’, ‘Standby’,
‘R’, ‘Normal’,
‘S’, ‘Suspended’,
‘T’, ‘Terminating’,
‘U’, ‘Disabled’,
‘W’, ‘Paused’,
‘X’, ‘Terminated’,
‘Z’, ‘Waiting’) ” NAME”,
status_code,
Count(*)
FROM fnd_concurrent_worker_requests
WHERE phase_code = ‘P’
AND hold_flag != ‘Y’
AND requested_start_date <= SYSDATE
AND concurrent_queue_name <> ‘FNDCRM’
AND concurrent_queue_name <> ‘GEMSPS’
GROUP BY user_concurrent_program_name,
concurrent_queue_name,
priority,
phase_code,
status_code
ORDER BY Count(*) DESC
Lists the Concurrent Manager Names with the No. of Requests in Pending/Running
SELECT a.user_concurrent_queue_name,
a.max_processes,
SUM(Decode(b.phase_code, ‘P’, Decode(b.status_code, ‘Q’, 1,
0),
0)) Pending_Standby,
SUM(Decode(b.phase_code, ‘P’, Decode(b.status_code, ‘I’, 1,
0),
0)) Pending_Normal,
SUM(Decode(b.phase_code, ‘R’, Decode(b.status_code, ‘R’, 1,
0),
0)) Running_Normal
FROM fnd_concurrent_queues_vl a,
fnd_concurrent_worker_requests b
WHERE a.concurrent_queue_id = b.concurrent_queue_id
AND b.requested_start_date <= SYSDATE
GROUP BY a.user_concurrent_queue_name,
a.max_processes;