Syed Saad Ali

Oracle ACE Pro

Oracle Solution Architect

Oracle E-Business Suite

Oracle Cloud Infrastructure

Oracle Fusion Middleware

Oracle Database Administration

Oracle Weblogic Administration

Syed Saad Ali

Oracle ACE Pro

Oracle Solution Architect

Oracle E-Business Suite

Oracle Cloud Infrastructure

Oracle Fusion Middleware

Oracle Database Administration

Oracle Weblogic Administration

Blog Post

Concurrent Request Queries

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;

 

 

 

 

 

 

Related Posts
Write a comment