The concurrent request was hold on by whom
SQL> select REQUEST_ID,To_char(LAST_UPDATE_DATE,'DD-MON-YYYY=>hh24:mi:ss') LAST_UPDATE_DATE,LAST_UPDATED_BY,REQUEST_DATE,REQUESTED_BY,PROGRAM_APPLICATION_ID,CONCURRENT_PROGRAM_ID,LAST_UPDATE_LOGIN from fnd_concurrent_requests where REQUEST_ID='&reqid';
SQL> select USER_ID,USER_NAME from fnd_user where LAST_UPDATED_BY=9852;
script to find out NLS patches applied or nor
SELECT driver.driver_file_name
,TO_CHAR(run.start_date,'DD-Mon HH24:MI:SS') start_date
,TO_CHAR(run.end_date ,'DD-Mon HH24:MI:SS') end_date
,lang.language
FROM ad_patch_runs run
,ad_patch_driver_langs lang
,ad_patch_drivers driver
,ad_applied_patches applied
WHERE run.patch_driver_id = driver.patch_driver_id
AND driver.applied_patch_id = applied.applied_patch_id
AND applied.patch_name = '&p_patch_number'
AND lang.patch_driver_id = driver.patch_driver_id
ORDER BY 1,2,3;
Running concurrent request
set lines 132
col os form A7 head AppProc
col spid form a6 head DBProc
col program form A43 trunc
set pages 38
col time form 9999.99 head Elapsed
col "Req Id" form 9999999
col "Parent" form a7
col "Prg Id" form 999999
col qname head "Concurrent Manager Queue" format a25 trunc
col sid format 99999 head SID
set recsep off
select q.concurrent_queue_name || ' - ' || target_node qname
,a.request_id "Req Id"
,decode(a.parent_request_id,-1,NULL,a.parent_request_id) "Parent"
,a.concurrent_program_id "Prg Id"
,a.phase_code,a.status_code
,nvl(a.os_process_id,b.os_process_id) "OS"
,vs.sid
,vp.spid
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 "Time"
,c.concurrent_program_name||' - '||
c2.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_tl c2
,APPLSYS.fnd_concurrent_programs c
,gv$session vs
,gv$process vp
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 c2.concurrent_program_id = c.concurrent_program_id
and a.phase_code in ('I','P','R','T')
and b.queue_application_id = q.application_id
and b.concurrent_queue_id = q.concurrent_queue_id
and c2.language = 'US'
and vs.process (+) = b.os_process_id
and vs.paddr = vp.addr (+)
and vs.inst_id = vp.inst_id
order by 1,2
/
concurrent request status
col os form A6
col program form A40
set pages 38
set verify off
col time head Elapsed form 9999.99
col "Req Id" form 9999999
col "Prg Id" form 999999
col "Started On" format a10
col "Finished On" format a10
col "Submitted By" format a30 trunc
col argument_text head "Arguments" format a40
col statustxt head Status format a10 trunc
col phasetxt head Phase format a10 trunc
set recsep off
accept cmreqid number prompt 'What is the concurrent request id : '
select l2.meaning phasetxt
,l1.meaning statustxt
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 "Time"
,to_char(a.actual_start_date,'mm/dd/yy hh:mi:ssAM') "Started On"
,to_char(a.actual_completion_date,'mm/dd/yy hh:mi:ssAM') "Finished On"
,u.user_name || ' - ' || u.description "Submitted By"
,a.argument_text
from APPLSYS.fnd_Concurrent_requests a
,applsys.fnd_user u
,applsys.fnd_lookup_values l1
,applsys.fnd_lookup_values l2
where u.user_id = a.requested_by
and a.request_id = &cmreqid
and l1.lookup_type = 'CP_STATUS_CODE'
and l1.lookup_code = a.status_code
and l1.language = 'US'
and l1.enabled_flag = 'Y'
and (l1.start_date_active <= sysdate and l1.start_date_active is not null)
and (l1.end_date_active > sysdate or l1.end_date_active is null)
and l2.lookup_type = 'CP_PHASE_CODE'
and l2.lookup_code = a.phase_code
and l2.language = 'US'
and l2.enabled_flag = 'Y'
and (l2.start_date_active <= sysdate and l2.start_date_active is not null)
and (l2.end_date_active > sysdate or l2.end_date_active is null)
/
query to find the details of long running and wating requests details
select description, request_date, requested_start_date, actual_start_date ,
actual_completion_date,request_id , cprogram, argument_text ,user_name,phase_code,status_code
,to_number(substr(run_time,9,2))*24+to_number(substr(run_time,12,2))||'.'||(decode(length(substr(run_time,15,2)),1,'0'||substr(run_time,15,2),substr(run_time,15,2))) RUN_HHMI_NEW
,to_number(substr(wait_time,9,2))*24+to_number(substr(wait_time,12,2))||'.'||(decode(length(substr(wait_time,15,2)),1,'0'||substr(wait_time,15,2),substr(wait_time,15,2))) WAIT_HHMI_NEW
from (
select
p.user_concurrent_program_name description, r.request_date, r.requested_start_date, r.actual_start_date ,
r.actual_completion_date,r.request_id,
p.concurrent_program_name cprogram,r.argument_text ,r.cancel_or_hold, fu.user_name,lup1.meaning phase_code,lup2.meaning status_code,
rtrim((cast(r.actual_completion_date as timestamp) - cast (r.actual_start_date as timestamp)),'000000') run_time,
rtrim((cast(r.actual_start_date as timestamp) - cast (r.request_date as timestamp)),'000000') wait_time
from apps.fnd_concurrent_queues fcq,
apps.fnd_concurrent_queue_content fcqc,
apps.fnd_concurrent_request_class c,
apps.fnd_application f,
apps.fnd_concurrent_programs_vl p,
apps.fnd_concurrent_requests r,
apps.fnd_user fu,
apps.fnd_lookups lup1,
apps.fnd_lookups lup2
where r.program_application_id = p.application_id
and r.concurrent_program_id = p.concurrent_program_id
and r.phase_code <> 'P'
and r.requested_by = fu.user_id
and trunc(request_date) between :P_FDATE and :P_TDATE
and p.application_id = f.application_id
and r.program_application_id = f.application_id
and r.request_class_application_id = c.application_id(+)
and r.concurrent_request_class_id = c.request_class_id(+)
and r.request_class_application_id = fcqc.type_application_id(+)
and r.concurrent_request_class_id = fcqc.type_id(+)
and fcqc.queue_application_id = fcq.application_id(+)
and fcqc.concurrent_queue_id = fcq.concurrent_queue_id(+)
and lup1.lookup_code = r.phase_code
and lup1.lookup_type = 'CP_PHASE_CODE'
and lup2.lookup_code = r.status_code
and lup2.lookup_type = 'CP_STATUS_CODE'
group by
c.request_class_name,
f.application_short_name,
p.concurrent_program_name,lup1.meaning ,lup2.meaning ,
p.user_concurrent_program_name,r.request_date,r.requested_start_date,
r.priority,r.actual_start_date ,r.request_id,r.actual_completion_date,fu.user_name,
r.argument_text,r.cancel_or_hold
order by 6 )
most executed concurrent requests day wise with node details
select request_id,logfile_node_name, p.user_concurrent_program_name "CONCURRENT PROGRAM NAME",
p.concurrent_program_name "PROGRAM",request_date
from apps.fnd_concurrent_queues fcq,
apps.fnd_concurrent_queue_content fcqc,
apps.fnd_concurrent_request_class c,
apps.fnd_application f,
apps.fnd_concurrent_programs_vl p,
apps.fnd_concurrent_requests r
where r.program_application_id = p.application_id
and r.concurrent_program_id = p.concurrent_program_id
--and r.status_code in ('C','G','E')
and trunc(request_date) between :P_FDATE and :P_TDATE
and p.application_id = f.application_id
and r.program_application_id = f.application_id
and r.request_class_application_id = c.application_id(+)
and r.concurrent_request_class_id = c.request_class_id(+)
and r.request_class_application_id = fcqc.type_application_id(+)
and r.concurrent_request_class_id = fcqc.type_id(+)
and fcqc.queue_application_id = fcq.application_id(+)
and fcqc.concurrent_queue_id = fcq.concurrent_queue_id(+)
--and p.user_concurrent_program_name like '%Purge%Manager%'
group by
request_id,c.request_class_name,
f.application_short_name,
p.concurrent_program_name,
p.user_concurrent_program_name,
r.priority, request_date,logfile_node_name
order by 3
most executed concurrent requests
select
p.user_concurrent_program_name "description",
p.concurrent_program_name "program",
count(*) "NUMS"
from apps.fnd_concurrent_queues fcq,
apps.fnd_concurrent_queue_content fcqc,
apps.fnd_concurrent_request_class c,
apps.fnd_application f,
apps.fnd_concurrent_programs_vl p,
apps.fnd_concurrent_requests r
where r.program_application_id = p.application_id
and r.concurrent_program_id = p.concurrent_program_id
--and r.status_code in ('C','G','E')
and trunc(request_date) between :P_FDATE and :P_TDATE
and p.application_id = f.application_id
and r.program_application_id = f.application_id
and r.request_class_application_id = c.application_id(+)
and r.concurrent_request_class_id = c.request_class_id(+)
and r.request_class_application_id = fcqc.type_application_id(+)
and r.concurrent_request_class_id = fcqc.type_id(+)
and fcqc.queue_application_id = fcq.application_id(+)
and fcqc.concurrent_queue_id = fcq.concurrent_queue_id(+)
group by
c.request_class_name,
f.application_short_name,
p.concurrent_program_name,
p.user_concurrent_program_name,
r.priority
order by count(*) desc
most executed concurrent requests day wise
select p.user_concurrent_program_name "CONCURRENT PROGRAM NAME",
p.concurrent_program_name "PROGRAM",to_char(request_date,'DD-MON-YYYY') "DATE",
count(*) "NUMS"
from apps.fnd_concurrent_queues fcq,
apps.fnd_concurrent_queue_content fcqc,
apps.fnd_concurrent_request_class c,
apps.fnd_application f,
apps.fnd_concurrent_programs_vl p,
apps.fnd_concurrent_requests r
where r.program_application_id = p.application_id
and r.concurrent_program_id = p.concurrent_program_id
--and r.status_code in ('C','G','E')
and trunc(request_date) between :P_FDATE and :P_TDATE
and p.application_id = f.application_id
and r.program_application_id = f.application_id
and r.request_class_application_id = c.application_id(+)
and r.concurrent_request_class_id = c.request_class_id(+)
and r.request_class_application_id = fcqc.type_application_id(+)
and r.concurrent_request_class_id = fcqc.type_id(+)
and fcqc.queue_application_id = fcq.application_id(+)
and fcqc.concurrent_queue_id = fcq.concurrent_queue_id(+)
group by
c.request_class_name,
f.application_short_name,
p.concurrent_program_name,
p.user_concurrent_program_name,
r.priority, to_char(request_date,'DD-MON-YYYY')
order by 3, count(*) desc
number_of requests submitted per day
select to_char(request_date,'DD-MON-YYYY') "DATE", count(request_date) "NUM_REQS"
from fnd_concurrent_requests
where trunc(request_date) between :P_FDATE and :P_TDATE
group by to_char(request_date,'DD-MON-YYYY') order by 1
query to find the number of requests submitted previous day
select to_char(sysdate-1,'DD-MON-YYYY') "DATE", count(request_date) "NUM_REQS"
from fnd_concurrent_requests
where trunc(request_date)=trunc(sysdate-1);
