Oracle Apps DBA Useful concurrent request queries.
###############################################################
Find out All Concurrent Queue/Manager sizes/Processes?
###########################################################
set lines 200 pages 300
select a.concurrent_queue_name,b.min_processes,b.max_processes from apps.fnd_concurrent_queues a,apps.fnd_concurrent_queue_size b where a.concurrent_queue_id=b.concurrent_queue_id;
-----------------------------------------------------------
select fcq.application_id,
fcq.concurrent_queue_name,
fcq.user_concurrent_queue_name,
ftp.application_id,
ftp.concurrent_time_period_name,
fa.application_short_name,
ftp.description,
fcqs.min_processes,
fcqs.max_processes,
fcqs.sleep_seconds,
fcqs.service_parameters
from apps.fnd_concurrent_queues_vl fcq,
apps.fnd_concurrent_queue_size fcqs,
apps.fnd_concurrent_time_periods ftp,
apps.fnd_application fa
where fcq.application_id = fcqs.queue_application_id
and fcq.concurrent_queue_id = fcqs.concurrent_queue_id
and fcqs.period_application_id = ftp.application_id
and fcqs.concurrent_time_period_id = ftp.concurrent_time_period_id
and ftp.application_id = fa.application_id;
###############################################################
To see all the pending / Running requests per each manager wise
###############################################################
SELECT request_id, phase_code, status_code, user_name,
user_concurrent_queue_name
FROM apps.fnd_concurrent_worker_requests cwr,
apps.fnd_concurrent_queues_tl cq,
apps.fnd_user fu
WHERE (cwr.phase_code = 'P' OR cwr.phase_code = 'R')
AND cwr.hold_flag != 'Y'
AND cwr.requested_start_date <= SYSDATE
AND cwr.concurrent_queue_id = cq.concurrent_queue_id
AND cwr.queue_application_id = cq.application_id
AND cq.LANGUAGE = 'US'
AND cwr.requested_by = fu.user_id
ORDER BY 5
Note: The same information can be seen in Administer Concurrent Manager form for each manager.
#############################################################################
Query to find the Concurrent Requests which are incompatible with Request
##############################################################################3
--input: Concurrent program name
SELECT fcpt.user_concurrent_program_name Input_Program,
(SELECT fcpt1.user_concurrent_program_name Incompatible_Programs
FROM fnd_concurrent_programs_tl fcpt1
WHERE fcpt1.concurrent_program_id in (fcps.to_run_concurrent_program_id)
AND rownum = 1) Incompatible_Programs
FROM fnd_concurrent_program_serial fcps
,fnd_concurrent_programs_tl fcpt
WHERE fcps.running_concurrent_program_id = fcpt.concurrent_program_id
AND fcpt.user_concurrent_program_name like '%'||:Program_Name||'%'
################################################################
Concurrent Program enabled with trace
#########################################################
col User_Program_Name for a40
col Last_Updated_By for a30
col DESCRIPTION for a30
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;
#######################################
sid
############################
SELECT B.SID, B.SERIAL#, B.INST_ID, B.EVENT, B.BLOCKING_SESSION, C.SQL_ID, C.PLAN_HASH_VALUE,C.module
FROM APPS.fnd_concurrent_requests A
,GV$SESSION B
,GV$SQLAREA C
WHERE A.oracle_session_id = B.audsid
-- AND a.phase_code = 'R'
AND B.sql_hash_value = C.hash_value
AND A.request_id in ('606963','606965','606964','606966','606967','606969','606970','606968','606971');
####################################################################
Request submitted by User
######################################################################
SELECT
user_concurrent_program_name,
request_date,
request_id,
phase_code,
status_code
FROM
fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcp,
fnd_responsibility_tl fr,
fnd_user fu
WHERE
fcr.CONCURRENT_PROGRAM_ID = fcp.concurrent_program_id
and fcr.responsibility_id = fr.responsibility_id
and fcr.requested_by = fu.user_id
and user_name = '&user'
AND actual_start_date > sysdate - 1
ORDER BY REQUEST_DATE Asc;
##########################################################################
Find out request id from Oracle_Process Id
###############################################################################
select REQUEST_ID,ORACLE_PROCESS_ID,OS_PROCESS_Id from fnd_concurrent_requests where
ORACLE_PROCESS_ID='&a';
#######################################################################
find Concurrent Request using SID
######################################################################
SELECT a.request_id,
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 d.sid = &SID;
###########################################################
##list of running requests
###################################
SELECT a.request_id
,a.oracle_process_id "SPID"
,frt.responsibility_name
,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name
,a.description
,a.ARGUMENT_TEXT
,b.node_name
,b.db_instance
,a.logfile_name
,a.logfile_node_name
,a.outfile_name
,q.concurrent_queue_name
,a.phase_code,a.status_code, a.completion_text
, actual_start_date
, actual_completion_date
, fu.user_name
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 mins
,(SELECT avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440 avg_run_time
FROM APPLSYS.fnd_Concurrent_requests a2,
APPLSYS.fnd_concurrent_programs c2
WHERE c2.concurrent_program_id = c.concurrent_program_id
AND a2.concurrent_program_id = c2.concurrent_program_id
AND a2.program_application_id = c2.application_id
AND a2.phase_code || '' = 'C') avg_mins
,round((actual_completion_date - requested_start_date),2) * 24 duration_in_hours
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
,apps.fnd_user fu
,apps.FND_RESPONSIBILITY_TL frt
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.phase_code = 'R'
AND a.status_code = 'R'
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 a.requested_by = fu.user_id
AND a.responsibility_id = frt.responsibility_id
ORDER BY a.actual_start_date DESC;
##############################################################
Concurrent program submitted with values and varibales
##############################################################
set lines 20000
col PROGRAM for a70
set pagesize 100
col ARGUMENT_TEXT for a50
select a.REQUEST_ID,b.program,a.ARGUMENT_TEXT,decode(a.status_code,'R','Normal','I','Normal','Z','Waiting','D','Cancelled','U','Disabled','E','Error','M','NoManager','C','Normal','H','On-Hold','W','Paused','B','Resuming','P','Scheduled','Q','Standby','S','Suspended','X','Terminated','T','Terminating','A','Waiting','G','Warning') Status,
decode(a.phase_code,'P','Pending','R','Running','C','Completed','I','Inactive') Phase,
to_char(b.ACTUAL_START_DATE,'dd/mm/yy hh24:mi:ss') "Start Time",
to_char(b.ACTUAL_COMPLETION_DATE,'dd/mm/yy hh24:mi:ss') "End Time" ,3600*(b.ACTUAL_COMPLETION_DATE-b.ACTUAL_START_DATE) "Execution Time"
from apps.fnd_concurrent_requests a,apps.FND_CONC_REQ_SUMMARY_V b where a.request_id=b.request_id and A.request_id in ('608397') ;
##################################################
Find out session details of a concurrent Request?
###################################################
set lines 200 pages 300
col USER_CONCURRENT_PROGRAM_NAME for a40
select USER_CONCURRENT_PROGRAM_NAME,c.request_id,s.sid,p.spid,s.process,s.osuser,s.username,s.program,s.status,logon_time,last_call_et
from v$session s, v$process p, apps.fnd_concurrent_requests c,apps.fnd_concurrent_programs_tl ct
where oracle_process_id=p.spid
and s.paddr=p.addr and
ct.concurrent_program_id=c.concurrent_program_id
and request_id=&creq_id;
#######################################################
Current running sql text for given concurrent request.
#######################################################
SELECT A.REQUEST_ID, D.SID, D.SERIAL#, D.OSUSER, D.PROCESS, C.SPID,
E.SQL_TEXT
FROM APPS.FND_CONCURRENT_REQUESTS A,
APPS.FND_CONCURRENT_PROCESSES B,
V$PROCESS C,
V$SESSION D,
V$SQL E
WHERE A.CONTROLLING_MANAGER = B.CONCURRENT_PROCESS_ID
AND C.PID = B.ORACLE_PROCESS_ID
AND B.SESSION_ID = D.AUDSID
AND D.SQL_ADDRESS = E.ADDRESS
AND A.REQUEST_ID = 760781;
########################################################
To find child requests for parent id
########################################################
set lines 200
col USER_CONCURRENT_PROGRAM_NAME for a40
col PHASE_CODE for a10
col STATUS_CODE for a10
col COMPLETION_TEXT for a20
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_concurrent_request_id';
########################################################
To find sid,serial# for a given concurrent request id?
##########################################################
set lines 200
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_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.request_id = &Request_ID
AND a.phase_code = 'R';
########################################################
To find the sql query for a given concurrent request sid?
#########################################################
select s.sid, s.serial#,s.inst_id, s.program,p.spid from gv$session s , gv$process p where p.addr =s.paddr and s.sid in ('1192');
#######################################################
Sql text for the request id doing in the backend.
#######################################################
Based on the SPID associated to each running request, query the v$session orv$session_longops table to see what is the request id doing in the backend.
SELECT b.sid, b.serial#, a.spid, b.program, b.osuser, b.machine,
b.TYPE, b.event, b.action, b.p1text, b.p2text, b.p3text, b.state, c.sql_text,b.logon_time
FROM v$process a, v$session b, v$sqltext c
WHERE a.addr=b.paddr
AND b.sql_hash_value = c.hash_value
AND b.STATUS = 'ACTIVE'
AND a.spid = '11696'
ORDER BY a.spid, c.piece;
-----------------------------------------------
SELECT A.REQUEST_ID, D.SID, D.SERIAL#, D.OSUSER, D.PROCESS, C.SPID,
E.SQL_TEXT
FROM APPS.FND_CONCURRENT_REQUESTS A,
APPS.FND_CONCURRENT_PROCESSES B,
V$PROCESS C,
V$SESSION D,
V$SQL E
WHERE A.CONTROLLING_MANAGER = B.CONCURRENT_PROCESS_ID
AND C.PID = B.ORACLE_PROCESS_ID
AND B.SESSION_ID = D.AUDSID
AND D.SQL_ADDRESS = E.ADDRESS
AND A.REQUEST_ID = 740473;
#########################################################################3
To find child requests for parent id
#############################################################################3
set lines 200
col USER_CONCURRENT_PROGRAM_NAME for a40
col PHASE_CODE for a10
col STATUS_CODE for a10
col COMPLETION_TEXT for a20
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_concurrent_request_id';
##############################################################################
Check run time of concurrent program of specific date or for some days duration.
##############################################################################
SELECT /*+ rule */
rq.parent_request_id "Parent Req. ID",
rq.request_id "Req. ID",
tl.user_concurrent_program_name "Program Name",
rq.actual_start_date "Start Date",
rq.actual_completion_date "Completion Date",
ROUND((rq.actual_completion_date -
rq.actual_start_date) * 1440, 2) "Runtime (in Minutes)"
FROM applsys.fnd_concurrent_programs_tl tl,
applsys.fnd_concurrent_requests rq
WHERE tl.application_id = rq.program_application_id
AND tl.concurrent_program_id = rq.concurrent_program_id
AND tl.LANGUAGE = USERENV('LANG')
AND rq.actual_start_date IS NOT NULL
AND rq.actual_completion_date IS NOT NULL
AND tl.user_concurrent_program_name = 'Autoinvoice Import Program' -- <change it>
-- AND TRUNC(rq.actual_start_date) = '&start_date' -- uncomment this for a specific date
ORDER BY rq.request_id DESC;
#######################################################
Checking which manager is going to execute a program
#############################################################
The below query identifies the manager which will be executing a given program. This query is based on the specialization rules set for the managers.
SELECT user_concurrent_program_name, user_concurrent_queue_name
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_queue_content cqc,
apps.fnd_concurrent_queues_tl cq
WHERE cqc.type_application_id(+) = cp.application_id
AND cqc.type_id(+) = cp.concurrent_program_id
AND cqc.type_code(+) = 'P'
AND cqc.include_flag(+) = 'I'
AND cp.LANGUAGE = 'US'
AND cp.user_concurrent_program_name = '&USER_CONCURRENT_PROGRAM_NAME' AND NVL (cqc.concurrent_queue_id, 0) = cq.concurrent_queue_id
AND NVL (cqc.queue_application_id, 0) = cq.application_id
AND cq.LANGUAGE = 'US';
###########################################################
CPU might be more if request submitted by end date user
Requests submitted by end-dated users using the query below
############################################################
select fcr.request_id
, fcpt.user_concurrent_program_name
, fu.user_name submitted_by
, to_char(fcr.requested_start_date,'DD-MON-YYYY HH24:MM:SS') start_date
, 'Every '|| decode(length(fcrc.class_info),39,fcrc.class_info,
substr(fcrc.class_info,1,instr(fcrc.class_info,':',1)-1)||' '||
decode(substr(fcrc.class_info,instr(fcrc.class_info,':',1)+1,1)
, 'N','Minute(s) '
, 'D','Day(s) '
, 'H','Hour(s) '
, 'M','Month(s) ')|| 'after '||
decode(substr(fcrc.class_info,instr(fcrc.class_info,':',1,2)+1,1)
, 'S'
, 'Start '
, 'C'
, 'Completion ')|| 'of prior request' ) scheduled_as
, nvl(to_char(fcrc.end_date_active,'DD-MON-YYYY'),'forever') ending_on
, fu.end_date "USER'S END DATE"
from fnd_concurrent_requests fcr
, fnd_concurrent_programs_tl fcpt
, fnd_user fu
, fnd_conc_release_classes fcrc
where fcr.phase_code = 'P'
and fcr.status_code in ('I','Q')
and fcr.program_application_id = fcpt.application_id
and fcr.concurrent_program_id = fcpt.concurrent_program_id
and fcpt.language = 'US'
and fcr.requested_by = fu.user_id
and fcr.release_class_app_id = fcrc.application_id
and fcr.release_class_id = fcrc.release_class_id
and fu.end_date <= sysdate
order by fcr.requested_start_date desc;
####################################################################################################################################################3
Check the values for the "Concurrent:Sequential Requests", "Concurrent:Active Request Limit" and "Concurrent:Hold Requests (CONC_HOLD)" profile options.
Concurrent:Active Request Limit (CONC_REQUEST_LIMIT)
You can limit the number of requests that may be run simultaneously by each user or for every user at a site. If you do not specify a limit, no limit is imposed.
Concurrent:Sequential Requests (CONC_SINGLE_THREAD)
You can force your requests to run one at a time (sequentially) according to the requests' start dates and times, or allow them to run concurrently, when their programs are compatible.
A value of "Yes" prevents your requests from running concurrently. Requests run sequentially in the order they are submitted.
A value of "No" means your requests can run concurrently when their concurrent programs are compatible.
Concurrent:Hold Requests (CONC_HOLD)
You can automatically place your concurrent requests on hold when you submit them.
The default is "No". The concurrent managers run your requests according to the priority and start time specified for each.
"Yes" means your concurrent requests and reports are automatically placed on hold.
################################################################################################################################################
select fcr.request_id
, fcpt.user_concurrent_program_name
, fu.user_name submitted_by
, to_char(fcr.requested_start_date,'DD-MON-YYYY HH24:MM:SS') start_date
, 'Every '|| decode(length(fcrc.class_info),39,fcrc.class_info,
substr(fcrc.class_info,1,instr(fcrc.class_info,':',1)-1)||' '||
decode(substr(fcrc.class_info,instr(fcrc.class_info,':',1)+1,1)
, 'N','Minute(s) '
, 'D','Day(s) '
, 'H','Hour(s) '
, 'M','Month(s) ')|| 'after '||
decode(substr(fcrc.class_info,instr(fcrc.class_info,':',1,2)+1,1)
, 'S'
, 'Start '
, 'C'
, 'Completion ')|| 'of prior request' ) scheduled_as
, nvl(to_char(fcrc.end_date_active,'DD-MON-YYYY'),'forever') ending_on
, fu.end_date "USER'S END DATE"
from fnd_concurrent_requests fcr
, fnd_concurrent_programs_tl fcpt
, fnd_user fu
, fnd_conc_release_classes fcrc
where fcr.phase_code = 'P'
and fcr.status_code in ('I','Q')
and fcr.program_application_id = fcpt.application_id
and fcr.concurrent_program_id = fcpt.concurrent_program_id
and fcpt.language = 'US'
and fcr.requested_by = fu.user_id
and fcr.release_class_app_id = fcrc.application_id
and fcr.release_class_id = fcrc.release_class_id
and fu.end_date <= sysdate
order by fcr.requested_start_date desc;
#####################################################################
######## Runaway concurrent requests (with no start date )
######################################################################
select a.REQUEST_ID,b.program,to_char(b.ACTUAL_START_DATE,'dd/mm/yy hh24:mi:ss') Time, a.status_code Status,a.phase_code Phase,
c.USER_NAME
from apps.fnd_concurrent_requests a,apps.FND_CONC_REQ_SUMMARY_V b,apps.fnd_user c
where a.request_id=b.request_id and c.USER_ID=a.REQUESTED_BY and a.phase_code='R' and b.ACTUAL_START_DATE is NULL
order by 3;
###############################################################
Currently held locks per concurrent request
###################################################################
set lines 150
col object_name format a32
col mode_held format a15
select /*+ ordered */
fcr.request_id
, object_name
, object_type
, decode( l.block
, 0, 'Not Blocking'
, 1, 'Blocking'
, 2, 'Global'
) status
, decode( v.locked_mode
, 0, 'None'
, 1, 'Null'
, 2, 'Row-S (SS)'
, 3, 'Row-X (SX)'
, 4, 'Share'
, 5, 'S/Row-X (SSX)'
, 6, 'Exclusive'
, to_char(lmode)
) mode_held
from apps.fnd_concurrent_requests fcr
, gv$process pro
, gv$session sess
, gv$locked_object v
, gv$lock l
, dba_objects d
where fcr.phase_code = 'R'
and fcr.oracle_process_id = pro.spid (+)
and pro.addr = sess.paddr (+)
and sess.sid = v.session_id (+)
and v.object_id = d.object_id (+)
and v.object_id = l.id1 (+)
;
REQUEST_ID OBJECT_NAME OBJECT_TYPE STATUS MODE_HELD
---------- -------------------------------- ------------------- ------------ ---------------
1070780 VIRTUATE_GL_OLAP_REFRESH TABLE Not Blocking Exclusive
###########################################################
Find out All Concurrent Queue/Manager sizes/Processes?
#############################################################
set lines 200 pages 300
select a.concurrent_queue_name,b.min_processes,b.max_processes from apps.fnd_concurrent_queues a,apps.fnd_concurrent_queue_size b where a.concurrent_queue_id=b.concurrent_queue_id;
##############################################################
Parameters used and completion time of program
##############################################################
set lines 20000
col PROGRAM for a70
set pagesize 100
col ARGUMENT_TEXT for a50
select a.REQUEST_ID,b.program,a.ARGUMENT_TEXT,decode(a.status_code,'R','Normal','I','Normal','Z','Waiting','D','Cancelled','U','Disabled','E','Error','M','NoManager','C','Normal','H','On-Hold','W','Paused','B','Resuming','P','Scheduled','Q','Standby','S','Suspended','X','Terminated','T','Terminating','A','Waiting','G','Warning') Status,
decode(a.phase_code,'P','Pending','R','Running','C','Completed','I','Inactive') Phase,
to_char(b.ACTUAL_START_DATE,'dd/mm/yy hh24:mi:ss') "Start Time",
to_char(b.ACTUAL_COMPLETION_DATE,'dd/mm/yy hh24:mi:ss') "End Time" ,3600*(b.ACTUAL_COMPLETION_DATE-b.ACTUAL_START_DATE) "Execution Time"
from apps.fnd_concurrent_requests a,apps.FND_CONC_REQ_SUMMARY_V b where a.request_id=b.request_id and A.request_id in ('608397') ;
##################################################################################
Check run time of concurrent program of specific date or for some days duration.
###################################################################################
SELECT /*+ rule */
rq.parent_request_id "Parent Req. ID",
rq.request_id "Req. ID",
tl.user_concurrent_program_name "Program Name",
rq.actual_start_date "Start Date",
rq.actual_completion_date "Completion Date",
ROUND((rq.actual_completion_date -
rq.actual_start_date) * 1440, 2) "Runtime (in Minutes)"
FROM applsys.fnd_concurrent_programs_tl tl,
applsys.fnd_concurrent_requests rq
WHERE tl.application_id = rq.program_application_id
AND tl.concurrent_program_id = rq.concurrent_program_id
AND tl.LANGUAGE = USERENV('LANG')
AND rq.actual_start_date IS NOT NULL
AND rq.actual_completion_date IS NOT NULL
AND tl.user_concurrent_program_name = 'Autoinvoice Import Program' -- <change it>
-- AND TRUNC(rq.actual_start_date) = '&start_date' -- uncomment this for a specific date
ORDER BY rq.request_id DESC;
#############################################################################
Find the terminated requests by users.
################################################################################
SELECT actual_completion_date TERMINATED,
request_id,
SUBSTR(program,1,53) PROGRAM,
SUBSTR(requestor,1,15) REQUESTOR,
parent_request_id PARENT,
completion_text MESSAGE
FROM apps.FND_CONC_REQ_SUMMARY_V
WHERE STATUS_CODE = 'E'
AND actual_completion_date > SYSDATE - 2
--AND requestor LIKE ('%AR_BTOGUSER%')
ORDER BY REQUEST_ID DESC;
##########################################################################333
Check Concurrent Manager and Program rules:
##############################################################################33
--Gives Detail of the Concurrent_queue_name and User_concurrent_program_name
SELECT b.concurrent_queue_name, c.user_concurrent_program_name
FROM FND_CONCURRENT_QUEUE_CONTENT a, fnd_concurrent_queues b, fnd_concurrent_programs_vl c
WHERE a.queue_application_id = 283
and a.concurrent_queue_id = b.concurrent_queue_id
and a.type_id = c.concurrent_program_id
order by decode(INCLUDE_FLAG, 'I', 1, 2), type_code;
################################################################################
Check Scheduled concurrent requests
#####################################################################################
Lot of times we need to find out the concurrent programs scheduled. Users can schedule the concurrent requests in three ways (To run once at a specified time / To run periodically / To run on specific days of the month or week).
The below query will return all the concurrent requests which are scheduled using any of the above methods:
SELECT cr.request_id,
DECODE (cp.user_concurrent_program_name,
'Report Set', 'Report Set:' || cr.description,
cp.user_concurrent_program_name
) NAME,
argument_text, cr.resubmit_interval,
NVL2 (cr.resubmit_interval,
'PERIODICALLY',
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')
) schedule_type,
DECODE (NVL2 (cr.resubmit_interval,
'PERIODICALLY',
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')
),
'PERIODICALLY', 'EVERY '
|| cr.resubmit_interval
|| ' '
|| cr.resubmit_interval_unit_code
|| ' FROM '
|| cr.resubmit_interval_type_code
|| ' OF PREV RUN',
'ONCE', 'AT :'
|| TO_CHAR (cr.requested_start_date, 'DD-MON-RR HH24:MI'),
'EVERY: ' || fcr.class_info
) schedule,
fu.user_name, requested_start_date
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_requests cr,
apps.fnd_user fu,
apps.fnd_conc_release_classes fcr
WHERE cp.application_id = cr.program_application_id
AND cp.concurrent_program_id = cr.concurrent_program_id
AND cr.requested_by = fu.user_id
AND cr.phase_code = 'P'
AND cr.requested_start_date > SYSDATE
AND cp.LANGUAGE = 'US'
AND fcr.release_class_id(+) = cr.release_class_id
AND fcr.application_id(+) = cr.release_class_app_id;
Note: The "SCHEDULE" column in the above query returns a string of zeros and ones for the requests which are scheduled on specific days of the month or week.
Positions 1 through 31: Specific day of the month.
Position 32: Last day of the month
Positions 33 through 39: Sunday through Saturday
#########################################################################
Provide Concurrent Request Set Name, It will list out all the concurrent programs It has?
########################################################################################
SELECT USER_CONCURRENT_PROGRAM_NAME
FROM fnd_concurrent_programs_tl
WHERE CONCURRENT_PROGRAM_ID IN
(SELECT concurrent_program_id
FROM FND_REQUEST_SET_PROGRAMS
WHERE request_set_id =
(SELECT request_set_id
FROM FND_REQUEST_SETS_TL
WHERE upper(user_request_set_name) = upper('&Request_Set_Name')));
##############################################################################################
Provide Concurrent Program Name, It will list out all concurrent requests sets names that has concurrent program in it?
############################################################################################
SELECT DISTINCT user_request_set_name
FROM FND_REQUEST_SETS_TL
WHERE request_set_id IN
(SELECT request_set_id
FROM FND_REQUEST_SET_PROGRAMS
WHERE concurrent_program_id =
(SELECT CONCURRENT_PROGRAM_ID
FROM fnd_concurrent_programs_tl
WHERE upper(USER_CONCURRENT_PROGRAM_NAME) = upper( '&Enter_Prog_name')));
######################################################################################################33
Find out Responsibility name from a Concurrent program?
#####################################################################################################
set lines 200 pages 300
SELECT DISTINCT
FCPL.USER_CONCURRENT_PROGRAM_NAME
, FCP.CONCURRENT_PROGRAM_NAME
, FAPP.APPLICATION_NAME
, FRG.REQUEST_GROUP_NAME
, FNRTL.RESPONSIBILITY_NAME
FROM
APPS.FND_REQUEST_GROUPS FRG
, APPS.FND_APPLICATION_TL FAPP
, APPS.FND_REQUEST_GROUP_UNITS FRGU
, APPS.FND_CONCURRENT_PROGRAMS FCP
, APPS.FND_CONCURRENT_PROGRAMS_TL FCPL
, APPS.FND_RESPONSIBILITY FNR
, APPS.FND_RESPONSIBILITY_TL FNRTL
WHERE FRG.APPLICATION_ID=fapp.APPLICATION_ID
AND FRG.APPLICATION_ID = FRGU.APPLICATION_ID
AND FRG.REQUEST_GROUP_ID = FRGU.REQUEST_GROUP_ID
AND FRG.REQUEST_GROUP_ID = FNR.REQUEST_GROUP_ID
AND FRG.APPLICATION_ID = FNR.APPLICATION_ID
AND FNR.RESPONSIBILITY_ID = FNRTL.RESPONSIBILITY_ID
AND FRGU.REQUEST_UNIT_ID = FCP.CONCURRENT_PROGRAM_ID
AND FRGU.UNIT_APPLICATION_ID = FCP.APPLICATION_ID
AND FCP.CONCURRENT_PROGRAM_ID = FCPL.CONCURRENT_PROGRAM_ID
AND FCPL.USER_CONCURRENT_PROGRAM_NAME LIKE '%&Conc_Prog_name%'
AND FNRTL.LANGUAGE = 'US'
AND FAPP.LANGUAGE = 'US';
#####################################################################
Find out What are all concurrent programs are inlcuded/excluded concurrent Managers?
###############################################################################33
set lines 200 pages 300
col USER_CONCURRENT_QUEUE_NAME for a50
col CONCURRENT_PROGRAM_NAME for a50
break on USER_CONCURRENT_QUEUE_NAME skip 1;
SELECT C.USER_CONCURRENT_QUEUE_NAME,B.CONCURRENT_PROGRAM_NAME,A.INCLUDE_FLAG
FROM APPLSYS.FND_CONCURRENT_QUEUE_CONTENT A, APPLSYS.FND_CONCURRENT_PROGRAMS B, APPS.FND_CONCURRENT_QUEUES_VL C
WHERE type_id = b.concurrent_program_id and c.concurrent_queue_id = a.concurrent_queue_id order by C.USER_CONCURRENT_QUEUE_NAME;
############################################################################33
Find currently spooling temp file from request
#################################################################################
col outfile format a30
col logfile format a30
select cp.plsql_dir || '/' || cp.plsql_out outfile
, cp.plsql_dir || '/' || cp.plsql_log logfile
from apps.fnd_concurrent_requests cr
, apps.fnd_concurrent_processes cp
where cp.concurrent_process_id = cr.controlling_manager
and cr.request_id = &request_id;
OUTFILE LOGFILE
------------------------------ ------------------------------
/usr/tmp/PROD/o0068190.tmp /usr/tmp/PROD/l0068190.tmp
REM Now tail log file on database node to see where it is at, near realtime
REM tail -f /usr/tmp/l0068190.tmp
###########################################################################
Script to Monitor Concurrent requests average run time.
##############################################################################
Do you need a daily report which would give an average run time of all concurrent requests in your environment?
So you would know which concurrent request is taking longer time and on which day:
The below script gives an average run time for all concurrent requests for the current day, previous day, day before yesterday and for the whole week:
set pagesize 500
set echo off
set feedback off
set linesize 200
col USER_CONCURRENT_PROGRAM_NAME for a75
col wkly_Time Heading 'Weekly Run | Time | Avg |(In Minutes) '
col dbydy_Tim Heading 'Day Before|Yesterday | Run Time | Avg |(In Minutes) '
col ydy_Tim Heading 'Yesterday | Run Time | Avg |(In Minutes) '
col tdy_Tim Heading 'Today | Run Time | Avg |(In Minutes) '
col tdy_Tim Heading &_DATE
select wkly.user_concurrent_program_name, wkly.wkly_time , to_char(nvl(dbydy.dbydy_time,' Not Run')) Dbydy_tim,to_char(nvl(ydy.ydy_time,' Not Run')) ydy_tim,to_char(nvl(tdy.tdy_time,' Not Run')) tdy_tim
FROM
(SELECT user_concurrent_program_name,
to_char(avg(round((actual_completion_date- actual_start_date)*24*60)),'99999999.99') wkly_time
FROM apps.fnd_conc_req_summary_v
WHERE nvl(actual_start_date,sysdate) >= (sysdate-7)
AND phase_code='C' and status_code in ('C','G')
group by user_concurrent_program_name) wkly,
(SELECT user_concurrent_program_name,
to_char(avg(round((actual_completion_date- actual_start_date)*24*60)),'99999999.99') dbydy_time
FROM apps.fnd_conc_req_summary_v
WHERE trunc(nvl(actual_start_date,sysdate)) = trunc((sysdate-2))
AND phase_code='C' and status_code in ('C','G')
group by user_concurrent_program_name ) dbydy,
(SELECT user_concurrent_program_name,
to_char(avg(round((actual_completion_date- actual_start_date)*24*60)),'99999999.99') ydy_time
FROM apps.fnd_conc_req_summary_v
WHERE trunc(nvl(actual_start_date,sysdate)) = trunc((sysdate-1))
AND phase_code='C' and status_code in ('C','G')
group by user_concurrent_program_name ) ydy,
(SELECT user_concurrent_program_name,
to_char(avg(round((actual_completion_date- actual_start_date)*24*60)),'99999999.99') tdy_time
FROM apps.fnd_conc_req_summary_v
WHERE trunc(nvl(actual_start_date,sysdate)) = trunc((sysdate))
AND phase_code='C' and status_code in ('C','G')
group by user_concurrent_program_name ) tdy
WHERE wkly.user_concurrent_program_name =dbydy.user_concurrent_program_name (+)
AND dbydy.user_concurrent_program_name = ydy.user_concurrent_program_name (+)
AND ydy.user_concurrent_program_name = tdy.user_concurrent_program_name (+)
order by wkly_time desc;
#############################################################################################3
Checking the duplicated schedules of the same program with the same arguments
#################################################################################################3
The below query can be used to check the duplicated schedule of the same program with the same arguments. This can be used to alert the users to cancel these duplicated schedules.
Note: This query will return even though the request was submitted using a different responsibility.
SELECT request_id, NAME, argument_text, user_name
FROM (SELECT cr.request_id,
DECODE (cp.user_concurrent_program_name,
'Report Set', 'Report Set:' || cr.description,
cp.user_concurrent_program_name
) NAME,
argument_text, fu.user_name
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_requests cr,
apps.fnd_user fu
WHERE cp.application_id = cr.program_application_id
AND cp.concurrent_program_id = cr.concurrent_program_id
AND cr.requested_by = fu.user_id
AND cr.phase_code = 'P'
AND cr.requested_start_date > SYSDATE
AND cp.LANGUAGE = 'US'
AND fu.user_name NOT LIKE 'PPG%') t1
WHERE EXISTS (
SELECT 1
FROM (SELECT cr.request_id,
DECODE (cp.user_concurrent_program_name,
'Report Set', 'Report Set:'
|| cr.description,
cp.user_concurrent_program_name
) NAME,
argument_text, fu.user_name
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_requests cr,
apps.fnd_user fu
WHERE cp.application_id = cr.program_application_id
AND cp.concurrent_program_id =
cr.concurrent_program_id
AND cr.requested_by = fu.user_id
AND cr.phase_code = 'P'
AND cr.requested_start_date > SYSDATE
AND cp.LANGUAGE = 'US'
AND fu.user_name NOT LIKE 'PPG%') t2
WHERE t1.NAME = t2.NAME
AND t1.argument_text = t2.argument_text
AND t1.user_name = t2.user_name
GROUP BY NAME, argument_text, user_name
HAVING COUNT (*) > 1)
ORDER BY user_name, NAME;
############################################################################3
Query to find the executable name associated with the concurrent program
###################################################################################
--input: Concurrent Program Name
SELECT fcpv.user_concurrent_program_name concurrent_program_name
,fe.execution_file_name source_name
,fl.meaning program_type
FROM apps.fnd_concurrent_programs_vl fcpv
,apps.fnd_executables fe
,apps.fnd_lookups fl
WHERE fe.executable_id=fcpv.executable_id
AND fl.lookup_type='CP_EXECUTION_METHOD_CODE'
AND fl.lookup_code=fe.execution_method_code
AND fcpv.user_concurrent_program_name like 'Refresh iSupport Materialized View';
#################################################################################
Query to find the responsibilities which can run the concurrent program
###############################################################################
--input: Concurrent Program name
SELECT frv.responsibility_name
FROM apps.fnd_request_group_units frgu
,apps.fnd_responsibility_vl frv
,apps.fnd_concurrent_programs_vl fcpv
WHERE frv.REQUEST_GROUP_ID=frgu.REQUEST_GROUP_ID
AND fcpv.concurrent_program_id=frgu.REQUEST_UNIT_ID
AND nvl(frv.end_date,sysdate+1)>sysdate
AND EXISTS(
SELECT 'Y'
FROM apps.fnd_user_resp_groups grps
WHERE grps.responsibility_id=frv.responsibility_id
AND nvl(grps.end_date,sysdate+1)>sysdate
)
AND fcpv.user_concurrent_program_name like '%'||:Program_Name||'%'
#############################################################################33
Query to find the last run date of a Concurrent Program
##############################################################################
--input: Concurrent Program name
SELECT max(fcr.REQUEST_DATE)
FROM fnd_concurrent_requests fcr
,fnd_concurrent_programs_tl fcpt
WHERE fcr.concurrent_program_id = fcpt.concurrent_program_id
AND fcr.program_application_id = fcpt.application_id
AND fcr.actual_start_date > SYSDATE - :days_to_check
AND fcpt.user_concurrent_program_name like '%'||:Program_Name||'%'
#########################################################
Query to find Running request in Concurrent Manager
###############################################################
set pages 58
set linesize 79Column Reqst Format 999999
Column Requestor Format A10
Column Orcl Format A7
Column Program Format A10
Column Started Format A14
Column Manager Format A11
Column LN Format a10Column Reqst HEADING 'Request|ID '
Column Requestor HEADING 'Requestor'
Column Orcl HEADING 'Oracle|Name'
Column Started HEADING 'Started at'
Column MANAGER HEADING 'Controlling|Manager'
Column LN HEADING 'Logfile|name'
Column Program HEADING 'Program'
select Request_Id Reqst, User_Name Requestor, Oracle_Username Orcl,
Fcr.Logfile_Name LN,
Concurrent_Queue_Name Manager,
Concurrent_Program_Name Program,
To_Char(Actual_Start_Date, 'Mm-Dd-Yy Hh24:Mi') Started,
Run_Alone_Flag, Single_Thread_Flag
From Fnd_Concurrent_Requests Fcr, Fnd_Concurrent_Programs Fcp,
Fnd_Oracle_Userid O, Fnd_Concurrent_Processes P,
Fnd_Concurrent_Queues Q, Fnd_User
Where
Controlling_Manager = Concurrent_Process_ID
And ( P.Concurrent_Queue_ID = Q.Concurrent_Queue_ID AND
P.Queue_Application_ID = Q.Application_ID )
And O.Oracle_Id = Fcr.Oracle_Id
And ( Fcr.Program_Application_Id = Fcp.Application_Id
And Fcr.Concurrent_Program_Id = Fcp.Concurrent_Program_Id )
And Requested_By = User_Id
And Phase_Code = 'R' and Status_Code = 'R'
Order By Actual_Start_Date, Request_Id;
#######################################################################33
LIST ALL THE REGISTERED CONCURRENT PROGRAMS BY MODULE:
##########################################################################3
set lines 180
set pages 300
col SHORT_NAME for a10
col APPLICATION_NAME for a30
SELECT SUBSTR(a.application_name,1,60) Application_NAME
, b.application_short_name SHORT_NAME
, DECODE(SUBSTR(cp.user_concurrent_program_name,4,1),':'
, 'Concurrent Manager Executable'
, 'Subprogram or Function') TYPE
, SUBSTR(d.concurrent_program_name,1,16) PROGRAM
, SUBSTR(cp.user_concurrent_program_name,1,55) USER_PROGRAM_NAME
FROM applsys.FND_CONCURRENT_PROGRAMS_TL cp, applsys.FND_CONCURRENT_PROGRAMS d, applsys.FND_APPLICATION_TL a, applsys.fnd_application b
WHERE cp.application_id = a.application_id
AND d.CONCURRENT_PROGRAM_ID = cp.CONCURRENT_PROGRAM_ID
AND a.APPLICATION_ID = b.APPLICATION_ID
AND b.application_short_name LIKE UPPER('PA')
UNION ALL
SELECT SUBSTR(a.application_name,1,60) c1
, b.application_short_name c2 , 'Form Executable' c3
, SUBSTR(f.form_name,1,16) c4 ,
SUBSTR(d.user_form_name,1,55) c5
FROM applsys.fnd_form f , applsys.FND_APPLICATION_TL a, applsys.fnd_application b, applsys.FND_FORM_TL d
WHERE f.application_id = a.application_id
AND d.FORM_ID = f.FORM_ID
AND a.APPLICATION_ID = b.APPLICATION_ID
AND b.application_short_name LIKE UPPER('PA') ORDER BY 1,2,3,4;
###########################################################333
PROGRAMS RAN MORE 200 TIMES IN A DAY:
###############################################################
SET LINES 120
SET PAGES 900
COL program FORMAT a70
COL cnt FORMAT 999999 HEADING "Number of Runs"
ttitle 'Programs that ran for more than 200 times ' skip 2
SELECT SUBSTR(user_concurrent_program_name,1,70) program
, COUNT(*) cnt
FROM apps.fnd_conc_req_summary_v
WHERE TRUNC(actual_start_date) = TRUNC(SYSDATE) -1
GROUP BY SUBSTR(user_concurrent_program_name,1,70)
HAVING COUNT(*) > 200
ORDER BY 2
###################################################################
Run history of concurrent program
#####################################################################
Cl03vr
SELECT distinct t.user_concurrent_program_name "Conc Program Name",
r.REQUEST_ID "Request ID",
to_char(r.ACTUAL_START_DATE,'dd-MON-yy hh24:mi:ss') "Started at",
to_char(r.ACTUAL_COMPLETION_DATE,'dd-MON-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",
u.user_name "Username",
--ROUND ((v.actual_completion_date - v.actual_start_date) * 1440,
-- 2
-- ) "Runtime (in Minutes)"
round(((nvl(v.actual_completion_date,sysdate)-v.actual_start_date)*24*60),2) "ElapsedTime(Mins)",
r.LOGFILE_NAME,
r.OUTFILE_NAME
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 - &NO_DAYS)
--AND r.requested_by=22378
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 ='2260046' in ('13829387','13850423')
and t.user_concurrent_program_name like '%ETL%'
order by to_char(r.ACTUAL_COMPLETION_DATE,'dd-MON-yy hh24:mi:ss') desc;
#######################################################################################
--Query to find the executable name associated with the concurrent program
--input: Concurrent Program Name
###########################################################################################
SELECT fcpv.user_concurrent_program_name concurrent_program_name
,fe.execution_file_name source_name
,fl.meaning program_type
FROM apps.fnd_concurrent_programs_vl fcpv
,apps.fnd_executables fe
,apps.fnd_lookups fl
WHERE fe.executable_id=fcpv.executable_id
AND fl.lookup_type='CP_EXECUTION_METHOD_CODE'
AND fl.lookup_code=fe.execution_method_code
AND fcpv.user_concurrent_program_name like 'Refresh iSupport Materialized View';
#######################################################################################
--Query to find the concurrent program associated with an executable
--input: Executable name
#######################################################################################
SELECT fcpv.user_concurrent_program_name concurrent_program_name
,fe.execution_file_name source_name
,fl.meaning program_type
FROM apps.fnd_concurrent_programs_vl fcpv
,apps.fnd_executables fe
,apps.fnd_lookups fl
WHERE fe.executable_id=fcpv.executable_id
AND fl.lookup_type='CP_EXECUTION_METHOD_CODE'
AND fl.lookup_code=fe.execution_method_code
AND fe.execution_file_name like '% Program Name%';
#######################################################################################
--Query to find the Concurrent Requests which are incompatible with Request
--input: Concurrent program name
#######################################################################################
SELECT fcpt.user_concurrent_program_name Input_Program,
(SELECT fcpt1.user_concurrent_program_name Incompatible_Programs
FROM fnd_concurrent_programs_tl fcpt1
WHERE fcpt1.concurrent_program_id in (fcps.to_run_concurrent_program_id)
AND rownum = 1) Incompatible_Programs
FROM fnd_concurrent_program_serial fcps
,fnd_concurrent_programs_tl fcpt
WHERE fcps.running_concurrent_program_id = fcpt.concurrent_program_id
AND fcpt.user_concurrent_program_name like '%Program_Name%';
#######################################################################################
--Query to find if a concurrent request is parent or not
--input: Concurrent request ID
#######################################################################################
SELECT NVL(fcr.has_sub_request,'N') IS_PARENT
FROM fnd_concurrent_requests fcr
WHERE fcr.request_id = :Request_ID;
#######################################################################################
--Query to find the last successful run date of a Concurrent Program
--input: Concurrent Program name
#######################################################################################
SELECT max(fcr.REQUEST_DATE) last_successfull_run_date
FROM fnd_concurrent_requests fcr
,fnd_concurrent_programs_tl fcpt
WHERE fcr.concurrent_program_id = fcpt.concurrent_program_id
AND fcr.program_application_id = fcpt.application_id
AND fcr.status_code in ('C','I','R')
AND fcr.phase_code = 'C'
AND fcr.actual_start_date > SYSDATE - :days_to_check
AND fcpt.user_concurrent_program_name like '%'||:Program_Name||'%';
#######################################################################################
--Query to find the Request set of a Concurrent Program
--input: Concurrent Program name
#######################################################################################
SELECT fcpt.user_concurrent_program_name PROGRAM
,frst.USER_REQUEST_SET_NAME REQUEST_SET
FROM fnd_request_sets_tl frst
,fnd_request_set_programs frsp
,fnd_concurrent_programs_tl fcpt
WHERE frst.request_set_id = frsp.request_set_id
AND fcpt.application_id = frsp.program_application_id
AND fcpt.concurrent_program_id = frsp.concurrent_program_id
AND fcpt.user_concurrent_program_name like '%'||:Program_Name||'%'
####################################################################################
-------------------- Display the Duration of a particular request for the last 1 week -----------------
############################################################################################
SELECT fcr.request_id, ftp.user_concurrent_program_name, fcu.user_name,
ROUND((actual_completion_date - actual_start_date)*24*60,2)minutes, argument_text,actual_start_date,
actual_completion_date
FROM fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl ftp,
fnd_user fcu
WHERE fcu.user_id = fcr.requested_by
AND user_concurrent_program_name = 'Gather Schema Statistics'
AND actual_completion_date > SYSDATE - 7
AND fcr.concurrent_program_id = ftp.concurrent_program_id
ORDER BY actual_completion_date DESC
###############################################################
Find out All Concurrent Queue/Manager sizes/Processes?
###########################################################
set lines 200 pages 300
select a.concurrent_queue_name,b.min_processes,b.max_processes from apps.fnd_concurrent_queues a,apps.fnd_concurrent_queue_size b where a.concurrent_queue_id=b.concurrent_queue_id;
-----------------------------------------------------------
select fcq.application_id,
fcq.concurrent_queue_name,
fcq.user_concurrent_queue_name,
ftp.application_id,
ftp.concurrent_time_period_name,
fa.application_short_name,
ftp.description,
fcqs.min_processes,
fcqs.max_processes,
fcqs.sleep_seconds,
fcqs.service_parameters
from apps.fnd_concurrent_queues_vl fcq,
apps.fnd_concurrent_queue_size fcqs,
apps.fnd_concurrent_time_periods ftp,
apps.fnd_application fa
where fcq.application_id = fcqs.queue_application_id
and fcq.concurrent_queue_id = fcqs.concurrent_queue_id
and fcqs.period_application_id = ftp.application_id
and fcqs.concurrent_time_period_id = ftp.concurrent_time_period_id
and ftp.application_id = fa.application_id;
###############################################################
To see all the pending / Running requests per each manager wise
###############################################################
SELECT request_id, phase_code, status_code, user_name,
user_concurrent_queue_name
FROM apps.fnd_concurrent_worker_requests cwr,
apps.fnd_concurrent_queues_tl cq,
apps.fnd_user fu
WHERE (cwr.phase_code = 'P' OR cwr.phase_code = 'R')
AND cwr.hold_flag != 'Y'
AND cwr.requested_start_date <= SYSDATE
AND cwr.concurrent_queue_id = cq.concurrent_queue_id
AND cwr.queue_application_id = cq.application_id
AND cq.LANGUAGE = 'US'
AND cwr.requested_by = fu.user_id
ORDER BY 5
Note: The same information can be seen in Administer Concurrent Manager form for each manager.
#############################################################################
Query to find the Concurrent Requests which are incompatible with Request
##############################################################################3
--input: Concurrent program name
SELECT fcpt.user_concurrent_program_name Input_Program,
(SELECT fcpt1.user_concurrent_program_name Incompatible_Programs
FROM fnd_concurrent_programs_tl fcpt1
WHERE fcpt1.concurrent_program_id in (fcps.to_run_concurrent_program_id)
AND rownum = 1) Incompatible_Programs
FROM fnd_concurrent_program_serial fcps
,fnd_concurrent_programs_tl fcpt
WHERE fcps.running_concurrent_program_id = fcpt.concurrent_program_id
AND fcpt.user_concurrent_program_name like '%'||:Program_Name||'%'
################################################################
Concurrent Program enabled with trace
#########################################################
col User_Program_Name for a40
col Last_Updated_By for a30
col DESCRIPTION for a30
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;
#######################################
sid
############################
SELECT B.SID, B.SERIAL#, B.INST_ID, B.EVENT, B.BLOCKING_SESSION, C.SQL_ID, C.PLAN_HASH_VALUE,C.module
FROM APPS.fnd_concurrent_requests A
,GV$SESSION B
,GV$SQLAREA C
WHERE A.oracle_session_id = B.audsid
-- AND a.phase_code = 'R'
AND B.sql_hash_value = C.hash_value
AND A.request_id in ('606963','606965','606964','606966','606967','606969','606970','606968','606971');
####################################################################
Request submitted by User
######################################################################
SELECT
user_concurrent_program_name,
request_date,
request_id,
phase_code,
status_code
FROM
fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcp,
fnd_responsibility_tl fr,
fnd_user fu
WHERE
fcr.CONCURRENT_PROGRAM_ID = fcp.concurrent_program_id
and fcr.responsibility_id = fr.responsibility_id
and fcr.requested_by = fu.user_id
and user_name = '&user'
AND actual_start_date > sysdate - 1
ORDER BY REQUEST_DATE Asc;
##########################################################################
Find out request id from Oracle_Process Id
###############################################################################
select REQUEST_ID,ORACLE_PROCESS_ID,OS_PROCESS_Id from fnd_concurrent_requests where
ORACLE_PROCESS_ID='&a';
#######################################################################
find Concurrent Request using SID
######################################################################
SELECT a.request_id,
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 d.sid = &SID;
###########################################################
##list of running requests
###################################
SELECT a.request_id
,a.oracle_process_id "SPID"
,frt.responsibility_name
,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name
,a.description
,a.ARGUMENT_TEXT
,b.node_name
,b.db_instance
,a.logfile_name
,a.logfile_node_name
,a.outfile_name
,q.concurrent_queue_name
,a.phase_code,a.status_code, a.completion_text
, actual_start_date
, actual_completion_date
, fu.user_name
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 mins
,(SELECT avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440 avg_run_time
FROM APPLSYS.fnd_Concurrent_requests a2,
APPLSYS.fnd_concurrent_programs c2
WHERE c2.concurrent_program_id = c.concurrent_program_id
AND a2.concurrent_program_id = c2.concurrent_program_id
AND a2.program_application_id = c2.application_id
AND a2.phase_code || '' = 'C') avg_mins
,round((actual_completion_date - requested_start_date),2) * 24 duration_in_hours
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
,apps.fnd_user fu
,apps.FND_RESPONSIBILITY_TL frt
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.phase_code = 'R'
AND a.status_code = 'R'
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 a.requested_by = fu.user_id
AND a.responsibility_id = frt.responsibility_id
ORDER BY a.actual_start_date DESC;
##############################################################
Concurrent program submitted with values and varibales
##############################################################
set lines 20000
col PROGRAM for a70
set pagesize 100
col ARGUMENT_TEXT for a50
select a.REQUEST_ID,b.program,a.ARGUMENT_TEXT,decode(a.status_code,'R','Normal','I','Normal','Z','Waiting','D','Cancelled','U','Disabled','E','Error','M','NoManager','C','Normal','H','On-Hold','W','Paused','B','Resuming','P','Scheduled','Q','Standby','S','Suspended','X','Terminated','T','Terminating','A','Waiting','G','Warning') Status,
decode(a.phase_code,'P','Pending','R','Running','C','Completed','I','Inactive') Phase,
to_char(b.ACTUAL_START_DATE,'dd/mm/yy hh24:mi:ss') "Start Time",
to_char(b.ACTUAL_COMPLETION_DATE,'dd/mm/yy hh24:mi:ss') "End Time" ,3600*(b.ACTUAL_COMPLETION_DATE-b.ACTUAL_START_DATE) "Execution Time"
from apps.fnd_concurrent_requests a,apps.FND_CONC_REQ_SUMMARY_V b where a.request_id=b.request_id and A.request_id in ('608397') ;
##################################################
Find out session details of a concurrent Request?
###################################################
set lines 200 pages 300
col USER_CONCURRENT_PROGRAM_NAME for a40
select USER_CONCURRENT_PROGRAM_NAME,c.request_id,s.sid,p.spid,s.process,s.osuser,s.username,s.program,s.status,logon_time,last_call_et
from v$session s, v$process p, apps.fnd_concurrent_requests c,apps.fnd_concurrent_programs_tl ct
where oracle_process_id=p.spid
and s.paddr=p.addr and
ct.concurrent_program_id=c.concurrent_program_id
and request_id=&creq_id;
#######################################################
Current running sql text for given concurrent request.
#######################################################
SELECT A.REQUEST_ID, D.SID, D.SERIAL#, D.OSUSER, D.PROCESS, C.SPID,
E.SQL_TEXT
FROM APPS.FND_CONCURRENT_REQUESTS A,
APPS.FND_CONCURRENT_PROCESSES B,
V$PROCESS C,
V$SESSION D,
V$SQL E
WHERE A.CONTROLLING_MANAGER = B.CONCURRENT_PROCESS_ID
AND C.PID = B.ORACLE_PROCESS_ID
AND B.SESSION_ID = D.AUDSID
AND D.SQL_ADDRESS = E.ADDRESS
AND A.REQUEST_ID = 760781;
########################################################
To find child requests for parent id
########################################################
set lines 200
col USER_CONCURRENT_PROGRAM_NAME for a40
col PHASE_CODE for a10
col STATUS_CODE for a10
col COMPLETION_TEXT for a20
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_concurrent_request_id';
########################################################
To find sid,serial# for a given concurrent request id?
##########################################################
set lines 200
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_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.request_id = &Request_ID
AND a.phase_code = 'R';
########################################################
To find the sql query for a given concurrent request sid?
#########################################################
select s.sid, s.serial#,s.inst_id, s.program,p.spid from gv$session s , gv$process p where p.addr =s.paddr and s.sid in ('1192');
#######################################################
Sql text for the request id doing in the backend.
#######################################################
Based on the SPID associated to each running request, query the v$session orv$session_longops table to see what is the request id doing in the backend.
SELECT b.sid, b.serial#, a.spid, b.program, b.osuser, b.machine,
b.TYPE, b.event, b.action, b.p1text, b.p2text, b.p3text, b.state, c.sql_text,b.logon_time
FROM v$process a, v$session b, v$sqltext c
WHERE a.addr=b.paddr
AND b.sql_hash_value = c.hash_value
AND b.STATUS = 'ACTIVE'
AND a.spid = '11696'
ORDER BY a.spid, c.piece;
-----------------------------------------------
SELECT A.REQUEST_ID, D.SID, D.SERIAL#, D.OSUSER, D.PROCESS, C.SPID,
E.SQL_TEXT
FROM APPS.FND_CONCURRENT_REQUESTS A,
APPS.FND_CONCURRENT_PROCESSES B,
V$PROCESS C,
V$SESSION D,
V$SQL E
WHERE A.CONTROLLING_MANAGER = B.CONCURRENT_PROCESS_ID
AND C.PID = B.ORACLE_PROCESS_ID
AND B.SESSION_ID = D.AUDSID
AND D.SQL_ADDRESS = E.ADDRESS
AND A.REQUEST_ID = 740473;
#########################################################################3
To find child requests for parent id
#############################################################################3
set lines 200
col USER_CONCURRENT_PROGRAM_NAME for a40
col PHASE_CODE for a10
col STATUS_CODE for a10
col COMPLETION_TEXT for a20
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_concurrent_request_id';
##############################################################################
Check run time of concurrent program of specific date or for some days duration.
##############################################################################
SELECT /*+ rule */
rq.parent_request_id "Parent Req. ID",
rq.request_id "Req. ID",
tl.user_concurrent_program_name "Program Name",
rq.actual_start_date "Start Date",
rq.actual_completion_date "Completion Date",
ROUND((rq.actual_completion_date -
rq.actual_start_date) * 1440, 2) "Runtime (in Minutes)"
FROM applsys.fnd_concurrent_programs_tl tl,
applsys.fnd_concurrent_requests rq
WHERE tl.application_id = rq.program_application_id
AND tl.concurrent_program_id = rq.concurrent_program_id
AND tl.LANGUAGE = USERENV('LANG')
AND rq.actual_start_date IS NOT NULL
AND rq.actual_completion_date IS NOT NULL
AND tl.user_concurrent_program_name = 'Autoinvoice Import Program' -- <change it>
-- AND TRUNC(rq.actual_start_date) = '&start_date' -- uncomment this for a specific date
ORDER BY rq.request_id DESC;
#######################################################
Checking which manager is going to execute a program
#############################################################
The below query identifies the manager which will be executing a given program. This query is based on the specialization rules set for the managers.
SELECT user_concurrent_program_name, user_concurrent_queue_name
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_queue_content cqc,
apps.fnd_concurrent_queues_tl cq
WHERE cqc.type_application_id(+) = cp.application_id
AND cqc.type_id(+) = cp.concurrent_program_id
AND cqc.type_code(+) = 'P'
AND cqc.include_flag(+) = 'I'
AND cp.LANGUAGE = 'US'
AND cp.user_concurrent_program_name = '&USER_CONCURRENT_PROGRAM_NAME' AND NVL (cqc.concurrent_queue_id, 0) = cq.concurrent_queue_id
AND NVL (cqc.queue_application_id, 0) = cq.application_id
AND cq.LANGUAGE = 'US';
###########################################################
CPU might be more if request submitted by end date user
Requests submitted by end-dated users using the query below
############################################################
select fcr.request_id
, fcpt.user_concurrent_program_name
, fu.user_name submitted_by
, to_char(fcr.requested_start_date,'DD-MON-YYYY HH24:MM:SS') start_date
, 'Every '|| decode(length(fcrc.class_info),39,fcrc.class_info,
substr(fcrc.class_info,1,instr(fcrc.class_info,':',1)-1)||' '||
decode(substr(fcrc.class_info,instr(fcrc.class_info,':',1)+1,1)
, 'N','Minute(s) '
, 'D','Day(s) '
, 'H','Hour(s) '
, 'M','Month(s) ')|| 'after '||
decode(substr(fcrc.class_info,instr(fcrc.class_info,':',1,2)+1,1)
, 'S'
, 'Start '
, 'C'
, 'Completion ')|| 'of prior request' ) scheduled_as
, nvl(to_char(fcrc.end_date_active,'DD-MON-YYYY'),'forever') ending_on
, fu.end_date "USER'S END DATE"
from fnd_concurrent_requests fcr
, fnd_concurrent_programs_tl fcpt
, fnd_user fu
, fnd_conc_release_classes fcrc
where fcr.phase_code = 'P'
and fcr.status_code in ('I','Q')
and fcr.program_application_id = fcpt.application_id
and fcr.concurrent_program_id = fcpt.concurrent_program_id
and fcpt.language = 'US'
and fcr.requested_by = fu.user_id
and fcr.release_class_app_id = fcrc.application_id
and fcr.release_class_id = fcrc.release_class_id
and fu.end_date <= sysdate
order by fcr.requested_start_date desc;
####################################################################################################################################################3
Check the values for the "Concurrent:Sequential Requests", "Concurrent:Active Request Limit" and "Concurrent:Hold Requests (CONC_HOLD)" profile options.
Concurrent:Active Request Limit (CONC_REQUEST_LIMIT)
You can limit the number of requests that may be run simultaneously by each user or for every user at a site. If you do not specify a limit, no limit is imposed.
Concurrent:Sequential Requests (CONC_SINGLE_THREAD)
You can force your requests to run one at a time (sequentially) according to the requests' start dates and times, or allow them to run concurrently, when their programs are compatible.
A value of "Yes" prevents your requests from running concurrently. Requests run sequentially in the order they are submitted.
A value of "No" means your requests can run concurrently when their concurrent programs are compatible.
Concurrent:Hold Requests (CONC_HOLD)
You can automatically place your concurrent requests on hold when you submit them.
The default is "No". The concurrent managers run your requests according to the priority and start time specified for each.
"Yes" means your concurrent requests and reports are automatically placed on hold.
################################################################################################################################################
select fcr.request_id
, fcpt.user_concurrent_program_name
, fu.user_name submitted_by
, to_char(fcr.requested_start_date,'DD-MON-YYYY HH24:MM:SS') start_date
, 'Every '|| decode(length(fcrc.class_info),39,fcrc.class_info,
substr(fcrc.class_info,1,instr(fcrc.class_info,':',1)-1)||' '||
decode(substr(fcrc.class_info,instr(fcrc.class_info,':',1)+1,1)
, 'N','Minute(s) '
, 'D','Day(s) '
, 'H','Hour(s) '
, 'M','Month(s) ')|| 'after '||
decode(substr(fcrc.class_info,instr(fcrc.class_info,':',1,2)+1,1)
, 'S'
, 'Start '
, 'C'
, 'Completion ')|| 'of prior request' ) scheduled_as
, nvl(to_char(fcrc.end_date_active,'DD-MON-YYYY'),'forever') ending_on
, fu.end_date "USER'S END DATE"
from fnd_concurrent_requests fcr
, fnd_concurrent_programs_tl fcpt
, fnd_user fu
, fnd_conc_release_classes fcrc
where fcr.phase_code = 'P'
and fcr.status_code in ('I','Q')
and fcr.program_application_id = fcpt.application_id
and fcr.concurrent_program_id = fcpt.concurrent_program_id
and fcpt.language = 'US'
and fcr.requested_by = fu.user_id
and fcr.release_class_app_id = fcrc.application_id
and fcr.release_class_id = fcrc.release_class_id
and fu.end_date <= sysdate
order by fcr.requested_start_date desc;
#####################################################################
######## Runaway concurrent requests (with no start date )
######################################################################
select a.REQUEST_ID,b.program,to_char(b.ACTUAL_START_DATE,'dd/mm/yy hh24:mi:ss') Time, a.status_code Status,a.phase_code Phase,
c.USER_NAME
from apps.fnd_concurrent_requests a,apps.FND_CONC_REQ_SUMMARY_V b,apps.fnd_user c
where a.request_id=b.request_id and c.USER_ID=a.REQUESTED_BY and a.phase_code='R' and b.ACTUAL_START_DATE is NULL
order by 3;
###############################################################
Currently held locks per concurrent request
###################################################################
set lines 150
col object_name format a32
col mode_held format a15
select /*+ ordered */
fcr.request_id
, object_name
, object_type
, decode( l.block
, 0, 'Not Blocking'
, 1, 'Blocking'
, 2, 'Global'
) status
, decode( v.locked_mode
, 0, 'None'
, 1, 'Null'
, 2, 'Row-S (SS)'
, 3, 'Row-X (SX)'
, 4, 'Share'
, 5, 'S/Row-X (SSX)'
, 6, 'Exclusive'
, to_char(lmode)
) mode_held
from apps.fnd_concurrent_requests fcr
, gv$process pro
, gv$session sess
, gv$locked_object v
, gv$lock l
, dba_objects d
where fcr.phase_code = 'R'
and fcr.oracle_process_id = pro.spid (+)
and pro.addr = sess.paddr (+)
and sess.sid = v.session_id (+)
and v.object_id = d.object_id (+)
and v.object_id = l.id1 (+)
;
REQUEST_ID OBJECT_NAME OBJECT_TYPE STATUS MODE_HELD
---------- -------------------------------- ------------------- ------------ ---------------
1070780 VIRTUATE_GL_OLAP_REFRESH TABLE Not Blocking Exclusive
###########################################################
Find out All Concurrent Queue/Manager sizes/Processes?
#############################################################
set lines 200 pages 300
select a.concurrent_queue_name,b.min_processes,b.max_processes from apps.fnd_concurrent_queues a,apps.fnd_concurrent_queue_size b where a.concurrent_queue_id=b.concurrent_queue_id;
##############################################################
Parameters used and completion time of program
##############################################################
set lines 20000
col PROGRAM for a70
set pagesize 100
col ARGUMENT_TEXT for a50
select a.REQUEST_ID,b.program,a.ARGUMENT_TEXT,decode(a.status_code,'R','Normal','I','Normal','Z','Waiting','D','Cancelled','U','Disabled','E','Error','M','NoManager','C','Normal','H','On-Hold','W','Paused','B','Resuming','P','Scheduled','Q','Standby','S','Suspended','X','Terminated','T','Terminating','A','Waiting','G','Warning') Status,
decode(a.phase_code,'P','Pending','R','Running','C','Completed','I','Inactive') Phase,
to_char(b.ACTUAL_START_DATE,'dd/mm/yy hh24:mi:ss') "Start Time",
to_char(b.ACTUAL_COMPLETION_DATE,'dd/mm/yy hh24:mi:ss') "End Time" ,3600*(b.ACTUAL_COMPLETION_DATE-b.ACTUAL_START_DATE) "Execution Time"
from apps.fnd_concurrent_requests a,apps.FND_CONC_REQ_SUMMARY_V b where a.request_id=b.request_id and A.request_id in ('608397') ;
##################################################################################
Check run time of concurrent program of specific date or for some days duration.
###################################################################################
SELECT /*+ rule */
rq.parent_request_id "Parent Req. ID",
rq.request_id "Req. ID",
tl.user_concurrent_program_name "Program Name",
rq.actual_start_date "Start Date",
rq.actual_completion_date "Completion Date",
ROUND((rq.actual_completion_date -
rq.actual_start_date) * 1440, 2) "Runtime (in Minutes)"
FROM applsys.fnd_concurrent_programs_tl tl,
applsys.fnd_concurrent_requests rq
WHERE tl.application_id = rq.program_application_id
AND tl.concurrent_program_id = rq.concurrent_program_id
AND tl.LANGUAGE = USERENV('LANG')
AND rq.actual_start_date IS NOT NULL
AND rq.actual_completion_date IS NOT NULL
AND tl.user_concurrent_program_name = 'Autoinvoice Import Program' -- <change it>
-- AND TRUNC(rq.actual_start_date) = '&start_date' -- uncomment this for a specific date
ORDER BY rq.request_id DESC;
#############################################################################
Find the terminated requests by users.
################################################################################
SELECT actual_completion_date TERMINATED,
request_id,
SUBSTR(program,1,53) PROGRAM,
SUBSTR(requestor,1,15) REQUESTOR,
parent_request_id PARENT,
completion_text MESSAGE
FROM apps.FND_CONC_REQ_SUMMARY_V
WHERE STATUS_CODE = 'E'
AND actual_completion_date > SYSDATE - 2
--AND requestor LIKE ('%AR_BTOGUSER%')
ORDER BY REQUEST_ID DESC;
##########################################################################333
Check Concurrent Manager and Program rules:
##############################################################################33
--Gives Detail of the Concurrent_queue_name and User_concurrent_program_name
SELECT b.concurrent_queue_name, c.user_concurrent_program_name
FROM FND_CONCURRENT_QUEUE_CONTENT a, fnd_concurrent_queues b, fnd_concurrent_programs_vl c
WHERE a.queue_application_id = 283
and a.concurrent_queue_id = b.concurrent_queue_id
and a.type_id = c.concurrent_program_id
order by decode(INCLUDE_FLAG, 'I', 1, 2), type_code;
################################################################################
Check Scheduled concurrent requests
#####################################################################################
Lot of times we need to find out the concurrent programs scheduled. Users can schedule the concurrent requests in three ways (To run once at a specified time / To run periodically / To run on specific days of the month or week).
The below query will return all the concurrent requests which are scheduled using any of the above methods:
SELECT cr.request_id,
DECODE (cp.user_concurrent_program_name,
'Report Set', 'Report Set:' || cr.description,
cp.user_concurrent_program_name
) NAME,
argument_text, cr.resubmit_interval,
NVL2 (cr.resubmit_interval,
'PERIODICALLY',
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')
) schedule_type,
DECODE (NVL2 (cr.resubmit_interval,
'PERIODICALLY',
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')
),
'PERIODICALLY', 'EVERY '
|| cr.resubmit_interval
|| ' '
|| cr.resubmit_interval_unit_code
|| ' FROM '
|| cr.resubmit_interval_type_code
|| ' OF PREV RUN',
'ONCE', 'AT :'
|| TO_CHAR (cr.requested_start_date, 'DD-MON-RR HH24:MI'),
'EVERY: ' || fcr.class_info
) schedule,
fu.user_name, requested_start_date
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_requests cr,
apps.fnd_user fu,
apps.fnd_conc_release_classes fcr
WHERE cp.application_id = cr.program_application_id
AND cp.concurrent_program_id = cr.concurrent_program_id
AND cr.requested_by = fu.user_id
AND cr.phase_code = 'P'
AND cr.requested_start_date > SYSDATE
AND cp.LANGUAGE = 'US'
AND fcr.release_class_id(+) = cr.release_class_id
AND fcr.application_id(+) = cr.release_class_app_id;
Note: The "SCHEDULE" column in the above query returns a string of zeros and ones for the requests which are scheduled on specific days of the month or week.
Positions 1 through 31: Specific day of the month.
Position 32: Last day of the month
Positions 33 through 39: Sunday through Saturday
#########################################################################
Provide Concurrent Request Set Name, It will list out all the concurrent programs It has?
########################################################################################
SELECT USER_CONCURRENT_PROGRAM_NAME
FROM fnd_concurrent_programs_tl
WHERE CONCURRENT_PROGRAM_ID IN
(SELECT concurrent_program_id
FROM FND_REQUEST_SET_PROGRAMS
WHERE request_set_id =
(SELECT request_set_id
FROM FND_REQUEST_SETS_TL
WHERE upper(user_request_set_name) = upper('&Request_Set_Name')));
##############################################################################################
Provide Concurrent Program Name, It will list out all concurrent requests sets names that has concurrent program in it?
############################################################################################
SELECT DISTINCT user_request_set_name
FROM FND_REQUEST_SETS_TL
WHERE request_set_id IN
(SELECT request_set_id
FROM FND_REQUEST_SET_PROGRAMS
WHERE concurrent_program_id =
(SELECT CONCURRENT_PROGRAM_ID
FROM fnd_concurrent_programs_tl
WHERE upper(USER_CONCURRENT_PROGRAM_NAME) = upper( '&Enter_Prog_name')));
######################################################################################################33
Find out Responsibility name from a Concurrent program?
#####################################################################################################
set lines 200 pages 300
SELECT DISTINCT
FCPL.USER_CONCURRENT_PROGRAM_NAME
, FCP.CONCURRENT_PROGRAM_NAME
, FAPP.APPLICATION_NAME
, FRG.REQUEST_GROUP_NAME
, FNRTL.RESPONSIBILITY_NAME
FROM
APPS.FND_REQUEST_GROUPS FRG
, APPS.FND_APPLICATION_TL FAPP
, APPS.FND_REQUEST_GROUP_UNITS FRGU
, APPS.FND_CONCURRENT_PROGRAMS FCP
, APPS.FND_CONCURRENT_PROGRAMS_TL FCPL
, APPS.FND_RESPONSIBILITY FNR
, APPS.FND_RESPONSIBILITY_TL FNRTL
WHERE FRG.APPLICATION_ID=fapp.APPLICATION_ID
AND FRG.APPLICATION_ID = FRGU.APPLICATION_ID
AND FRG.REQUEST_GROUP_ID = FRGU.REQUEST_GROUP_ID
AND FRG.REQUEST_GROUP_ID = FNR.REQUEST_GROUP_ID
AND FRG.APPLICATION_ID = FNR.APPLICATION_ID
AND FNR.RESPONSIBILITY_ID = FNRTL.RESPONSIBILITY_ID
AND FRGU.REQUEST_UNIT_ID = FCP.CONCURRENT_PROGRAM_ID
AND FRGU.UNIT_APPLICATION_ID = FCP.APPLICATION_ID
AND FCP.CONCURRENT_PROGRAM_ID = FCPL.CONCURRENT_PROGRAM_ID
AND FCPL.USER_CONCURRENT_PROGRAM_NAME LIKE '%&Conc_Prog_name%'
AND FNRTL.LANGUAGE = 'US'
AND FAPP.LANGUAGE = 'US';
#####################################################################
Find out What are all concurrent programs are inlcuded/excluded concurrent Managers?
###############################################################################33
set lines 200 pages 300
col USER_CONCURRENT_QUEUE_NAME for a50
col CONCURRENT_PROGRAM_NAME for a50
break on USER_CONCURRENT_QUEUE_NAME skip 1;
SELECT C.USER_CONCURRENT_QUEUE_NAME,B.CONCURRENT_PROGRAM_NAME,A.INCLUDE_FLAG
FROM APPLSYS.FND_CONCURRENT_QUEUE_CONTENT A, APPLSYS.FND_CONCURRENT_PROGRAMS B, APPS.FND_CONCURRENT_QUEUES_VL C
WHERE type_id = b.concurrent_program_id and c.concurrent_queue_id = a.concurrent_queue_id order by C.USER_CONCURRENT_QUEUE_NAME;
############################################################################33
Find currently spooling temp file from request
#################################################################################
col outfile format a30
col logfile format a30
select cp.plsql_dir || '/' || cp.plsql_out outfile
, cp.plsql_dir || '/' || cp.plsql_log logfile
from apps.fnd_concurrent_requests cr
, apps.fnd_concurrent_processes cp
where cp.concurrent_process_id = cr.controlling_manager
and cr.request_id = &request_id;
OUTFILE LOGFILE
------------------------------ ------------------------------
/usr/tmp/PROD/o0068190.tmp /usr/tmp/PROD/l0068190.tmp
REM Now tail log file on database node to see where it is at, near realtime
REM tail -f /usr/tmp/l0068190.tmp
###########################################################################
Script to Monitor Concurrent requests average run time.
##############################################################################
Do you need a daily report which would give an average run time of all concurrent requests in your environment?
So you would know which concurrent request is taking longer time and on which day:
The below script gives an average run time for all concurrent requests for the current day, previous day, day before yesterday and for the whole week:
set pagesize 500
set echo off
set feedback off
set linesize 200
col USER_CONCURRENT_PROGRAM_NAME for a75
col wkly_Time Heading 'Weekly Run | Time | Avg |(In Minutes) '
col dbydy_Tim Heading 'Day Before|Yesterday | Run Time | Avg |(In Minutes) '
col ydy_Tim Heading 'Yesterday | Run Time | Avg |(In Minutes) '
col tdy_Tim Heading 'Today | Run Time | Avg |(In Minutes) '
col tdy_Tim Heading &_DATE
select wkly.user_concurrent_program_name, wkly.wkly_time , to_char(nvl(dbydy.dbydy_time,' Not Run')) Dbydy_tim,to_char(nvl(ydy.ydy_time,' Not Run')) ydy_tim,to_char(nvl(tdy.tdy_time,' Not Run')) tdy_tim
FROM
(SELECT user_concurrent_program_name,
to_char(avg(round((actual_completion_date- actual_start_date)*24*60)),'99999999.99') wkly_time
FROM apps.fnd_conc_req_summary_v
WHERE nvl(actual_start_date,sysdate) >= (sysdate-7)
AND phase_code='C' and status_code in ('C','G')
group by user_concurrent_program_name) wkly,
(SELECT user_concurrent_program_name,
to_char(avg(round((actual_completion_date- actual_start_date)*24*60)),'99999999.99') dbydy_time
FROM apps.fnd_conc_req_summary_v
WHERE trunc(nvl(actual_start_date,sysdate)) = trunc((sysdate-2))
AND phase_code='C' and status_code in ('C','G')
group by user_concurrent_program_name ) dbydy,
(SELECT user_concurrent_program_name,
to_char(avg(round((actual_completion_date- actual_start_date)*24*60)),'99999999.99') ydy_time
FROM apps.fnd_conc_req_summary_v
WHERE trunc(nvl(actual_start_date,sysdate)) = trunc((sysdate-1))
AND phase_code='C' and status_code in ('C','G')
group by user_concurrent_program_name ) ydy,
(SELECT user_concurrent_program_name,
to_char(avg(round((actual_completion_date- actual_start_date)*24*60)),'99999999.99') tdy_time
FROM apps.fnd_conc_req_summary_v
WHERE trunc(nvl(actual_start_date,sysdate)) = trunc((sysdate))
AND phase_code='C' and status_code in ('C','G')
group by user_concurrent_program_name ) tdy
WHERE wkly.user_concurrent_program_name =dbydy.user_concurrent_program_name (+)
AND dbydy.user_concurrent_program_name = ydy.user_concurrent_program_name (+)
AND ydy.user_concurrent_program_name = tdy.user_concurrent_program_name (+)
order by wkly_time desc;
#############################################################################################3
Checking the duplicated schedules of the same program with the same arguments
#################################################################################################3
The below query can be used to check the duplicated schedule of the same program with the same arguments. This can be used to alert the users to cancel these duplicated schedules.
Note: This query will return even though the request was submitted using a different responsibility.
SELECT request_id, NAME, argument_text, user_name
FROM (SELECT cr.request_id,
DECODE (cp.user_concurrent_program_name,
'Report Set', 'Report Set:' || cr.description,
cp.user_concurrent_program_name
) NAME,
argument_text, fu.user_name
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_requests cr,
apps.fnd_user fu
WHERE cp.application_id = cr.program_application_id
AND cp.concurrent_program_id = cr.concurrent_program_id
AND cr.requested_by = fu.user_id
AND cr.phase_code = 'P'
AND cr.requested_start_date > SYSDATE
AND cp.LANGUAGE = 'US'
AND fu.user_name NOT LIKE 'PPG%') t1
WHERE EXISTS (
SELECT 1
FROM (SELECT cr.request_id,
DECODE (cp.user_concurrent_program_name,
'Report Set', 'Report Set:'
|| cr.description,
cp.user_concurrent_program_name
) NAME,
argument_text, fu.user_name
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_requests cr,
apps.fnd_user fu
WHERE cp.application_id = cr.program_application_id
AND cp.concurrent_program_id =
cr.concurrent_program_id
AND cr.requested_by = fu.user_id
AND cr.phase_code = 'P'
AND cr.requested_start_date > SYSDATE
AND cp.LANGUAGE = 'US'
AND fu.user_name NOT LIKE 'PPG%') t2
WHERE t1.NAME = t2.NAME
AND t1.argument_text = t2.argument_text
AND t1.user_name = t2.user_name
GROUP BY NAME, argument_text, user_name
HAVING COUNT (*) > 1)
ORDER BY user_name, NAME;
############################################################################3
Query to find the executable name associated with the concurrent program
###################################################################################
--input: Concurrent Program Name
SELECT fcpv.user_concurrent_program_name concurrent_program_name
,fe.execution_file_name source_name
,fl.meaning program_type
FROM apps.fnd_concurrent_programs_vl fcpv
,apps.fnd_executables fe
,apps.fnd_lookups fl
WHERE fe.executable_id=fcpv.executable_id
AND fl.lookup_type='CP_EXECUTION_METHOD_CODE'
AND fl.lookup_code=fe.execution_method_code
AND fcpv.user_concurrent_program_name like 'Refresh iSupport Materialized View';
#################################################################################
Query to find the responsibilities which can run the concurrent program
###############################################################################
--input: Concurrent Program name
SELECT frv.responsibility_name
FROM apps.fnd_request_group_units frgu
,apps.fnd_responsibility_vl frv
,apps.fnd_concurrent_programs_vl fcpv
WHERE frv.REQUEST_GROUP_ID=frgu.REQUEST_GROUP_ID
AND fcpv.concurrent_program_id=frgu.REQUEST_UNIT_ID
AND nvl(frv.end_date,sysdate+1)>sysdate
AND EXISTS(
SELECT 'Y'
FROM apps.fnd_user_resp_groups grps
WHERE grps.responsibility_id=frv.responsibility_id
AND nvl(grps.end_date,sysdate+1)>sysdate
)
AND fcpv.user_concurrent_program_name like '%'||:Program_Name||'%'
#############################################################################33
Query to find the last run date of a Concurrent Program
##############################################################################
--input: Concurrent Program name
SELECT max(fcr.REQUEST_DATE)
FROM fnd_concurrent_requests fcr
,fnd_concurrent_programs_tl fcpt
WHERE fcr.concurrent_program_id = fcpt.concurrent_program_id
AND fcr.program_application_id = fcpt.application_id
AND fcr.actual_start_date > SYSDATE - :days_to_check
AND fcpt.user_concurrent_program_name like '%'||:Program_Name||'%'
#########################################################
Query to find Running request in Concurrent Manager
###############################################################
set pages 58
set linesize 79Column Reqst Format 999999
Column Requestor Format A10
Column Orcl Format A7
Column Program Format A10
Column Started Format A14
Column Manager Format A11
Column LN Format a10Column Reqst HEADING 'Request|ID '
Column Requestor HEADING 'Requestor'
Column Orcl HEADING 'Oracle|Name'
Column Started HEADING 'Started at'
Column MANAGER HEADING 'Controlling|Manager'
Column LN HEADING 'Logfile|name'
Column Program HEADING 'Program'
select Request_Id Reqst, User_Name Requestor, Oracle_Username Orcl,
Fcr.Logfile_Name LN,
Concurrent_Queue_Name Manager,
Concurrent_Program_Name Program,
To_Char(Actual_Start_Date, 'Mm-Dd-Yy Hh24:Mi') Started,
Run_Alone_Flag, Single_Thread_Flag
From Fnd_Concurrent_Requests Fcr, Fnd_Concurrent_Programs Fcp,
Fnd_Oracle_Userid O, Fnd_Concurrent_Processes P,
Fnd_Concurrent_Queues Q, Fnd_User
Where
Controlling_Manager = Concurrent_Process_ID
And ( P.Concurrent_Queue_ID = Q.Concurrent_Queue_ID AND
P.Queue_Application_ID = Q.Application_ID )
And O.Oracle_Id = Fcr.Oracle_Id
And ( Fcr.Program_Application_Id = Fcp.Application_Id
And Fcr.Concurrent_Program_Id = Fcp.Concurrent_Program_Id )
And Requested_By = User_Id
And Phase_Code = 'R' and Status_Code = 'R'
Order By Actual_Start_Date, Request_Id;
#######################################################################33
LIST ALL THE REGISTERED CONCURRENT PROGRAMS BY MODULE:
##########################################################################3
set lines 180
set pages 300
col SHORT_NAME for a10
col APPLICATION_NAME for a30
SELECT SUBSTR(a.application_name,1,60) Application_NAME
, b.application_short_name SHORT_NAME
, DECODE(SUBSTR(cp.user_concurrent_program_name,4,1),':'
, 'Concurrent Manager Executable'
, 'Subprogram or Function') TYPE
, SUBSTR(d.concurrent_program_name,1,16) PROGRAM
, SUBSTR(cp.user_concurrent_program_name,1,55) USER_PROGRAM_NAME
FROM applsys.FND_CONCURRENT_PROGRAMS_TL cp, applsys.FND_CONCURRENT_PROGRAMS d, applsys.FND_APPLICATION_TL a, applsys.fnd_application b
WHERE cp.application_id = a.application_id
AND d.CONCURRENT_PROGRAM_ID = cp.CONCURRENT_PROGRAM_ID
AND a.APPLICATION_ID = b.APPLICATION_ID
AND b.application_short_name LIKE UPPER('PA')
UNION ALL
SELECT SUBSTR(a.application_name,1,60) c1
, b.application_short_name c2 , 'Form Executable' c3
, SUBSTR(f.form_name,1,16) c4 ,
SUBSTR(d.user_form_name,1,55) c5
FROM applsys.fnd_form f , applsys.FND_APPLICATION_TL a, applsys.fnd_application b, applsys.FND_FORM_TL d
WHERE f.application_id = a.application_id
AND d.FORM_ID = f.FORM_ID
AND a.APPLICATION_ID = b.APPLICATION_ID
AND b.application_short_name LIKE UPPER('PA') ORDER BY 1,2,3,4;
###########################################################333
PROGRAMS RAN MORE 200 TIMES IN A DAY:
###############################################################
SET LINES 120
SET PAGES 900
COL program FORMAT a70
COL cnt FORMAT 999999 HEADING "Number of Runs"
ttitle 'Programs that ran for more than 200 times ' skip 2
SELECT SUBSTR(user_concurrent_program_name,1,70) program
, COUNT(*) cnt
FROM apps.fnd_conc_req_summary_v
WHERE TRUNC(actual_start_date) = TRUNC(SYSDATE) -1
GROUP BY SUBSTR(user_concurrent_program_name,1,70)
HAVING COUNT(*) > 200
ORDER BY 2
###################################################################
Run history of concurrent program
#####################################################################
Cl03vr
SELECT distinct t.user_concurrent_program_name "Conc Program Name",
r.REQUEST_ID "Request ID",
to_char(r.ACTUAL_START_DATE,'dd-MON-yy hh24:mi:ss') "Started at",
to_char(r.ACTUAL_COMPLETION_DATE,'dd-MON-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",
u.user_name "Username",
--ROUND ((v.actual_completion_date - v.actual_start_date) * 1440,
-- 2
-- ) "Runtime (in Minutes)"
round(((nvl(v.actual_completion_date,sysdate)-v.actual_start_date)*24*60),2) "ElapsedTime(Mins)",
r.LOGFILE_NAME,
r.OUTFILE_NAME
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 - &NO_DAYS)
--AND r.requested_by=22378
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 ='2260046' in ('13829387','13850423')
and t.user_concurrent_program_name like '%ETL%'
order by to_char(r.ACTUAL_COMPLETION_DATE,'dd-MON-yy hh24:mi:ss') desc;
#######################################################################################
--Query to find the executable name associated with the concurrent program
--input: Concurrent Program Name
###########################################################################################
SELECT fcpv.user_concurrent_program_name concurrent_program_name
,fe.execution_file_name source_name
,fl.meaning program_type
FROM apps.fnd_concurrent_programs_vl fcpv
,apps.fnd_executables fe
,apps.fnd_lookups fl
WHERE fe.executable_id=fcpv.executable_id
AND fl.lookup_type='CP_EXECUTION_METHOD_CODE'
AND fl.lookup_code=fe.execution_method_code
AND fcpv.user_concurrent_program_name like 'Refresh iSupport Materialized View';
#######################################################################################
--Query to find the concurrent program associated with an executable
--input: Executable name
#######################################################################################
SELECT fcpv.user_concurrent_program_name concurrent_program_name
,fe.execution_file_name source_name
,fl.meaning program_type
FROM apps.fnd_concurrent_programs_vl fcpv
,apps.fnd_executables fe
,apps.fnd_lookups fl
WHERE fe.executable_id=fcpv.executable_id
AND fl.lookup_type='CP_EXECUTION_METHOD_CODE'
AND fl.lookup_code=fe.execution_method_code
AND fe.execution_file_name like '% Program Name%';
#######################################################################################
--Query to find the Concurrent Requests which are incompatible with Request
--input: Concurrent program name
#######################################################################################
SELECT fcpt.user_concurrent_program_name Input_Program,
(SELECT fcpt1.user_concurrent_program_name Incompatible_Programs
FROM fnd_concurrent_programs_tl fcpt1
WHERE fcpt1.concurrent_program_id in (fcps.to_run_concurrent_program_id)
AND rownum = 1) Incompatible_Programs
FROM fnd_concurrent_program_serial fcps
,fnd_concurrent_programs_tl fcpt
WHERE fcps.running_concurrent_program_id = fcpt.concurrent_program_id
AND fcpt.user_concurrent_program_name like '%Program_Name%';
#######################################################################################
--Query to find if a concurrent request is parent or not
--input: Concurrent request ID
#######################################################################################
SELECT NVL(fcr.has_sub_request,'N') IS_PARENT
FROM fnd_concurrent_requests fcr
WHERE fcr.request_id = :Request_ID;
#######################################################################################
--Query to find the last successful run date of a Concurrent Program
--input: Concurrent Program name
#######################################################################################
SELECT max(fcr.REQUEST_DATE) last_successfull_run_date
FROM fnd_concurrent_requests fcr
,fnd_concurrent_programs_tl fcpt
WHERE fcr.concurrent_program_id = fcpt.concurrent_program_id
AND fcr.program_application_id = fcpt.application_id
AND fcr.status_code in ('C','I','R')
AND fcr.phase_code = 'C'
AND fcr.actual_start_date > SYSDATE - :days_to_check
AND fcpt.user_concurrent_program_name like '%'||:Program_Name||'%';
#######################################################################################
--Query to find the Request set of a Concurrent Program
--input: Concurrent Program name
#######################################################################################
SELECT fcpt.user_concurrent_program_name PROGRAM
,frst.USER_REQUEST_SET_NAME REQUEST_SET
FROM fnd_request_sets_tl frst
,fnd_request_set_programs frsp
,fnd_concurrent_programs_tl fcpt
WHERE frst.request_set_id = frsp.request_set_id
AND fcpt.application_id = frsp.program_application_id
AND fcpt.concurrent_program_id = frsp.concurrent_program_id
AND fcpt.user_concurrent_program_name like '%'||:Program_Name||'%'
####################################################################################
-------------------- Display the Duration of a particular request for the last 1 week -----------------
############################################################################################
SELECT fcr.request_id, ftp.user_concurrent_program_name, fcu.user_name,
ROUND((actual_completion_date - actual_start_date)*24*60,2)minutes, argument_text,actual_start_date,
actual_completion_date
FROM fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl ftp,
fnd_user fcu
WHERE fcu.user_id = fcr.requested_by
AND user_concurrent_program_name = 'Gather Schema Statistics'
AND actual_completion_date > SYSDATE - 7
AND fcr.concurrent_program_id = ftp.concurrent_program_id
ORDER BY actual_completion_date DESC
No comments:
Post a Comment