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;

Oracle Forms Session Queries

(more…)