SQL TO FIND OUT CONCURRENT REQUESTS
CURRENTLY RUNNING:
**********************************************************
set lines 180
set pages 1000
set verify off
undef spid
column req_id format 99999999999
column OPID format a10
column PPID format a8
column SPID format a8
column ST_CD format a1
column ph_cd format a1
column CNAME format a30
column event format a15
column user_name format a10
column program format a8
column serial# format 999999
column sid format 9999
column username format a8
select a.request_id "REQ_ID",a.oracle_process_id "OPID",a.os_process_id
"PPID",
e.user_concurrent_program_name "CNAME",
f.user_name,a.status_code "ST_CD",a.phase_code "PH_CD", b.username,b.sid,
b.serial#,b.program,g.event,
to_char(a.ACTUAL_START_DATE,'MON-DD-HH-MI-SS') START_DATE,
to_char(a.ACTUAL_COMPLETION_DATE,'MON-DD-HH-MI-SS') COMPL_DATE
from apps.fnd_concurrent_requests a,(select c.username,c.sid,c.serial#,
c.program,d.spid from v$session c, v$process d
where c.paddr=d.addr) b,
apps.fnd_concurrent_programs_tl e,
apps.fnd_user f,
v$session_wait g
where a.oracle_process_id=b.spid
and a.concurrent_program_id=e.concurrent_program_id
and e.language='US'
and a.requested_by=f.user_id
and b.sid=g.sid
and a.status_code='R'
and a.phase_code='R';
**********************************************************
set lines 180
set pages 1000
set verify off
undef spid
column req_id format 99999999999
column OPID format a10
column PPID format a8
column SPID format a8
column ST_CD format a1
column ph_cd format a1
column CNAME format a30
column event format a15
column user_name format a10
column program format a8
column serial# format 999999
column sid format 9999
column username format a8
select a.request_id "REQ_ID",a.oracle_process_id "OPID",a.os_process_id
"PPID",
e.user_concurrent_program_name "CNAME",
f.user_name,a.status_code "ST_CD",a.phase_code "PH_CD", b.username,b.sid,
b.serial#,b.program,g.event,
to_char(a.ACTUAL_START_DATE,'MON-DD-HH-MI-SS') START_DATE,
to_char(a.ACTUAL_COMPLETION_DATE,'MON-DD-HH-MI-SS') COMPL_DATE
from apps.fnd_concurrent_requests a,(select c.username,c.sid,c.serial#,
c.program,d.spid from v$session c, v$process d
where c.paddr=d.addr) b,
apps.fnd_concurrent_programs_tl e,
apps.fnd_user f,
v$session_wait g
where a.oracle_process_id=b.spid
and a.concurrent_program_id=e.concurrent_program_id
and e.language='US'
and a.requested_by=f.user_id
and b.sid=g.sid
and a.status_code='R'
and a.phase_code='R';
SEE
THE CURRENT STATUS OF ALL SUBMITTED REQUEST.
SELECT
fu.user_name
"User ID",
frt.responsibility_name
"Responsibility
Used",
fcr.request_id
"Request ID",
fcpt.user_concurrent_program_name
"Concurrent Program Name",
DECODE(fcr.phase_code,
'C', 'Completed',
'P', 'Pending',
'R', 'Running',
'I', 'Inactive',
fcr.phase_code)
"Phase",
DECODE(fcr.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',
fcr.status_code)
"Status",
fcr.request_date
"Request Date",
fcr.requested_start_date
"Request Start Date",
fcr.hold_flag
"Hold Flag",
fcr.printer
"Printer Name",
fcr.parent_request_id
"Parent Request ID"
-- fcr.number_of_arguments,
-- fcr.argument_text,
-- fcr.logfile_name,
-- fcr.outfile_name
FROM
fnd_user
fu,
fnd_responsibility_tl frt,
fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl
fcpt
WHERE fu.user_id
= fcr.requested_by
AND fcr.concurrent_program_id =
fcpt.concurrent_program_id
AND fcr.responsibility_id =
frt.responsibility_id
AND frt.LANGUAGE
= USERENV('LANG')
AND fcpt.LANGUAGE
= USERENV('LANG')
-- AND
fcr.request_id = 7137350 -- <change it>
-- AND fcpt.user_concurrent_program_name = 'Autoinvoice Import Program' --<
change it>
ORDER BY fcr.request_date DESC;
FIND
THE PENDING STATUS OF Concurrent
request.
Long running Concurrent Requests
Source:
http://shaikoracleappsdba.blogspot.in/2013/09/long-running-concurrent-requests.html
1.How to Determine Which Manager Ran a Specific Concurrent Request?
col USER_CONCURRENT_QUEUE_NAME for a100
select b.USER_CONCURRENT_QUEUE_NAME from fnd_concurrent_processes a,
fnd_concurrent_queues_vl b, fnd_concurrent_requests c
where a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID
and a.CONCURRENT_PROCESS_ID = c.controlling_manager
and c.request_id = '&conc_reqid';
=========================================================================
1.How to Determine Which Manager Ran a Specific Concurrent Request?
col USER_CONCURRENT_QUEUE_NAME for a100
select b.USER_CONCURRENT_QUEUE_NAME from fnd_concurrent_processes a,
fnd_concurrent_queues_vl b, fnd_concurrent_requests c
where a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID
and a.CONCURRENT_PROCESS_ID = c.controlling_manager
and c.request_id = '&conc_reqid';
=========================================================================
Long Running Concurrent Request In Oracle
:
Looking on how to check long running concurrent request in
Oracle Apps 11i or R12? Here’s the overview of the SQL query script to detect
the session information of each program.
First you need to get the listing of running concurrent request in Oracle Apps 11i or R12. You can use the SQL query script as below to obtain the list of running request.
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
You can see the request id and other relevant information from the result.
Based on the SPID associated to each running request, query the v$session or v$session_longops table to see what is the request id doing in the backend.
11284795
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
Replace v$session with gv$session if the database is running on RAC environment. Enable or set trace if you wish to know more details on the session.
====================================================================
2.Concurrent manager status for a given sid?
col MODULE for a20
col OSUSER for a10
col USERNAME for a10
set num 10
col MACHINE for a20
set lines 200
col SCHEMANAME for a10
select s.sid,s.serial#,p.spid os_pid,s.status, s.osuser,s.username, s.MACHINE,s.MODULE,
s.SCHEMANAME,
s.action from gv$session s, gv$process p WHERE s.paddr = p.addr and s.sid = '&oracle_sid';
3. Find out request id from Oracle_Process Id:
select REQUEST_ID,ORACLE_PROCESS_ID,OS_PROCESS_Id from apps.fnd_concurrent_requests where
ORACLE_PROCESS_ID='&a';
4.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';
5.To find concurrent program name,phase code,status code for a given request id?
SELECT request_id, user_concurrent_program_name, DECODE(phase_code,'C','Completed',phase_code)
phase_code, DECODE(status_code,'D', 'Cancelled' ,
'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X',
'Terminated', 'C', 'Normal', status_code) status_code, to_char(actual_start_date,'dd-mon-
yy:hh24:mi:ss') Start_Date, to_char(actual_completion_date,'dd-mon-yy:hh24:mi:ss'),
completion_text FROM apps.fnd_conc_req_summary_v WHERE request_id = '&req_id' ORDER BY 6 DESC;
6.To find the sql query for a given concurrent request sid?
select sid,sql_text from gv$session ses, gv$sqlarea sql where
ses.sql_hash_value = sql.hash_value(+) and ses.sql_address = sql.address(+) and
ses.sid='&oracle_sid'
/
7. To find child requests
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';
8. Cancelling Concurrent request :
update fnd_concurrent_requests
set status_code='D', phase_code='C'
where request_id=&req_id;
9. Kill sessions program wise
select 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' immediate;' from v$session where
MODULE like '';
10 .Concurrent Request running by 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;
11. 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';
12. Oracle Concurrent Request Error Script (requests which were error ed out)
SELECT a.request_id "Req Id"
,a.phase_code,a.status_code
, actual_start_date
, actual_completion_date
,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name "program"
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
WHERE a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND a.status_code = 'E'
AND a.phase_code = 'C'
AND actual_start_date > sysdate - 2
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND ctl.LANGUAGE = 'US'
ORDER BY 5 DESC;
13. 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;
14.Concurrent Program enable 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
15.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.
First you need to get the listing of running concurrent request in Oracle Apps 11i or R12. You can use the SQL query script as below to obtain the list of running request.
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
You can see the request id and other relevant information from the result.
Based on the SPID associated to each running request, query the v$session or v$session_longops table to see what is the request id doing in the backend.
11284795
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
Replace v$session with gv$session if the database is running on RAC environment. Enable or set trace if you wish to know more details on the session.
====================================================================
2.Concurrent manager status for a given sid?
col MODULE for a20
col OSUSER for a10
col USERNAME for a10
set num 10
col MACHINE for a20
set lines 200
col SCHEMANAME for a10
select s.sid,s.serial#,p.spid os_pid,s.status, s.osuser,s.username, s.MACHINE,s.MODULE,
s.SCHEMANAME,
s.action from gv$session s, gv$process p WHERE s.paddr = p.addr and s.sid = '&oracle_sid';
3. Find out request id from Oracle_Process Id:
select REQUEST_ID,ORACLE_PROCESS_ID,OS_PROCESS_Id from apps.fnd_concurrent_requests where
ORACLE_PROCESS_ID='&a';
4.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';
5.To find concurrent program name,phase code,status code for a given request id?
SELECT request_id, user_concurrent_program_name, DECODE(phase_code,'C','Completed',phase_code)
phase_code, DECODE(status_code,'D', 'Cancelled' ,
'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X',
'Terminated', 'C', 'Normal', status_code) status_code, to_char(actual_start_date,'dd-mon-
yy:hh24:mi:ss') Start_Date, to_char(actual_completion_date,'dd-mon-yy:hh24:mi:ss'),
completion_text FROM apps.fnd_conc_req_summary_v WHERE request_id = '&req_id' ORDER BY 6 DESC;
6.To find the sql query for a given concurrent request sid?
select sid,sql_text from gv$session ses, gv$sqlarea sql where
ses.sql_hash_value = sql.hash_value(+) and ses.sql_address = sql.address(+) and
ses.sid='&oracle_sid'
/
7. To find child requests
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';
8. Cancelling Concurrent request :
update fnd_concurrent_requests
set status_code='D', phase_code='C'
where request_id=&req_id;
9. Kill sessions program wise
select 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' immediate;' from v$session where
MODULE like '';
10 .Concurrent Request running by 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;
11. 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';
12. Oracle Concurrent Request Error Script (requests which were error ed out)
SELECT a.request_id "Req Id"
,a.phase_code,a.status_code
, actual_start_date
, actual_completion_date
,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name "program"
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
WHERE a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND a.status_code = 'E'
AND a.phase_code = 'C'
AND actual_start_date > sysdate - 2
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND ctl.LANGUAGE = 'US'
ORDER BY 5 DESC;
13. 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;
14.Concurrent Program enable 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
15.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'
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'
16.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
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.
17 .Checking the incompatibilities between the programs
The below query can be used to find all incompatibilities in an application instance.
17 .Checking the incompatibilities between the programs
The below query can be used to find all incompatibilities in an application instance.
SELECT
a2.application_name, a1.user_concurrent_program_name,
DECODE (running_type,
'P', 'Program',
'S', 'Request set',
'UNKNOWN'
) "Type",
b2.application_name "Incompatible App",
b1.user_concurrent_program_name "Incompatible_Prog",
DECODE (to_run_type,
'P', 'Program',
'S', 'Request set',
'UNKNOWN'
) incompatible_type
FROM apps.fnd_concurrent_program_serial cps,
apps.fnd_concurrent_programs_tl a1,
apps.fnd_concurrent_programs_tl b1,
apps.fnd_application_tl a2,
apps.fnd_application_tl b2
WHERE a1.application_id = cps.running_application_id
AND a1.concurrent_program_id = cps.running_concurrent_program_id
AND a2.application_id = cps.running_application_id
AND b1.application_id = cps.to_run_application_id
AND b1.concurrent_program_id = cps.to_run_concurrent_program_id
AND b2.application_id = cps.to_run_application_id
AND a1.language = 'US'
AND a2.language = 'US'
AND b1.language = 'US'
AND b2.language = 'US'
DECODE (running_type,
'P', 'Program',
'S', 'Request set',
'UNKNOWN'
) "Type",
b2.application_name "Incompatible App",
b1.user_concurrent_program_name "Incompatible_Prog",
DECODE (to_run_type,
'P', 'Program',
'S', 'Request set',
'UNKNOWN'
) incompatible_type
FROM apps.fnd_concurrent_program_serial cps,
apps.fnd_concurrent_programs_tl a1,
apps.fnd_concurrent_programs_tl b1,
apps.fnd_application_tl a2,
apps.fnd_application_tl b2
WHERE a1.application_id = cps.running_application_id
AND a1.concurrent_program_id = cps.running_concurrent_program_id
AND a2.application_id = cps.running_application_id
AND b1.application_id = cps.to_run_application_id
AND b1.concurrent_program_id = cps.to_run_concurrent_program_id
AND b2.application_id = cps.to_run_application_id
AND a1.language = 'US'
AND a2.language = 'US'
AND b1.language = 'US'
AND b2.language = 'US'
The table apps.fnd_concurrent_program_serial has the information
about incompatibilities.
18 .How to find if module is installed or
not in Oracle Apps
We have 3 ways to find out if a module is installed in oracle apps
1 We can run the following script
cd $AD_TOP/sql/adutconf.sql
In this script, there is a section->"Product Installation Status, Version Info and Patch Level"
In this, status of installed means the product is installed.
2 Through OAM
In oracle apps, navigate to
OAM>Site Map>/License Manager>Reports>Licensed Products
Here filter the products by using "Licensed". These are the licensed and installed products in oracle apps.
3 Using a sql query->
We can use the following query
SELECT a.application_name,a.product_code,
DECODE (b.status, 'I', 'Installed', 'S', 'Shared', 'N/A') status,
patch_level
FROM apps.fnd_application_vl a,
apps.fnd_product_installations b
WHERE a.application_id = b.application_id
and b.status='I'
order by product_code asc;
19.How to Find out Product Installations and Patch Set level in Oracle Applications 11i ?
Use Following Query to find out Product Installation
SELECT application_name "Application Name",
SUBSTR (application_short_name, 1, 10) "Short Name",
RPAD (DECODE (fpi.status, 'I', 'Installed', 'S', 'Shared Install', 'N', 'Not Installed' ), 14, ' ' ) "Install Status", SUBSTR (patch_level, 1, 12) "Patch Level", fa.BASEPATH "Basepath"
FROM fnd_product_installations fpi,
fnd_application fa,
fnd_application_tl fat
WHERE fa.application_id = fpi.application_id
AND fa.application_id = fat.application_id
ORDER BY fpi.application_id;
SELECT application_name,SUBSTR (application_short_name, 1, 10) "Short Name",SUBSTR (patch_level, 1, 12) "Patch Level", fa.BASEPATH "Basepath" FROM fnd_product_installations fpi,fnd_application fa,fnd_application_tl fat
WHERE fa.application_id = fpi.application_id AND fa.application_id = fat.application_id order by fa.BASEPATH;
Select product_version,patch_level from fnd_product_installations where patch_level like '%AP%';
select BASEPATH from fnd_application order by BASEPATH;
20. How to Find Environment Variables from database table -
Oracle E-Business Suite
Are you running Oracle E-Business Suite (EBS) / Applications and
want to get an operating system level environment variable value from a
database table, for example for use in PL/SQL? Or perhaps to default a
concurrent program parameter? Didn't think environment variables were stored in
the database?
Try out out this query that shows you $FND_TOP:
Try out out this query that shows you $FND_TOP:
select value
from fnd_env_context
where variable_name =
'FND_TOP'
and
concurrent_process_id =
( select
max(concurrent_process_id) from fnd_env_context );
VALUE
--------------------------------------------------------------------------------
/d01/oracle/VIS/apps/apps_st/appl/fnd/12.0.0
-------------------------------------------------------------------------------
Or
Did you want to find out the Product "TOP" directories e.g the full directory path values from fnd_appl_tops under APPL_TOP?
Did you want to find out the Product "TOP" directories e.g the full directory path values from fnd_appl_tops under APPL_TOP?
col variable_name format a15
col value format a64
select variable_name, value
from fnd_env_context
where variable_name like
'%\_TOP' escape '\'
and
concurrent_process_id =
( select
max(concurrent_process_id) from fnd_env_context )
order by 1;
VARIABLE_NAME VALUE
---------------
----------------------------------------------------------------
AD_TOP /d01/oracle/VIS/apps/apps_st/appl/ad/12.0.0
AF_JRE_TOP
/d01/oracle/VIS/apps/tech_st/10.1.3/appsutil/jdk/jre
AHL_TOP
/d01/oracle/VIS/apps/apps_st/appl/ahl/12.0.0
AK_TOP
/d01/oracle/VIS/apps/apps_st/appl/ak/12.0.0
ALR_TOP /d01/oracle/VIS/apps/apps_st/appl/alr/12.0.0
AME_TOP
/d01/oracle/VIS/apps/apps_st/appl/ame/12.0.0
AMS_TOP
/d01/oracle/VIS/apps/apps_st/appl/ams/12.0.0
AMV_TOP
/d01/oracle/VIS/apps/apps_st/appl/amv/12.0.0
AMW_TOP
/d01/oracle/VIS/apps/apps_st/appl/amw/12.0.0
APPL_TOP
/d01/oracle/VIS/apps/apps_st/appl
AP_TOP
/d01/oracle/VIS/apps/apps_st/appl/ap/12.0.0
AR_TOP
/d01/oracle/VIS/apps/apps_st/appl/ar/12.0.0
...
21.How to find the full directory path to $APPLTMP?
select value
from fnd_env_context
where variable_name =
'APPLTMP'
and
concurrent_process_id =
( select
max(concurrent_process_id) from fnd_env_context );
VALUE
--------------------------------------------------------------------------------
/d01/oracle/VIS/inst/apps/VIS_demo/appltmp
NB: These queries assume your concurrent managers are
running!
22. Script to Get OS user name with terminal name
REM: Script to Get Os user name with terminal name
REM:*****************************************
REM: NOTE: PLEASE TEST THIS SCRIPT BEFORE USE.
REM: Author will not be responsible for any damage that may be cause by this script.
REM:*****************************************
SELECT
DBA_USERS.USERNAME USERNAME,
DECODE(V$SESSION.USERNAME, NULL, 'NOT CONNECTED', 'CONNECTED') STATUS,
NVL(OSUSER, '-') OSUSER,
NVL(TERMINAL,'-') TERMINAL,
SUM(DECODE(V$SESSION.USERNAME, NULL, 0,1)) SESSIONS
FROM
DBA_USERS, V$SESSION
WHERE DBA_USERS.USERNAME = V$SESSION.USERNAME (+)
GROUP BY
DBA_USERS.USERNAME,
DECODE(V$SESSION.USERNAME, NULL, 'NOT CONNECTED', 'CONNECTED'),
OSUSER,
TERMINAL
ORDER BY 1 ;
REM:*****************************************
REM: NOTE: PLEASE TEST THIS SCRIPT BEFORE USE.
REM: Author will not be responsible for any damage that may be cause by this script.
REM:*****************************************
SELECT
DBA_USERS.USERNAME USERNAME,
DECODE(V$SESSION.USERNAME, NULL, 'NOT CONNECTED', 'CONNECTED') STATUS,
NVL(OSUSER, '-') OSUSER,
NVL(TERMINAL,'-') TERMINAL,
SUM(DECODE(V$SESSION.USERNAME, NULL, 0,1)) SESSIONS
FROM
DBA_USERS, V$SESSION
WHERE DBA_USERS.USERNAME = V$SESSION.USERNAME (+)
GROUP BY
DBA_USERS.USERNAME,
DECODE(V$SESSION.USERNAME, NULL, 'NOT CONNECTED', 'CONNECTED'),
OSUSER,
TERMINAL
ORDER BY 1 ;
23 .Query to Identify the Concurrent
Program for Long time.....................
Step1 : Run the first query ,
this will list all the programs that currently running in Application. Take the
SID and use it in the second query.
SELECT
f.user_name
,a.request_id "Req Id"
,a.concurrent_program_id "Prg Id"
,a.RESPONSIBILITY_ID Responsibility
,a.phase_code,a.status_code
,b.os_process_id "OS"
,vs.sid
,vs.serial# "Serial#"
,vp.spid
,TO_CHAR(request_date,'DD-MON-YY hh24:mi:ss') request_date
,(NVL(a.actual_completion_date,SYSDATE)-a.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
,APPLSYS.fnd_user f
,v$session vs
,v$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 c2.application_id = c.application_id
AND a.phase_code IN ('I','P','R','T')
AND a.requested_by = f.user_id
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND c2.LANGUAGE = 'US'
AND a.oracle_process_id = vp.spid
AND vs.paddr = vp.addr
ORDER BY 9
f.user_name
,a.request_id "Req Id"
,a.concurrent_program_id "Prg Id"
,a.RESPONSIBILITY_ID Responsibility
,a.phase_code,a.status_code
,b.os_process_id "OS"
,vs.sid
,vs.serial# "Serial#"
,vp.spid
,TO_CHAR(request_date,'DD-MON-YY hh24:mi:ss') request_date
,(NVL(a.actual_completion_date,SYSDATE)-a.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
,APPLSYS.fnd_user f
,v$session vs
,v$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 c2.application_id = c.application_id
AND a.phase_code IN ('I','P','R','T')
AND a.requested_by = f.user_id
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND c2.LANGUAGE = 'US'
AND a.oracle_process_id = vp.spid
AND vs.paddr = vp.addr
ORDER BY 9
Step 2 : Get Sid from step1 and Keep on executing this query in SQL. This query will show the currently running SQL in the DB, as your concurrent is submitted and running. You can now find out the exact query ( select / insert / update ) which is actually taking time in your concurrent program.
SELECT sql_text FROM v$sqltext t,v$session s
WHERE t.ADDRESS = s.SQL_ADDRESS
AND t.HASH_VALUE = s.SQL_HASH_VALUE
AND s.sid = 100 – Get this value from step1
ORDER BY PIECE
Step 3 :
------------
The following query finds total run-time (in minutes) for a concurrent program. Thus, with a little modification to this query, you can track which concurrent programs take (very) long time to complete, and may need performance tuning.
Change the concurrent program name (tl.user_concurrent_program_name, see below) according to your search criteria. In this example, my concurrent program is "Autoinvoice Import Program". You can also uncomment the "&Start_Date" line to get the list for a specific date.
WHERE t.ADDRESS = s.SQL_ADDRESS
AND t.HASH_VALUE = s.SQL_HASH_VALUE
AND s.sid = 100 – Get this value from step1
ORDER BY PIECE
Step 3 :
------------
The following query finds total run-time (in minutes) for a concurrent program. Thus, with a little modification to this query, you can track which concurrent programs take (very) long time to complete, and may need performance tuning.
Change the concurrent program name (tl.user_concurrent_program_name, see below) according to your search criteria. In this example, my concurrent program is "Autoinvoice Import Program". You can also uncomment the "&Start_Date" line to get the list for a specific date.
-------------------------------------------------------------------------------
-- Query to find runtime
for a concurrent program
-------------------------------------------------------------------------------
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;
*************************************************************
24
.Script to reset Oracle Apps front end User ID Password
Here is a sample anonymous PL/SQL block which will reset
the Oracle Apps frontend password for a given user from backend
DECLARE
flag_value BOOLEAN;
BEGIN
flag_value :=
fnd_user_pkg.changepassword(username=> 'NKUMAR2'
,newpassword => 'welcome1');
IF flag_value
THEN
DBMS_OUTPU.PUT_LINE('The password reset successfully');
ELSE
DBMS_OUTPUT.PUT_LINE('The password reset has failed');
END IF;
END;
/
COMMIT;
25 .Checking the duplicated schedules of the same program with the same arguments
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.
DECLARE
flag_value BOOLEAN;
BEGIN
flag_value :=
fnd_user_pkg.changepassword(username=> 'NKUMAR2'
,newpassword => 'welcome1');
IF flag_value
THEN
DBMS_OUTPU.PUT_LINE('The password reset successfully');
ELSE
DBMS_OUTPUT.PUT_LINE('The password reset has failed');
END IF;
END;
/
COMMIT;
25 .Checking the duplicated schedules of the same program with the same arguments
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;
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;
26.Average pending time
per request
This is a very
useful query to check the performance of the concurrent managers.
Average pending time for a request is calculated like
below:
("Highest of Requested_start_date or Date_submitted" - Actual_start_date ) / Total requests
A Request can be in Pending state for variety of reasons like conflict with other requests, improperly tuned managers (sleep seconds / cache size / number of managers etc)
We can schedule this script to gather data regularly for historical analysis as we normally purge the concurrent requests regularly.
("Highest of Requested_start_date or Date_submitted" - Actual_start_date ) / Total requests
A Request can be in Pending state for variety of reasons like conflict with other requests, improperly tuned managers (sleep seconds / cache size / number of managers etc)
We can schedule this script to gather data regularly for historical analysis as we normally purge the concurrent requests regularly.
SELECT
TO_CHAR (actual_start_date, 'DD-MON-YYYY') DAY,
concurrent_queue_name,
(SUM ( ( actual_start_date
- (CASE
WHEN requested_start_date > request_date
THEN requested_start_date
ELSE request_date
END
)
)
* 24
* 60
* 60
)
)
/ COUNT (*) "Wait_Time_per_Req_in_Secs"
FROM apps.fnd_concurrent_requests cr,
apps.fnd_concurrent_processes fcp,
apps.fnd_concurrent_queues fcq
WHERE cr.phase_code = 'C'
AND cr.actual_start_date IS NOT NULL
AND cr.requested_start_date IS NOT NULL
AND cr.controlling_manager = fcp.concurrent_process_id
AND fcp.queue_application_id = fcq.application_id
AND fcp.concurrent_queue_id = fcq.concurrent_queue_id
GROUP BY TO_CHAR (actual_start_date, 'DD-MON-YYYY'), concurrent_queue_name
ORDER BY 2;
concurrent_queue_name,
(SUM ( ( actual_start_date
- (CASE
WHEN requested_start_date > request_date
THEN requested_start_date
ELSE request_date
END
)
)
* 24
* 60
* 60
)
)
/ COUNT (*) "Wait_Time_per_Req_in_Secs"
FROM apps.fnd_concurrent_requests cr,
apps.fnd_concurrent_processes fcp,
apps.fnd_concurrent_queues fcq
WHERE cr.phase_code = 'C'
AND cr.actual_start_date IS NOT NULL
AND cr.requested_start_date IS NOT NULL
AND cr.controlling_manager = fcp.concurrent_process_id
AND fcp.queue_application_id = fcq.application_id
AND fcp.concurrent_queue_id = fcq.concurrent_queue_id
GROUP BY TO_CHAR (actual_start_date, 'DD-MON-YYYY'), concurrent_queue_name
ORDER BY 2;
Note: Depending on the purging schedules some requests
might miss if the corresponding data in fnd_concurrent_processes is purged.
27.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;
28. 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
29.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
-------------------------------------------------------------------------------
-- Query to find
concurrent request status related information
-------------------------------------------------------------------------------
SELECT
fu.user_name
"User ID",
frt.responsibility_name
"Responsibility
Used",
fcr.request_id
"Request ID",
fcpt.user_concurrent_program_name
"Concurrent Program Name",
DECODE(fcr.phase_code,
'C', 'Completed',
'P', 'Pending',
'R', 'Running',
'I', 'Inactive',
fcr.phase_code)
"Phase",
DECODE(fcr.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',
fcr.status_code)
"Status",
fcr.request_date
"Request Date",
fcr.requested_start_date
"Request Start Date",
fcr.hold_flag
"Hold Flag",
fcr.printer
"Printer Name",
fcr.parent_request_id
"Parent Request ID"
-- fcr.number_of_arguments,
-- fcr.argument_text,
-- fcr.logfile_name,
-- fcr.outfile_name
FROM
fnd_user
fu,
fnd_responsibility_tl frt,
fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcpt
WHERE fu.user_id
= fcr.requested_by
AND fcr.concurrent_program_id =
fcpt.concurrent_program_id
AND fcr.responsibility_id =
frt.responsibility_id
AND frt.LANGUAGE
= USERENV('LANG')
AND fcpt.LANGUAGE
= USERENV('LANG')
-- AND
fcr.request_id = 7137350 -- <change it>
AND fcpt.user_concurrent_program_name = 'Autoinvoice Import Program' --<
change it>
ORDER BY fcr.request_date DESC;
30) CONCURRENT REQUEST MONITORING QUERIES
SQL TO FIND OUT CONCURRENT REQUESTS CURRENTLY RUNNING:
**********************************************************
set lines 180
set pages 1000
set verify off
undef spid
column req_id format 99999999999
column OPID format a10
column PPID format a8
column SPID format a8
column ST_CD format a1
column ph_cd format a1
column CNAME format a30
column event format a15
column user_name format a10
column program format a8
column serial# format 999999
column sid format 9999
column username format a8
select a.request_id "REQ_ID",a.oracle_process_id "OPID",a.os_process_id
"PPID",
e.user_concurrent_program_name "CNAME",
f.user_name,a.status_code "ST_CD",a.phase_code "PH_CD", b.username,b.sid,
b.serial#,b.program,g.event,
to_char(a.ACTUAL_START_DATE,'MON-DD-HH-MI-SS') START_DATE,
to_char(a.ACTUAL_COMPLETION_DATE,'MON-DD-HH-MI-SS') COMPL_DATE
from apps.fnd_concurrent_requests a,(select c.username,c.sid,c.serial#,
c.program,d.spid from v$session c, v$process d
where c.paddr=d.addr) b,
apps.fnd_concurrent_programs_tl e,
apps.fnd_user f,
v$session_wait g
where a.oracle_process_id=b.spid
and a.concurrent_program_id=e.concurrent_program_id
and e.language='US'
and a.requested_by=f.user_id
and b.sid=g.sid
and a.status_code='R'
and a.phase_code='R';
********************************************************
Retrieve a list of all responsibilities.
Parameters
None
*//
SELECT
(SELECT application_short_name
FROM fnd_application fa
WHERE fa.application_id = frt.application_id)
application
, frt.responsibility_id
, frt.responsibility_name
FROM apps.fnd_responsibility_tl frt;
//*
2. Menus Listing
Purpose/Description:
To see the Menus associated with a given responsibility
Parameters
responsibility_id that you can retrieve from query nr 1 (Responsibilities Listing)
*//
SELECT DISTINCT
a.responsibility_name
, c.user_menu_name
FROM
apps.fnd_responsibility_tl a
, apps.fnd_responsibility b
, apps.fnd_menus_tl c
, apps.fnd_menus d
, apps.fnd_application_tl e
, apps.fnd_application f
WHERE
a.responsibility_id(+) = b.responsibility_id
AND a.responsibility_id = 50103
AND b.menu_id = c.menu_id
AND b.menu_id = d.menu_id
AND e.application_id = f.application_id
AND f.application_id = b.application_id
AND a.LANGUAGE = ‘US’;
//*
3. Submenu And Function Listing
Purpose/Description:
By using this query you can check function and submenus attached to a specific menu
Parameters
User_menu_name that you can get by running query 2 (Menu Listing)
*//
SELECT
c.prompt
, c.description
FROM
apps.fnd_menus_tl a
, fnd_menu_entries_tl c
WHERE
a.menu_id = c.menu_id
AND a.user_menu_name = ‘Navigator Menu – System Administrator GUI’;
//*
4.User and Assigned Responsibility Listing
Purpose/Description:
You can use this query to check responsibilities assigned to users.
Parameters
None
*//
SELECT UNIQUE
u.user_id
, SUBSTR (u.user_name, 1, 30) user_name
, SUBSTR (r.responsibility_name, 1, 60) responsiblity
, SUBSTR (a.application_name, 1, 50) application
FROM
fnd_user u
, fnd_user_resp_groups g
, fnd_application_tl a
, fnd_responsibility_tl r
WHERE
g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
ORDER BY
SUBSTR (user_name, 1, 30)
, SUBSTR (a.application_name, 1, 50)
, SUBSTR (r.responsibility_name, 1, 60);
//*
5. Responsibility and assigned request group listing
Purpose/Description:
To find responsibility and assigned request groups.
Every responsibility contains a request group (The request group is basis of submitting requests)
Parameters
None
*//
SELECT
responsibility_name responsibility
, request_group_name
, frg.description
FROM
fnd_request_groups frg
, fnd_responsibility_vl frv
WHERE
frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name
//*
6. Profile option with modification date and user
Purpose/Description:
Query that can be used to audit profile options.
Parameters
None
*//
SELECT
t.user_profile_option_name
, profile_option_value
, v.creation_date
, v.last_update_date
, v.creation_date – v.last_update_date "Change Date"
, (SELECT UNIQUE user_name
FROM fnd_user
WHERE user_id = v.created_by) "Created By"
, (SELECT user_name
FROM fnd_user
WHERE user_id = v.last_updated_by) "Last Update By"
FROM
fnd_profile_options o
, fnd_profile_option_values v
, fnd_profile_options_tl t
WHERE
o.profile_option_id = v.profile_option_id
AND o.application_id = v.application_id
AND start_date_active <= SYSDATE
AND NVL (end_date_active, SYSDATE) >= SYSDATE
AND o.profile_option_name = t.profile_option_name
AND level_id = 10001
AND t.LANGUAGE IN (SELECT language_code
FROM fnd_languages
WHERE installed_flag = ‘B’
UNION
SELECT nls_language
FROM fnd_languages
WHERE installed_flag = ‘B’)
ORDER BY user_profile_option_name;
//*
7. Forms personalization Listing
Purpose/Description:
To get modified profile options.
Personalization is a feature available in 11.5.10.X.
Parameters
None
*//
SELECT
ffft.user_function_name "User Form Name"
, ffcr.SEQUENCE
, ffcr.description
, ffcr.rule_type
, ffcr.enabled
, ffcr.trigger_event
, ffcr.trigger_object
, ffcr.condition
, ffcr.fire_in_enter_query
, (SELECT user_name
FROM fnd_user fu
WHERE fu.user_id = ffcr.created_by) "Created By”
FROM
fnd_form_custom_rules ffcr
, fnd_form_functions_vl ffft
WHERE ffcr.ID = ffft.function_id
ORDER BY 1;
//*
8. Patch Level Listing
Purpose/Description:
Query that can be used to view the patch level status of all modules
Parameters
None
*//
SELECT
a.application_name
, DECODE (b.status, ‘I’, ‘Installed’, ‘S’, ‘Shared’, ‘N/A’) status
, patch_level
FROM
apps.fnd_application_vl a
, apps.fnd_product_installations b
WHERE
a.application_id = b.application_id;
//*
9. Request attached to responsibility listing
Purpose/Description:
To see all requests attached to a responsibility
Parameters
None
*//
SELECT
responsibility_name
, frg.request_group_name
, fcpv.user_concurrent_program_name
, fcpv.description
FROM
fnd_request_groups frg
, fnd_request_group_units frgu
, fnd_concurrent_programs_vl fcpv
, fnd_responsibility_vl frv
WHERE
frgu.request_unit_type = ‘P’
AND frgu.request_group_id = frg.request_group_id
AND frgu.request_unit_id = fcpv.concurrent_program_id
AND frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name;
//*
10. Request listing application wise
Purpose/Description:
View all request types application wise
Parameters
None
*//
SELECT
fa.application_short_name
, fcpv.user_concurrent_program_name
, description
, DECODE (fcpv.execution_method_code
,’B', ‘Request Set Stage Function’
,’Q', ‘SQL*Plus’
,’H', ‘Host’
,’L', ‘SQL*Loader’
,’A', ‘Spawned’
,’I', ‘PL/SQL Stored Procedure’
,’P', ‘Oracle Reports’
,’S', ‘Immediate’
,fcpv.execution_method_code) exe_method
, output_file_type
, program_type
, printer_name
, minimum_width
, minimum_length
, concurrent_program_name
, concurrent_program_id
FROM
fnd_concurrent_programs_vl fcpv
, fnd_application fa
WHERE
fcpv.application_id = fa.application_id
ORDER BY description
//*
11. Count Reports per module
Purpose/Description:
To Count Reports
Parameters
None
*//
SELECT
fa.application_short_name
, DECODE (fcpv.execution_method_code
,’B', ‘Request Set Stage Function’
,’Q', ‘SQL*Plus’
,’H', ‘Host’
,’L', ‘SQL*Loader’
,’A', ‘Spawned’
,’I', ‘PL/SQL Stored Procedure’
,’P', ‘Oracle Reports’
,’S', ‘Immediate’
,fcpv.execution_method_code) exe_method
, COUNT (concurrent_program_id) COUNT
FROM
fnd_concurrent_programs_vl fcpv
, fnd_application fa
WHERE
fcpv.application_id = fa.application_id
GROUP BY
fa.application_short_name
, fcpv.execution_method_code
ORDER BY 1;
//*
12. Request Status Listing
Purpose/Description:
This query returns report/request processing time
Parameters
None
*//
SELECT
f.request_id
, pt.user_concurrent_program_name user_concurrent_program_name
, f.actual_start_date actual_start_date
, f.actual_completion_date actual_completion_date
, floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)
‘ HOURS ‘
floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)
‘ MINUTES ‘
round((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600 -
(floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)*60) ))
‘ SECS ‘ time_difference
, DECODE(p.concurrent_program_name
,’ALECDC’
,p.concurrent_program_name’['
f.description']‘
,p.concurrent_program_name) concurrent_program_name
, decode(f.phase_code
,’R',’Running’
,’C',’Complete’
,f.phase_code) Phase
, f.status_code
FROM
apps.fnd_concurrent_programs p
, apps.fnd_concurrent_programs_tl pt
, apps.fnd_concurrent_requests f
WHERE
f.concurrent_program_id = p.concurrent_program_id
AND f.program_application_id = p.application_id
AND f.concurrent_program_id = pt.concurrent_program_id
AND f.program_application_id = pt.application_id
AND pt.language = USERENV(‘Lang’)
AND f.actual_start_date is not null
ORDER by f.actual_completion_date-f.actual_start_date desc;
//*
13. User and responsibility listing
Purpose/Description:
Check responsibilities assigned to users
Parameters
None
*//
SELECT UNIQUE
u.user_id
, SUBSTR (u.user_name, 1, 30) user_name
, SUBSTR (r.responsibility_name, 1, 60) responsiblity
, SUBSTR (a.application_name, 1, 50) application
FROM
fnd_user u
, fnd_user_resp_groups g
, fnd_application_tl a
, fnd_responsibility_tl r
WHERE g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
–AND a.application_name like ‘%Order Man%’
ORDER BY SUBSTR (user_name, 1, 30),
SUBSTR (a.application_name, 1, 50),
SUBSTR (r.responsibility_name, 1, 60)
//*
14. Applied Patch Listing
Purpose/Description:
Check Current Applied Patches
Parameters
None
*//
SELECT
patch_name
, patch_type
, maint_pack_level
, creation_date
FROM applsys.ad_applied_patches
ORDER BY creation_date DESC
15. How to check the System Administarator responsiblity for the users in ORACLE EBS ?
set lines 2000
set pages 5000
col USER_NAME for a45
Select b.user_name, c.responsibility_name, a.START_DATE, a.END_DATE
from fnd_user_resp_groups_direct a, fnd_user b, fnd_responsibility_tl c
where a.user_id = b.user_id
and a.responsibility_id = c.responsibility_id
and c.responsibility_name='System Administrator'
Retrieve a list of all responsibilities.
Parameters
None
*//
SELECT
(SELECT application_short_name
FROM fnd_application fa
WHERE fa.application_id = frt.application_id)
application
, frt.responsibility_id
, frt.responsibility_name
FROM apps.fnd_responsibility_tl frt;
//*
2. Menus Listing
Purpose/Description:
To see the Menus associated with a given responsibility
Parameters
responsibility_id that you can retrieve from query nr 1 (Responsibilities Listing)
*//
SELECT DISTINCT
a.responsibility_name
, c.user_menu_name
FROM
apps.fnd_responsibility_tl a
, apps.fnd_responsibility b
, apps.fnd_menus_tl c
, apps.fnd_menus d
, apps.fnd_application_tl e
, apps.fnd_application f
WHERE
a.responsibility_id(+) = b.responsibility_id
AND a.responsibility_id = 50103
AND b.menu_id = c.menu_id
AND b.menu_id = d.menu_id
AND e.application_id = f.application_id
AND f.application_id = b.application_id
AND a.LANGUAGE = ‘US’;
//*
3. Submenu And Function Listing
Purpose/Description:
By using this query you can check function and submenus attached to a specific menu
Parameters
User_menu_name that you can get by running query 2 (Menu Listing)
*//
SELECT
c.prompt
, c.description
FROM
apps.fnd_menus_tl a
, fnd_menu_entries_tl c
WHERE
a.menu_id = c.menu_id
AND a.user_menu_name = ‘Navigator Menu – System Administrator GUI’;
//*
4.User and Assigned Responsibility Listing
Purpose/Description:
You can use this query to check responsibilities assigned to users.
Parameters
None
*//
SELECT UNIQUE
u.user_id
, SUBSTR (u.user_name, 1, 30) user_name
, SUBSTR (r.responsibility_name, 1, 60) responsiblity
, SUBSTR (a.application_name, 1, 50) application
FROM
fnd_user u
, fnd_user_resp_groups g
, fnd_application_tl a
, fnd_responsibility_tl r
WHERE
g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
ORDER BY
SUBSTR (user_name, 1, 30)
, SUBSTR (a.application_name, 1, 50)
, SUBSTR (r.responsibility_name, 1, 60);
//*
5. Responsibility and assigned request group listing
Purpose/Description:
To find responsibility and assigned request groups.
Every responsibility contains a request group (The request group is basis of submitting requests)
Parameters
None
*//
SELECT
responsibility_name responsibility
, request_group_name
, frg.description
FROM
fnd_request_groups frg
, fnd_responsibility_vl frv
WHERE
frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name
//*
6. Profile option with modification date and user
Purpose/Description:
Query that can be used to audit profile options.
Parameters
None
*//
SELECT
t.user_profile_option_name
, profile_option_value
, v.creation_date
, v.last_update_date
, v.creation_date – v.last_update_date "Change Date"
, (SELECT UNIQUE user_name
FROM fnd_user
WHERE user_id = v.created_by) "Created By"
, (SELECT user_name
FROM fnd_user
WHERE user_id = v.last_updated_by) "Last Update By"
FROM
fnd_profile_options o
, fnd_profile_option_values v
, fnd_profile_options_tl t
WHERE
o.profile_option_id = v.profile_option_id
AND o.application_id = v.application_id
AND start_date_active <= SYSDATE
AND NVL (end_date_active, SYSDATE) >= SYSDATE
AND o.profile_option_name = t.profile_option_name
AND level_id = 10001
AND t.LANGUAGE IN (SELECT language_code
FROM fnd_languages
WHERE installed_flag = ‘B’
UNION
SELECT nls_language
FROM fnd_languages
WHERE installed_flag = ‘B’)
ORDER BY user_profile_option_name;
//*
7. Forms personalization Listing
Purpose/Description:
To get modified profile options.
Personalization is a feature available in 11.5.10.X.
Parameters
None
*//
SELECT
ffft.user_function_name "User Form Name"
, ffcr.SEQUENCE
, ffcr.description
, ffcr.rule_type
, ffcr.enabled
, ffcr.trigger_event
, ffcr.trigger_object
, ffcr.condition
, ffcr.fire_in_enter_query
, (SELECT user_name
FROM fnd_user fu
WHERE fu.user_id = ffcr.created_by) "Created By”
FROM
fnd_form_custom_rules ffcr
, fnd_form_functions_vl ffft
WHERE ffcr.ID = ffft.function_id
ORDER BY 1;
//*
8. Patch Level Listing
Purpose/Description:
Query that can be used to view the patch level status of all modules
Parameters
None
*//
SELECT
a.application_name
, DECODE (b.status, ‘I’, ‘Installed’, ‘S’, ‘Shared’, ‘N/A’) status
, patch_level
FROM
apps.fnd_application_vl a
, apps.fnd_product_installations b
WHERE
a.application_id = b.application_id;
//*
9. Request attached to responsibility listing
Purpose/Description:
To see all requests attached to a responsibility
Parameters
None
*//
SELECT
responsibility_name
, frg.request_group_name
, fcpv.user_concurrent_program_name
, fcpv.description
FROM
fnd_request_groups frg
, fnd_request_group_units frgu
, fnd_concurrent_programs_vl fcpv
, fnd_responsibility_vl frv
WHERE
frgu.request_unit_type = ‘P’
AND frgu.request_group_id = frg.request_group_id
AND frgu.request_unit_id = fcpv.concurrent_program_id
AND frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name;
//*
10. Request listing application wise
Purpose/Description:
View all request types application wise
Parameters
None
*//
SELECT
fa.application_short_name
, fcpv.user_concurrent_program_name
, description
, DECODE (fcpv.execution_method_code
,’B', ‘Request Set Stage Function’
,’Q', ‘SQL*Plus’
,’H', ‘Host’
,’L', ‘SQL*Loader’
,’A', ‘Spawned’
,’I', ‘PL/SQL Stored Procedure’
,’P', ‘Oracle Reports’
,’S', ‘Immediate’
,fcpv.execution_method_code) exe_method
, output_file_type
, program_type
, printer_name
, minimum_width
, minimum_length
, concurrent_program_name
, concurrent_program_id
FROM
fnd_concurrent_programs_vl fcpv
, fnd_application fa
WHERE
fcpv.application_id = fa.application_id
ORDER BY description
//*
11. Count Reports per module
Purpose/Description:
To Count Reports
Parameters
None
*//
SELECT
fa.application_short_name
, DECODE (fcpv.execution_method_code
,’B', ‘Request Set Stage Function’
,’Q', ‘SQL*Plus’
,’H', ‘Host’
,’L', ‘SQL*Loader’
,’A', ‘Spawned’
,’I', ‘PL/SQL Stored Procedure’
,’P', ‘Oracle Reports’
,’S', ‘Immediate’
,fcpv.execution_method_code) exe_method
, COUNT (concurrent_program_id) COUNT
FROM
fnd_concurrent_programs_vl fcpv
, fnd_application fa
WHERE
fcpv.application_id = fa.application_id
GROUP BY
fa.application_short_name
, fcpv.execution_method_code
ORDER BY 1;
//*
12. Request Status Listing
Purpose/Description:
This query returns report/request processing time
Parameters
None
*//
SELECT
f.request_id
, pt.user_concurrent_program_name user_concurrent_program_name
, f.actual_start_date actual_start_date
, f.actual_completion_date actual_completion_date
, floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)
‘ HOURS ‘
floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)
‘ MINUTES ‘
round((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600 -
(floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)*60) ))
‘ SECS ‘ time_difference
, DECODE(p.concurrent_program_name
,’ALECDC’
,p.concurrent_program_name’['
f.description']‘
,p.concurrent_program_name) concurrent_program_name
, decode(f.phase_code
,’R',’Running’
,’C',’Complete’
,f.phase_code) Phase
, f.status_code
FROM
apps.fnd_concurrent_programs p
, apps.fnd_concurrent_programs_tl pt
, apps.fnd_concurrent_requests f
WHERE
f.concurrent_program_id = p.concurrent_program_id
AND f.program_application_id = p.application_id
AND f.concurrent_program_id = pt.concurrent_program_id
AND f.program_application_id = pt.application_id
AND pt.language = USERENV(‘Lang’)
AND f.actual_start_date is not null
ORDER by f.actual_completion_date-f.actual_start_date desc;
//*
13. User and responsibility listing
Purpose/Description:
Check responsibilities assigned to users
Parameters
None
*//
SELECT UNIQUE
u.user_id
, SUBSTR (u.user_name, 1, 30) user_name
, SUBSTR (r.responsibility_name, 1, 60) responsiblity
, SUBSTR (a.application_name, 1, 50) application
FROM
fnd_user u
, fnd_user_resp_groups g
, fnd_application_tl a
, fnd_responsibility_tl r
WHERE g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
–AND a.application_name like ‘%Order Man%’
ORDER BY SUBSTR (user_name, 1, 30),
SUBSTR (a.application_name, 1, 50),
SUBSTR (r.responsibility_name, 1, 60)
//*
14. Applied Patch Listing
Purpose/Description:
Check Current Applied Patches
Parameters
None
*//
SELECT
patch_name
, patch_type
, maint_pack_level
, creation_date
FROM applsys.ad_applied_patches
ORDER BY creation_date DESC
15. How to check the System Administarator responsiblity for the users in ORACLE EBS ?
set lines 2000
set pages 5000
col USER_NAME for a45
Select b.user_name, c.responsibility_name, a.START_DATE, a.END_DATE
from fnd_user_resp_groups_direct a, fnd_user b, fnd_responsibility_tl c
where a.user_id = b.user_id
and a.responsibility_id = c.responsibility_id
and c.responsibility_name='System Administrator'
SQL Query to Find Scheduled
Concurrent Programs
By running below sql query in
toad or sql develper, you can get list of scheduled concurrent requests.
SELECT fcr.request_id
, fcpt.user_concurrent_program_name|| NVL2(fcr.description, ' (' || fcr.description || ')', NULL) conc_prog
, fu.user_name requestor
, fu.description requested_by
, fu.email_address
, frt.responsibility_name requested_by_resp
, trim(fl.meaning) status
, fcr.phase_code
, fcr.status_code
, fcr.argument_text "PARAMETERS"
, TO_CHAR(fcr.request_date, 'DD-MON-YYYY HH24:MI:SS') requested
, TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS') requested_start
, TO_CHAR((fcr.requested_start_date), 'HH24:MI:SS') start_time
, DECODE(fcr.hold_flag, 'Y', 'Yes', 'N', 'No') on_hold
, CASE
WHEN fcr.hold_flag = 'Y'
Then Substr(
fu.description
, 0
, 40
)
END last_update_by
, CASE
WHEN fcr.hold_flag = 'Y'
THEN fcr.last_update_date
END last_update_date
, fcr.increment_dates
, CASE WHEN fcrc.CLASS_INFO IS NULL THEN
'Yes: ' || TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS')
ELSE
'n/a'
END run_once
, CASE WHEN fcrc.class_type = 'P' THEN
'Repeat every ' ||
substr(fcrc.class_info, 1, instr(fcrc.class_info, ':') - 1) ||
decode(substr(fcrc.class_info, instr(fcrc.class_info, ':', 1, 1) + 1, 1),
'N', ' minutes',
'M', ' months',
'H', ' hours',
'D', ' days') ||
decode(substr(fcrc.class_info, instr(fcrc.class_info, ':', 1, 2) + 1, 1),
'S', ' from the start of the prior run',
'C', ' from the completion of the prior run')
ELSE
'n/a'
END set_days_of_week
, CASE WHEN fcrc.class_type = 'S' AND instr(substr(fcrc.class_info, 33),'1',1) > 0 THEN
'Days of week: ' ||
decode(substr(fcrc.class_info, 33, 1), '1', 'Sun, ') ||
decode(substr(fcrc.class_info, 34, 1), '1', 'Mon, ') ||
decode(substr(fcrc.class_info, 35, 1), '1', 'Tue, ') ||
decode(substr(fcrc.class_info, 36, 1), '1', 'Wed, ') ||
decode(substr(fcrc.class_info, 37, 1), '1', 'Thu, ') ||
decode(substr(fcrc.class_info, 38, 1), '1', 'Fri, ') ||
decode(substr(fcrc.class_info, 39, 1), '1', 'Sat ')
ELSE
'n/a'
end days_of_week
FROM fnd_concurrent_requests fcr
, fnd_user fu
, fnd_concurrent_programs fcp
, fnd_concurrent_programs_tl fcpt
, fnd_printer_styles_tl fpst
, fnd_conc_release_classes fcrc
, fnd_responsibility_tl frt
, fnd_lookups fl
WHERE fcp.application_id = fcpt.application_id
AND fcr.requested_by = fu.user_id
AND fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcr.program_application_id = fcp.application_id
AND fcr.concurrent_program_id = fcpt.concurrent_program_id
AND fcr.responsibility_id = frt.responsibility_id
AND fcr.print_style = fpst.printer_style_name(+)
AND fcr.release_class_id = fcrc.release_class_id(+)
AND fcr.status_code = fl.lookup_code
AND fl.lookup_type = 'CP_STATUS_CODE'
AND fcr.phase_code = 'P'
AND 1=1
Order By Fu.Description, Fcr.Requested_Start_Date Asc
SELECT fcr.request_id
, fcpt.user_concurrent_program_name|| NVL2(fcr.description, ' (' || fcr.description || ')', NULL) conc_prog
, fu.user_name requestor
, fu.description requested_by
, fu.email_address
, frt.responsibility_name requested_by_resp
, trim(fl.meaning) status
, fcr.phase_code
, fcr.status_code
, fcr.argument_text "PARAMETERS"
, TO_CHAR(fcr.request_date, 'DD-MON-YYYY HH24:MI:SS') requested
, TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS') requested_start
, TO_CHAR((fcr.requested_start_date), 'HH24:MI:SS') start_time
, DECODE(fcr.hold_flag, 'Y', 'Yes', 'N', 'No') on_hold
, CASE
WHEN fcr.hold_flag = 'Y'
Then Substr(
fu.description
, 0
, 40
)
END last_update_by
, CASE
WHEN fcr.hold_flag = 'Y'
THEN fcr.last_update_date
END last_update_date
, fcr.increment_dates
, CASE WHEN fcrc.CLASS_INFO IS NULL THEN
'Yes: ' || TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS')
ELSE
'n/a'
END run_once
, CASE WHEN fcrc.class_type = 'P' THEN
'Repeat every ' ||
substr(fcrc.class_info, 1, instr(fcrc.class_info, ':') - 1) ||
decode(substr(fcrc.class_info, instr(fcrc.class_info, ':', 1, 1) + 1, 1),
'N', ' minutes',
'M', ' months',
'H', ' hours',
'D', ' days') ||
decode(substr(fcrc.class_info, instr(fcrc.class_info, ':', 1, 2) + 1, 1),
'S', ' from the start of the prior run',
'C', ' from the completion of the prior run')
ELSE
'n/a'
END set_days_of_week
, CASE WHEN fcrc.class_type = 'S' AND instr(substr(fcrc.class_info, 33),'1',1) > 0 THEN
'Days of week: ' ||
decode(substr(fcrc.class_info, 33, 1), '1', 'Sun, ') ||
decode(substr(fcrc.class_info, 34, 1), '1', 'Mon, ') ||
decode(substr(fcrc.class_info, 35, 1), '1', 'Tue, ') ||
decode(substr(fcrc.class_info, 36, 1), '1', 'Wed, ') ||
decode(substr(fcrc.class_info, 37, 1), '1', 'Thu, ') ||
decode(substr(fcrc.class_info, 38, 1), '1', 'Fri, ') ||
decode(substr(fcrc.class_info, 39, 1), '1', 'Sat ')
ELSE
'n/a'
end days_of_week
FROM fnd_concurrent_requests fcr
, fnd_user fu
, fnd_concurrent_programs fcp
, fnd_concurrent_programs_tl fcpt
, fnd_printer_styles_tl fpst
, fnd_conc_release_classes fcrc
, fnd_responsibility_tl frt
, fnd_lookups fl
WHERE fcp.application_id = fcpt.application_id
AND fcr.requested_by = fu.user_id
AND fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcr.program_application_id = fcp.application_id
AND fcr.concurrent_program_id = fcpt.concurrent_program_id
AND fcr.responsibility_id = frt.responsibility_id
AND fcr.print_style = fpst.printer_style_name(+)
AND fcr.release_class_id = fcrc.release_class_id(+)
AND fcr.status_code = fl.lookup_code
AND fl.lookup_type = 'CP_STATUS_CODE'
AND fcr.phase_code = 'P'
AND 1=1
Order By Fu.Description, Fcr.Requested_Start_Date Asc
prompt -- Updating any Running or
Terminating requests to Completed/Error
set feedback off
set head on
SELECT request_id request,
phase_code pcode,
status_code scode
FROM fnd_concurrent_requests
WHERE status_code = 'T' OR
phase_code = 'R'
ORDER BY request_id;
set feedback on
set head off
UPDATE fnd_concurrent_requests
SET phase_code = 'C', status_code =
'E'
WHERE status_code ='T' OR phase_code
= 'R';
Restart icm/sm/crm.
cmclean.sql
internal job
Update
process status codes to TERMINATED
Updating invalid process status codes in FND_CONCURRENT_PROCESSES
Set all managers to 0 processes
prompt -- Updating running processes in FND_CONCURRENT_QUEUES
prompt -- Setting running_processes = 0 and max_processes = 0 for all managers
Reset control codes
Updating invalid control_codes in FND_CONCURRENT_QUEUES
Also null out target_node for all managers
REM Set all 'Terminating' requests to Completed/Error
REM Also set Running requests to completed, since the managers are down
Updating any Running or Terminating requests to Completed/Error
Set all Runalone flags to 'N'
Updating invalid process status codes in FND_CONCURRENT_PROCESSES
Set all managers to 0 processes
prompt -- Updating running processes in FND_CONCURRENT_QUEUES
prompt -- Setting running_processes = 0 and max_processes = 0 for all managers
Reset control codes
Updating invalid control_codes in FND_CONCURRENT_QUEUES
Also null out target_node for all managers
REM Set all 'Terminating' requests to Completed/Error
REM Also set Running requests to completed, since the managers are down
Updating any Running or Terminating requests to Completed/Error
Set all Runalone flags to 'N'
Oracle
supplies several useful scripts, (located in $FND_TOP/sql directory), for
monitoring the concurrent managers:
afcmstat.sql
: Displays all the defined managers, their maximum capacity, pids, and their
status.
afimchk.sql
: Displays the status of ICM and PMON method in effect, the ICM's log file, and
determines if the concurrent manger monitor is running.
afcmcreq.sql
: Displays the concurrent manager and the name of its log file that processed a
request.
afrqwait.sql
: Displays the requests that are pending, held, and scheduled.
afrqstat.sql
: Displays of summary of concurrent request execution time and status since a
particular date.
afqpmrid.sql
: Displays the operating system process id of the FNDLIBR process based on a
concurrent request id. The process id can then be used with the ORADEBUG
utility.
afimlock.sql
: Displays the process id, terminal, and process id that may be causing locks
that the ICM and CRM are waiting to get. You should run this script if there
are long delays when submitting jobs, or if you suspect the ICM is in a gridlock
with another oracle
Something
about Concurrent Managers
Concurrent
Manager and program related scripts
SQL Script to Troubleshoot a long-running concurrent request
set term on
set feedback on
set echo on
set arraysize 4
set linesize 200
set pages 9999
set underline =;
column username format A15
column sid format 9990 heading SID
column type format A4
column lmode format 990 heading 'HELD'
column request format 990 heading 'REQ'
column id1 format 9999990
column id2 format 9999990
column sql_text format a100
column name format a80
break on id1 skip 1 dup
undefine v_request_id
define v_request_id
undefine v_spid
define v_spid
undefine v_sid
define v_sid
spool vj_concurrent_monitor.lst
Prompt Enter the concurrent_request_id
Accept v_request_id
prompt checking requests
select oracle_process_id from fnd_concurrent_requests where request_id='&v_request_id';
Prompt Enter the operating system oracle process id for this concurrent request
accept v_spid
Prompt Getting the sid
SELECT SID,SERIAL#,LOGON_TIME FROM V$SESSION WHERE PADDR IN
(SELECT ADDR FROM V$PROCESS WHERE SPID='&v_spid');
prompt Enter the session id for this concurrent request
accept v_sid
prompt memory usage for this session
SELECT A.SID,A.USERNAME,B.VALUE,c.name FROM V$SESSION a,V$SESSTAT B,V$STATNAME C WHERE A.SID=B.SID
AND B.STATISTIC#=C.STATISTIC# AND C.NAME like'%memor%' and a.sid='&v_sid';
prompt resource usage for this session
SELECT A.SID,A.USERNAME,B.VALUE,c.name FROM V$SESSION a,V$SESSTAT B,V$STATNAME C WHERE A.SID=B.SID
AND B.STATISTIC#=C.STATISTIC# and a.sid='&v_sid' order by b.value;
prompt this session waited on
select sid,event,wait_time,state from v$session_wait where sid='&v_sid' order by wait_time;
prompt current sql executing by this session
select a.sid,b.sorts,b.executions,b.loads,b.parse_calls,b.disk_reads,
b.buffer_gets,b.rows_processed,C.sql_text from v$session a,v$sqlarea b,V$SQLTEXT C
where a.sql_address=b.address and b.address=c.address and a.sid='&v_sid';
prompt sql which is taking more than 3mb in shared pool
prompt nosql should take morethan 1mb in shared pool.
prompt please ask the developers to tune the following sql statements
select name,
namespace,type,sharable_mem/(1024*1024) sharablemem,loads,executions,locks,pins,kept from v$db_object_cache
where SHARABLE_MEM>3000000;
prompt sort segments using by this session
SELECT s.username,s.sid,s.osuser,s.process,s.machine,u.extents, u.blocks,u.tablespace FROM v$session s, v$sort_usage u
WHERE s.saddr=u.session_addr order by extents;
and s.sid='&v_sid';
prompt current temp segments free in this instance
SELECT tablespace_name, extent_size, total_extents, used_extents, free_extents, max_used_size FROM v$sort_segment;
prompt total system events at this time
select event,total_waits waits, total_timeouts timeouts, time_waited total_time from v$system_event order by total_waits;
prompt latch contention if thery is any
SELECT latch#, name, gets, misses, sleeps FROM v$latch WHERE sleeps>0 ORDER BY sleeps ;
prompt the latch which is sleeping
select name, sleeps,latch# from v$latch_children where sleeps>4 order by sleeps;
spool off
clear columns
clear breaks
How to find out which request is handle by which concurrent queue.
a) First find out short_name of a program and then pass it as parameter to below query.
b) The below query will give you output
I - Included - Included in new concurrent queue
E - excluded from Standard Manager
This way you know now this running program (concurrent request) is handled by new manager and not part of standard manager.
SELECT A.INCLUDE_FLAG, A.QUEUE_APPLICATION_ID, C.USER_CONCURRENT_QUEUE_NAME,
B.CONCURRENT_PROGRAM_NAME
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 b.concurrent_program_name = ‘&SHORT_NAME’ and c.concurrent_queue_id = a.concurrent_queue_id
How to find out Summary of Concurrent requests.
SELECT
request_id, SUBSTR(requestor,1,25), SUBSTR(program,1,50), SUBSTR(user_concurrent_program_name,1,100),
TO_CHAR(actual_start_date,’dd/mm/yy :hh24:mi’) start_date,
TO_CHAR(actual_completion_date,’dd/mm/yy :hh24:mi’) completion_date,
FLOOR((ACTUAL_COMPLETION_DATE- ACTUAL_START_DATE)*24) “in Hours”,
(((ACTUAL_COMPLETION_DATE- ACTUAL_START_DATE)*24)-(FLOOR((ACTUAL_COMPLETION_DATE- ACTUAL_START_DATE)*24)))*60 “In_Min”
–requestor, program, user_concurrent_program_name
FROM fnd_conc_req_summary_v
WHERE (ACTUAL_COMPLETION_DATE- ACTUAL_START_DATE)*24*60 >10
How to find database SID from a Concurrent request.
column process heading “FNDLIBR PID”
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$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’;
You need your concurrent request ID as an input.
c.SPID= is the operating system process id
d.sid= is the Oracle process id
Cancel Concurrent requests. We don't need concurrent requests which are scheduled in production to keep running in test. We use the following update to cancel them.
update fnd_concurrent_requests
set phase_code='C',
status_code='D'
where phase_code = 'P'
and concurrent_program_id not in (
select concurrent_program_id
from fnd_concurrent_programs_tl
where user_concurrent_program_name like '%Synchronize%tables%'
or user_concurrent_program_name like '%Workflow%Back%'
or user_concurrent_program_name like '%Sync%responsibility%role%'
or user_concurrent_program_name like '%Workflow%Directory%')
and (status_code = 'I' OR status_code = 'Q');
Use the SQL below to only cancel the running requests connecting as sys
UPDATE applsys.fnd_concurrent_requests
SET phase_code = 'C', status_code = 'X'
WHERE phase_code = 'R' and status_code ='R'
/
commit
Also, please put all Pending Jobs on Hold, using the SQL below connecting as sys
update applsys.fnd_concurrent_requests
set hold_flag='Y' where
phase_code='P' and hold_flag='N'
/
commit
update fnd_concurrent_requests fcr
set phase_code = 'C',
status_code = 'D'
where fcr.PHASE_CODE <> 'C'
and (fcr.program_application_id,fcr.CONCURRENT_PROGRAM_id) in
(select fcp.application_id,fcp.concurrent_program_id from fnd_concurrent_programs fcp,fnd_executables_vl fev
where fcp.executable_application_id=fev.application_id and fcp.executable_id=fev.executable_id
and (upper(fev.user_executable_name) like 'AL%MAIL%'
or upper(fev.user_executable_name) like 'AL%FTP%'
or upper(fev.user_executable_name) like 'AL%EXCEL%'))
/
To change the number of processes for the standard manager
update FND_CONCURRENT_QUEUE_SIZE
set min_processes = 4
where concurrent_queue_id = 0;
how to find params passed to request from backend
select CONCURRENT_PROGRAM_ID,CONCURRENT_PROGRAM_NAME from fnd_concurrent_programs where concurrent_program_name like '';
select REQUEST_ID,CONCURRENT_PROGRAM_ID,substr(ARGUMENT_TEXT,1,60)params,status_code,phase_code from fnd_concurrent_requests where CONCURRENT_PROGRAM_ID=;
To increase the jvm for OPP
"From Note ID 737311.1 we need to do step 3 We are changing this parameter to 2048m as mentioned below in script.
Configure the Output Post Processor's JVM. These steps set the JVM to 2GB, depending upon
your server's size you might find 3 GB (-mx3072m), 4GB (-mx4096m) or even 5GB (-mx5120m) is a better value. This setting prevents the error ""java.lang.OutOfMemoryError: Java heap space""
in the Output Post Processor's log associated to the Subledger Accounting Program.
Login to SQL*Plus as APPS.
SQL>update FND_CP_SERVICES set DEVELOPER_PARAMETERS =
'J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx2048m'
where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');
Bounce the concurrent managers.
one more eg:
To determine current heap size:
select DEVELOPER_PARAMETERS
from FND_CP_SERVICES
where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');
To increase heap size to 1024:
update FND_CP_SERVICES
set DEVELOPER_PARAMETERS = 'J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx1024m'
where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');
Query to find pending concurrent requests
select count(*)
from APPS.FND_ 2 CONCURRENT_PROGRAMS_VL a,
APPS.FND_CONCURRENT_REQUESTS b
where a.CONCURRENT_PROGRAM_ID = b.CONCURRENT_PROGRAM_ID
and a.APPLICATION_ID = b.PROGRAM_APPLICATION_ID
and b.PHASE_CODE = 'P'
and b.requested_start_date <= sysdate
Pending job details
"SELECT c.user_name, request_id, phase_code, status_code, hold_flag,
TO_CHAR(requested_start_date,'DD-MON-YY:HH24:MM:SS') Requested_Start_Date,
user_concurrent_program_name, b.concurrent_program_id
FROM applsys.fnd_concurrent_requests a,
applsys.fnd_concurrent_programs_tl b,
applsys.fnd_user c
WHERE a.phase_code = 'P'
AND a.concurrent_program_id = b.concurrent_program_id
AND b.LANGUAGE = 'US'
AND c.user_id = a.requested_by
ORDER BY user_concurrent_program_name;"
Cancel scheduled concurrent Request “Gather Schema Statistics” sqlplus apps/apps
sql>
update fnd_concurrent_requests
set phase_code='C',status_code='D'
WHERE phase_code = 'P'
AND status_code in ('Q','I') and concurrent_program_id=38121;
Commit;
Exit
Putting all concurrent jobs on hold:
Update applsys.fnd_concurrent_requests set hold_flag='Y' where phase_code in ('R','P','I');
Cancel scheduled concurrent Request “Gather Schema Statistics”
sqlplus apps/apps
sql>
update fnd_concurrent_requests
set phase_code='C',status_code='D'
WHERE phase_code = 'P'
AND status_code in ('Q','I') and concurrent_program_id=38121;
Commit;
Exit
To terminate request from backend
SQL> select REQUEST_ID,ORACLE_ID,ORACLE_PROCESS_ID,ORACLE_SESSION_ID,OS_PROCESS_ID from applsys.FND_CONCURRENT_REQUESTS where REQUEST_ID=577945;
REQUEST_ID ORACLE_ID ORACLE_PROCESS_ID ORACLE_SESSION_ID
---------- ---------- ------------------------------ -----------------
OS_PROCESS_ID
------------------------------------------------------------------------------------------------------------------------------------
577945 900 13348 1242142
13299
SQL> !kill -9 13348
/bin/ksh: kill: 13348: No such process
SQL> update APPLSYS.fnd_Concurrent_requests set PHASE_CODE='C', STATUS_CODE='D' where REQUEST_ID=577945;
1 row updated.
SQL> commit;
Commit complete.
Schedule “Purge Concurrent Request and/or Manager Data”
Cancel existing scheduled request before scheduling new.
# ebappsenv
# sqlplus apps
update fnd_concurrent_requests
set phase_code='C',status_code='D'
WHERE phase_code = 'P'
AND status_code in ('Q','I')
and concurrent_program_id=32263;
Parameter:
Entity : REQUEST
Mode : Age
Mode Value : 15 à On non-prod environments as per case# 734780
Schedule:
on specific days : Wednesday and Saturday at 19:00 CET
Run Gather Schema Statistics as a Concurrent request
Cancel any pending jobs for “Gather Schema Statistics”
Sqlplus apps/appspasswd
Sql>
update fnd_concurrent_requests
set phase_code='C',status_code='D'
WHERE phase_code = 'P'
AND status_code in ('Q','I') and concurrent_program_id=38121;
Login to Oracle applications of target instance as sysadmin thru Appjump
Select system administrator Responisibility
Verify whether Concurrent Request “Gather Schema Statistics” is running or not
If not running, schedule the request to run immediately with Parameters : ALL,10
To check status fo running requests:
column REQUEST heading 'Request' format a8
column PHASE heading 'Phase' format A8
column STATUS heading 'Status' format A8
column PROGRAM heading 'Program Name' format A40
column SHORT heading 'Short Name' format A15
column REQUESTOR heading 'Requestor' format A10
column START_TIME heading 'Start Time' format A15
column RUN_TIME justify left heading 'Time(m)' format 999999.9
column OSPID heading 'OSPID' format a5
column OS_PIDa heading 'OSPIDA' format a6
column SID heading 'SID' format 99999
column serial# heading 'Serial#' format 99999
select substr(fcrv.request_id,1,8)REQUEST,
decode(fcrv.phase_code,'P','Pending','R','Running','I','Inactive','Completed')PHASE,
decode(fcrv.status_code,
'A','Waiting',
'B','Resuming',
'C','Normal',
'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',fcrv.status_code)STATUS,
substr(fcrv.program,1,40)PROGRAM,substr(fcrv.PROGRAM_SHORT_NAME,1,15)SHORT,
substr(fcrv.requestor,1,15)REQUESTOR,
-- to_char(fcrv.actual_start_date,'MM/DD/RR HH24:MI')START_TIME,
round(((sysdate - fcrv.actual_start_date)*1440),1)RUN_TIME,
substr(fcr.oracle_process_id,1,7)OSPID,s.sid,s.serial#
from apps.fnd_conc_req_summary_v fcrv,
apps.fnd_concurrent_requests fcr,
v$session s,v$process p
where fcrv.phase_code = 'R'
and fcrv.request_id = fcr.request_id
and s.paddr = p.addr
and fcr.oracle_process_id = p.spid
and fcrv.concurrent_program_id not in ('40112','40113','36887')
--and trunc(fcrv.actual_start_date) like trunc(sysdate)
order by PHASE, STATUS, REQUEST desc;
Take export dump of concurrent tables and import
exp userid=applsys/xxxx file=conc.dmp log=exp.log tables=FND_CONCURRENT_QUEUES,FND_CONCURRENT_QUEUES_TL,
FND_CONCURRENT_QUEUE_SIZE, FND_CONCURRENT_QUEUE_CONTENT
Truncate the fnd concurrent tables
SQL> truncate table fnd_Concurrent_queues;
Table truncated.
SQL> truncate table FND_CONCURRENT_QUEUES_TL;
Table truncated.
SQL> truncate table FND_CONCURRENT_QUEUE_SIZE;
Table truncated.
SQL> truncate table FND_CONCURRENT_QUEUE_CONTENT;
Table truncated.
Import the data back into the fnd tables which was exported as part of step above
imp userid=applsys/apps ignore=y file=conc.dmp full=y log=imp.log
Check whether the GSM is Up and Running in the system --> Profile option or using the Following Query :
select DECODE(b.profile_option_value, 'Y', 'Enabled', 'Disabled') DETAILS
from fnd_profile_options a, fnd_profile_option_values b
where a.APPLICATION_ID = b.APPLICATION_ID
and a.PROFILE_OPTION_ID = b.PROFILE_OPTION_ID
and a.PROFILE_OPTION_NAME = 'CONC_GSM_ENABLED';
Check whether the Service Manager is up and Running by the following Query :
select CONCURRENT_QUEUE_NAME, ENABLED_FLAG, MAX_PROCESSES, RUNNING_PROCESSES
from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME like 'FNDSM%';
The following SQL scripts located under $FND_TOP/sql are useful when diagnosing concurrent manager problems:
afimchk.sql Tells the status of the ICM and PMON method
afcmstat.sql Lists active manager processes
afrqrun.sql Lists all the running, waiting and Terminating requests
afrqwait.sql Lists requests that are constrained and waiting for the ICM to release them.
afrqscm.sql Prints log file name of managers that can run a given request. It can be used to check for possible errors when a request stays in pending status. It requires a request id value.
afcmcreq.sql Prints the log file name of the manager that processed the request
afrqstat.sql Summary of completed concurrent requests grouped by completion status and execution type. It requires number of days prior to today on which to report parameter.
afimlock.sql Lists locks that the ICM is waiting to get
afcmrrq.sql Lists managers that currently are running a request
Reports on requests that having been running for over a specified amount of time (hard coded as 4 hours). Exceptions, in addition to the defaults below, can be added by entering the program ID for the program exceptions under multi-items.
Runs Every 20 minutes
Uses the following query:
select b.REQUEST_ID, a.DESCRIPTION, b.phase_code,
(sysdate - b.actual_start_date) * 24 "running",
to_char(sysdate, 'mm/dd/yyyy hh:mi') "now",
to_char(b.request_date, 'mm/dd/yyyy hh:mi') "request_date",
to_char(b.actual_start_date, 'mm/dd/yyyy hh:mi') "start_time",
b.program_application_id "program_application_id",
b.concurrent_program_id "concurrent_program_id"
from APPS.FND_CONCURRENT_PROGRAMS_VL a,
APPS.FND_CONCURRENT_REQUESTS b
where a.CONCURRENT_PROGRAM_ID = b.CONCURRENT_PROGRAM_ID
and a.APPLICATION_ID = b.PROGRAM_APPLICATION_ID
and b.STATUS_CODE = 'R'
and b.PHASE_CODE = 'R'
and ((sysdate - b.actual_start_date) * 24) > 4
and a.CONCURRENT_PROGRAM_ID NOT IN(36887,43393,38121,42789,31556)
Excludes:
36887 - Workflow Mailer
43393 - ITM Adapter
38121 - Gather Schema Statistics
42789 - OAM Applications Dashboard Collection
31556 - Planning Manager
Monitors pending jobs exceeds the specified threshold. Excessive pending jobs may indicate an issue with the Concurrent Manager. Uses the following query:
select a.concurrent_program_name, b.REQUEST_ID, a.description,
to_char(b.request_date, 'mm/dd/yyyy hh:mi:ss') ""request_date"",
to_char(b.requested_start_date, 'mm/dd/yyyy hh:mi:ss') ""request_start""
from APPS.FND_CONCURRENT_PROGRAMS_VL a,
APPS.FND_CONCURRENT_REQUESTS b
where a.CONCURRENT_PROGRAM_ID = b.CONCURRENT_PROGRAM_ID
and a.APPLICATION_ID = b.PROGRAM_APPLICATION_ID
and b.PHASE_CODE = 'P'
and b.requested_start_date <= sysdate
The error threshold monitor will alert if the number of failed jobs exceeds the user defined threshold in a 30 minute period.
select a.concurrent_program_name, b.REQUEST_ID, a.description,
b.status_code, b.phase_code
from APPS.FND_CONCURRENT_PROGRAMS_VL a,
APPS.FND_CONCURRENT_REQUESTS b
where a.CONCURRENT_PROGRAM_ID = b.CONCURRENT_PROGRAM_ID
and a.APPLICATION_ID = b.PROGRAM_APPLICATION_ID
and b.STATUS_CODE IN ('E')
and b.actual_completion_date > sysdate - 1/48
Find request which are put on hold
SQL> select REQUEST_ID from fnd_concurrent_requests where phase_code ='P' and hold_flag='Y';
To find oracle_process id for a request id to pull trace file from udump:
select oracle_process_id ,
decode(status_code,'R','Running','D','Canceled','E','Error','X','Terminated','G','Warning','T','Terminating')""Status_code"",
phase_code,to_char(actual_start_date,'DD-MON-YYYY=>hh24:mi:ss') ""Login Time""
from apps.fnd_concurrent_requests where request_id='&Enter_conn_req_id'
To find spid of a request to get the trace file
prompt accept request prompt 'Please enter the concurrent request id for the appropriate concurrent program:
prompt
column traceid format a8
column tracename format a80
column user_concurrent_program_name format a40
column execname format a15
column enable_trace format a12
set lines 80
set pages 22
set head off
SELECT 'Request id: '||request_id ,'Trace id: '||oracle_Process_id, 'Trace Flag: '||req.enable_trace,
'Trace Name:'||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc', 'Prog. Name: '||prog.user_concurrent_program_name,'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running') ||'-'||decode(status_code,'R','Normal'),'SID Serial: '||ses.sid||','|| ses.serial#,'Module : '||ses.module
from fnd_concurrent_requests req, v$session ses, v$process proc,
v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog,
fnd_executables execname
where req.request_id ='&request'
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id
To check failed jobs submitted by an user
set lines 1000 pages 100
clear columns
col "Submitted By" format a15 word_wrap
select user_name "Submitted By", request_id "Request #",
to_char(cr.request_date,'dd-mon-rr hh24:mi') "Submitted on",
to_char(cr.last_update_date,'dd-mon-rr hh24:mi') "Failed on"
from applsys.fnd_concurrent_requests cr,
applsys.fnd_user u
where u.user_id = cr.requested_by
and user_name like '%BATCH%'
and cr.status_code ='E'
and cr.phase_code ='C'
and cr.request_date > sysdate - 1
order by 1
Statement to put the jobs on hold and release them lateron
To hold the requests (as apps user):
1) Drop table apps.str_dba_conc_req_hold ;
2) Create table apps.str_dba_conc_req_hold as select * from fnd_Concurrent_requests where PHASE_CODE='P' and hold_flag='N';
3) select count(*) from apps.str_dba_conc_req_hold ;
4) update fnd_Concurrent_requests set hold_flag='Y' where PHASE_CODE='P' and hold_flag='N' and request_id in (select request_id from apps.str_dba_conc_req_hold) ;
NOTE: You have to commit if select & update are same number of records. Otherwise rollback and try again till the numbers are same
5) Commit;
To Release these requests in prod after patching, here is the step :
6) a. update fnd_Concurrent_requests set hold_flag='N' where request_id in (select request_id from apps.str_dba_conc_req_hold);
b. commit;
How to take cm program trace.
Responsibility: System Administrator
Navigate: Concurrent > Program > Define
Query Concurrent Program
Select the Enable Trace Checkbox
Responsibility: System Administrator
Navigate: Profiles > System
Query Profile Option Concurrent: Allow Debugging
Set profile to Yes
Logon to the Responsibility that runs the Concurrent Program
In the Submit Request Screen click on Debug Options (B)
Select the Checkbox for SQL Trace
To submit active user request from backend
CONCSUB APPS/APPS SYSADMIN "System Administrator" SYSADMIN CONCURRENT FND FNDSCURS PROGRAM_NAME='"Active Users"'
sql program to submit request from backend
"SET SERVEROUTPUT ON
declare
req_id number;
begin
DBMS_OUTPUT.PUT_LINE('In begin');
fnd_global.APPS_INITIALIZE (0, 21758, 671);
req_id := FND_REQUEST.SUBMIT_REQUEST(application => 'FND',program => 'FNDSCURS',description => '',start_time =>
'',sub_request => FALSE);
if (req_id = 0) then
/* Handle submission error */
DBMS_OUTPUT.PUT_LINE('Request ID :' || req_id);
DBMS_OUTPUT.PUT_LINE('As the request ID is 0, the request was not submitted');
DBMS_OUTPUT.PUT_LINE('Please verify this part again');
else
DBMS_OUTPUT.PUT_LINE('Request ID :' || req_id);
DBMS_OUTPUT.PUT_LINE('Request submitted successfully');
commit;
end if;
end;
/
Meaning of status_code and phase_code in FND_CONCURRENT_REQUESTS table STATUS_CODE Column:
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
PHASE_CODE column
C - Completed
I - Inactive
P - Pending
R - Running
set term on
set feedback on
set echo on
set arraysize 4
set linesize 200
set pages 9999
set underline =;
column username format A15
column sid format 9990 heading SID
column type format A4
column lmode format 990 heading 'HELD'
column request format 990 heading 'REQ'
column id1 format 9999990
column id2 format 9999990
column sql_text format a100
column name format a80
break on id1 skip 1 dup
undefine v_request_id
define v_request_id
undefine v_spid
define v_spid
undefine v_sid
define v_sid
spool vj_concurrent_monitor.lst
Prompt Enter the concurrent_request_id
Accept v_request_id
prompt checking requests
select oracle_process_id from fnd_concurrent_requests where request_id='&v_request_id';
Prompt Enter the operating system oracle process id for this concurrent request
accept v_spid
Prompt Getting the sid
SELECT SID,SERIAL#,LOGON_TIME FROM V$SESSION WHERE PADDR IN
(SELECT ADDR FROM V$PROCESS WHERE SPID='&v_spid');
prompt Enter the session id for this concurrent request
accept v_sid
prompt memory usage for this session
SELECT A.SID,A.USERNAME,B.VALUE,c.name FROM V$SESSION a,V$SESSTAT B,V$STATNAME C WHERE A.SID=B.SID
AND B.STATISTIC#=C.STATISTIC# AND C.NAME like'%memor%' and a.sid='&v_sid';
prompt resource usage for this session
SELECT A.SID,A.USERNAME,B.VALUE,c.name FROM V$SESSION a,V$SESSTAT B,V$STATNAME C WHERE A.SID=B.SID
AND B.STATISTIC#=C.STATISTIC# and a.sid='&v_sid' order by b.value;
prompt this session waited on
select sid,event,wait_time,state from v$session_wait where sid='&v_sid' order by wait_time;
prompt current sql executing by this session
select a.sid,b.sorts,b.executions,b.loads,b.parse_calls,b.disk_reads,
b.buffer_gets,b.rows_processed,C.sql_text from v$session a,v$sqlarea b,V$SQLTEXT C
where a.sql_address=b.address and b.address=c.address and a.sid='&v_sid';
prompt sql which is taking more than 3mb in shared pool
prompt nosql should take morethan 1mb in shared pool.
prompt please ask the developers to tune the following sql statements
select name,
namespace,type,sharable_mem/(1024*1024) sharablemem,loads,executions,locks,pins,kept from v$db_object_cache
where SHARABLE_MEM>3000000;
prompt sort segments using by this session
SELECT s.username,s.sid,s.osuser,s.process,s.machine,u.extents, u.blocks,u.tablespace FROM v$session s, v$sort_usage u
WHERE s.saddr=u.session_addr order by extents;
and s.sid='&v_sid';
prompt current temp segments free in this instance
SELECT tablespace_name, extent_size, total_extents, used_extents, free_extents, max_used_size FROM v$sort_segment;
prompt total system events at this time
select event,total_waits waits, total_timeouts timeouts, time_waited total_time from v$system_event order by total_waits;
prompt latch contention if thery is any
SELECT latch#, name, gets, misses, sleeps FROM v$latch WHERE sleeps>0 ORDER BY sleeps ;
prompt the latch which is sleeping
select name, sleeps,latch# from v$latch_children where sleeps>4 order by sleeps;
spool off
clear columns
clear breaks
How to find out which request is handle by which concurrent queue.
a) First find out short_name of a program and then pass it as parameter to below query.
b) The below query will give you output
I - Included - Included in new concurrent queue
E - excluded from Standard Manager
This way you know now this running program (concurrent request) is handled by new manager and not part of standard manager.
SELECT A.INCLUDE_FLAG, A.QUEUE_APPLICATION_ID, C.USER_CONCURRENT_QUEUE_NAME,
B.CONCURRENT_PROGRAM_NAME
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 b.concurrent_program_name = ‘&SHORT_NAME’ and c.concurrent_queue_id = a.concurrent_queue_id
How to find out Summary of Concurrent requests.
SELECT
request_id, SUBSTR(requestor,1,25), SUBSTR(program,1,50), SUBSTR(user_concurrent_program_name,1,100),
TO_CHAR(actual_start_date,’dd/mm/yy :hh24:mi’) start_date,
TO_CHAR(actual_completion_date,’dd/mm/yy :hh24:mi’) completion_date,
FLOOR((ACTUAL_COMPLETION_DATE- ACTUAL_START_DATE)*24) “in Hours”,
(((ACTUAL_COMPLETION_DATE- ACTUAL_START_DATE)*24)-(FLOOR((ACTUAL_COMPLETION_DATE- ACTUAL_START_DATE)*24)))*60 “In_Min”
–requestor, program, user_concurrent_program_name
FROM fnd_conc_req_summary_v
WHERE (ACTUAL_COMPLETION_DATE- ACTUAL_START_DATE)*24*60 >10
How to find database SID from a Concurrent request.
column process heading “FNDLIBR PID”
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$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’;
You need your concurrent request ID as an input.
c.SPID= is the operating system process id
d.sid= is the Oracle process id
Cancel Concurrent requests. We don't need concurrent requests which are scheduled in production to keep running in test. We use the following update to cancel them.
update fnd_concurrent_requests
set phase_code='C',
status_code='D'
where phase_code = 'P'
and concurrent_program_id not in (
select concurrent_program_id
from fnd_concurrent_programs_tl
where user_concurrent_program_name like '%Synchronize%tables%'
or user_concurrent_program_name like '%Workflow%Back%'
or user_concurrent_program_name like '%Sync%responsibility%role%'
or user_concurrent_program_name like '%Workflow%Directory%')
and (status_code = 'I' OR status_code = 'Q');
Use the SQL below to only cancel the running requests connecting as sys
UPDATE applsys.fnd_concurrent_requests
SET phase_code = 'C', status_code = 'X'
WHERE phase_code = 'R' and status_code ='R'
/
commit
Also, please put all Pending Jobs on Hold, using the SQL below connecting as sys
update applsys.fnd_concurrent_requests
set hold_flag='Y' where
phase_code='P' and hold_flag='N'
/
commit
update fnd_concurrent_requests fcr
set phase_code = 'C',
status_code = 'D'
where fcr.PHASE_CODE <> 'C'
and (fcr.program_application_id,fcr.CONCURRENT_PROGRAM_id) in
(select fcp.application_id,fcp.concurrent_program_id from fnd_concurrent_programs fcp,fnd_executables_vl fev
where fcp.executable_application_id=fev.application_id and fcp.executable_id=fev.executable_id
and (upper(fev.user_executable_name) like 'AL%MAIL%'
or upper(fev.user_executable_name) like 'AL%FTP%'
or upper(fev.user_executable_name) like 'AL%EXCEL%'))
/
To change the number of processes for the standard manager
update FND_CONCURRENT_QUEUE_SIZE
set min_processes = 4
where concurrent_queue_id = 0;
how to find params passed to request from backend
select CONCURRENT_PROGRAM_ID,CONCURRENT_PROGRAM_NAME from fnd_concurrent_programs where concurrent_program_name like '';
select REQUEST_ID,CONCURRENT_PROGRAM_ID,substr(ARGUMENT_TEXT,1,60)params,status_code,phase_code from fnd_concurrent_requests where CONCURRENT_PROGRAM_ID=;
To increase the jvm for OPP
"From Note ID 737311.1 we need to do step 3 We are changing this parameter to 2048m as mentioned below in script.
Configure the Output Post Processor's JVM. These steps set the JVM to 2GB, depending upon
your server's size you might find 3 GB (-mx3072m), 4GB (-mx4096m) or even 5GB (-mx5120m) is a better value. This setting prevents the error ""java.lang.OutOfMemoryError: Java heap space""
in the Output Post Processor's log associated to the Subledger Accounting Program.
Login to SQL*Plus as APPS.
SQL>update FND_CP_SERVICES set DEVELOPER_PARAMETERS =
'J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx2048m'
where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');
Bounce the concurrent managers.
one more eg:
To determine current heap size:
select DEVELOPER_PARAMETERS
from FND_CP_SERVICES
where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');
To increase heap size to 1024:
update FND_CP_SERVICES
set DEVELOPER_PARAMETERS = 'J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx1024m'
where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');
Query to find pending concurrent requests
select count(*)
from APPS.FND_ 2 CONCURRENT_PROGRAMS_VL a,
APPS.FND_CONCURRENT_REQUESTS b
where a.CONCURRENT_PROGRAM_ID = b.CONCURRENT_PROGRAM_ID
and a.APPLICATION_ID = b.PROGRAM_APPLICATION_ID
and b.PHASE_CODE = 'P'
and b.requested_start_date <= sysdate
Pending job details
"SELECT c.user_name, request_id, phase_code, status_code, hold_flag,
TO_CHAR(requested_start_date,'DD-MON-YY:HH24:MM:SS') Requested_Start_Date,
user_concurrent_program_name, b.concurrent_program_id
FROM applsys.fnd_concurrent_requests a,
applsys.fnd_concurrent_programs_tl b,
applsys.fnd_user c
WHERE a.phase_code = 'P'
AND a.concurrent_program_id = b.concurrent_program_id
AND b.LANGUAGE = 'US'
AND c.user_id = a.requested_by
ORDER BY user_concurrent_program_name;"
Cancel scheduled concurrent Request “Gather Schema Statistics” sqlplus apps/apps
sql>
update fnd_concurrent_requests
set phase_code='C',status_code='D'
WHERE phase_code = 'P'
AND status_code in ('Q','I') and concurrent_program_id=38121;
Commit;
Exit
Putting all concurrent jobs on hold:
Update applsys.fnd_concurrent_requests set hold_flag='Y' where phase_code in ('R','P','I');
Cancel scheduled concurrent Request “Gather Schema Statistics”
sqlplus apps/apps
sql>
update fnd_concurrent_requests
set phase_code='C',status_code='D'
WHERE phase_code = 'P'
AND status_code in ('Q','I') and concurrent_program_id=38121;
Commit;
Exit
To terminate request from backend
SQL> select REQUEST_ID,ORACLE_ID,ORACLE_PROCESS_ID,ORACLE_SESSION_ID,OS_PROCESS_ID from applsys.FND_CONCURRENT_REQUESTS where REQUEST_ID=577945;
REQUEST_ID ORACLE_ID ORACLE_PROCESS_ID ORACLE_SESSION_ID
---------- ---------- ------------------------------ -----------------
OS_PROCESS_ID
------------------------------------------------------------------------------------------------------------------------------------
577945 900 13348 1242142
13299
SQL> !kill -9 13348
/bin/ksh: kill: 13348: No such process
SQL> update APPLSYS.fnd_Concurrent_requests set PHASE_CODE='C', STATUS_CODE='D' where REQUEST_ID=577945;
1 row updated.
SQL> commit;
Commit complete.
Schedule “Purge Concurrent Request and/or Manager Data”
Cancel existing scheduled request before scheduling new.
# ebappsenv
# sqlplus apps
update fnd_concurrent_requests
set phase_code='C',status_code='D'
WHERE phase_code = 'P'
AND status_code in ('Q','I')
and concurrent_program_id=32263;
Parameter:
Entity : REQUEST
Mode : Age
Mode Value : 15 à On non-prod environments as per case# 734780
Schedule:
on specific days : Wednesday and Saturday at 19:00 CET
Run Gather Schema Statistics as a Concurrent request
Cancel any pending jobs for “Gather Schema Statistics”
Sqlplus apps/appspasswd
Sql>
update fnd_concurrent_requests
set phase_code='C',status_code='D'
WHERE phase_code = 'P'
AND status_code in ('Q','I') and concurrent_program_id=38121;
Login to Oracle applications of target instance as sysadmin thru Appjump
Select system administrator Responisibility
Verify whether Concurrent Request “Gather Schema Statistics” is running or not
If not running, schedule the request to run immediately with Parameters : ALL,10
To check status fo running requests:
column REQUEST heading 'Request' format a8
column PHASE heading 'Phase' format A8
column STATUS heading 'Status' format A8
column PROGRAM heading 'Program Name' format A40
column SHORT heading 'Short Name' format A15
column REQUESTOR heading 'Requestor' format A10
column START_TIME heading 'Start Time' format A15
column RUN_TIME justify left heading 'Time(m)' format 999999.9
column OSPID heading 'OSPID' format a5
column OS_PIDa heading 'OSPIDA' format a6
column SID heading 'SID' format 99999
column serial# heading 'Serial#' format 99999
select substr(fcrv.request_id,1,8)REQUEST,
decode(fcrv.phase_code,'P','Pending','R','Running','I','Inactive','Completed')PHASE,
decode(fcrv.status_code,
'A','Waiting',
'B','Resuming',
'C','Normal',
'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',fcrv.status_code)STATUS,
substr(fcrv.program,1,40)PROGRAM,substr(fcrv.PROGRAM_SHORT_NAME,1,15)SHORT,
substr(fcrv.requestor,1,15)REQUESTOR,
-- to_char(fcrv.actual_start_date,'MM/DD/RR HH24:MI')START_TIME,
round(((sysdate - fcrv.actual_start_date)*1440),1)RUN_TIME,
substr(fcr.oracle_process_id,1,7)OSPID,s.sid,s.serial#
from apps.fnd_conc_req_summary_v fcrv,
apps.fnd_concurrent_requests fcr,
v$session s,v$process p
where fcrv.phase_code = 'R'
and fcrv.request_id = fcr.request_id
and s.paddr = p.addr
and fcr.oracle_process_id = p.spid
and fcrv.concurrent_program_id not in ('40112','40113','36887')
--and trunc(fcrv.actual_start_date) like trunc(sysdate)
order by PHASE, STATUS, REQUEST desc;
Take export dump of concurrent tables and import
exp userid=applsys/xxxx file=conc.dmp log=exp.log tables=FND_CONCURRENT_QUEUES,FND_CONCURRENT_QUEUES_TL,
FND_CONCURRENT_QUEUE_SIZE, FND_CONCURRENT_QUEUE_CONTENT
Truncate the fnd concurrent tables
SQL> truncate table fnd_Concurrent_queues;
Table truncated.
SQL> truncate table FND_CONCURRENT_QUEUES_TL;
Table truncated.
SQL> truncate table FND_CONCURRENT_QUEUE_SIZE;
Table truncated.
SQL> truncate table FND_CONCURRENT_QUEUE_CONTENT;
Table truncated.
Import the data back into the fnd tables which was exported as part of step above
imp userid=applsys/apps ignore=y file=conc.dmp full=y log=imp.log
Check whether the GSM is Up and Running in the system --> Profile option or using the Following Query :
select DECODE(b.profile_option_value, 'Y', 'Enabled', 'Disabled') DETAILS
from fnd_profile_options a, fnd_profile_option_values b
where a.APPLICATION_ID = b.APPLICATION_ID
and a.PROFILE_OPTION_ID = b.PROFILE_OPTION_ID
and a.PROFILE_OPTION_NAME = 'CONC_GSM_ENABLED';
Check whether the Service Manager is up and Running by the following Query :
select CONCURRENT_QUEUE_NAME, ENABLED_FLAG, MAX_PROCESSES, RUNNING_PROCESSES
from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME like 'FNDSM%';
The following SQL scripts located under $FND_TOP/sql are useful when diagnosing concurrent manager problems:
afimchk.sql Tells the status of the ICM and PMON method
afcmstat.sql Lists active manager processes
afrqrun.sql Lists all the running, waiting and Terminating requests
afrqwait.sql Lists requests that are constrained and waiting for the ICM to release them.
afrqscm.sql Prints log file name of managers that can run a given request. It can be used to check for possible errors when a request stays in pending status. It requires a request id value.
afcmcreq.sql Prints the log file name of the manager that processed the request
afrqstat.sql Summary of completed concurrent requests grouped by completion status and execution type. It requires number of days prior to today on which to report parameter.
afimlock.sql Lists locks that the ICM is waiting to get
afcmrrq.sql Lists managers that currently are running a request
Reports on requests that having been running for over a specified amount of time (hard coded as 4 hours). Exceptions, in addition to the defaults below, can be added by entering the program ID for the program exceptions under multi-items.
Runs Every 20 minutes
Uses the following query:
select b.REQUEST_ID, a.DESCRIPTION, b.phase_code,
(sysdate - b.actual_start_date) * 24 "running",
to_char(sysdate, 'mm/dd/yyyy hh:mi') "now",
to_char(b.request_date, 'mm/dd/yyyy hh:mi') "request_date",
to_char(b.actual_start_date, 'mm/dd/yyyy hh:mi') "start_time",
b.program_application_id "program_application_id",
b.concurrent_program_id "concurrent_program_id"
from APPS.FND_CONCURRENT_PROGRAMS_VL a,
APPS.FND_CONCURRENT_REQUESTS b
where a.CONCURRENT_PROGRAM_ID = b.CONCURRENT_PROGRAM_ID
and a.APPLICATION_ID = b.PROGRAM_APPLICATION_ID
and b.STATUS_CODE = 'R'
and b.PHASE_CODE = 'R'
and ((sysdate - b.actual_start_date) * 24) > 4
and a.CONCURRENT_PROGRAM_ID NOT IN(36887,43393,38121,42789,31556)
Excludes:
36887 - Workflow Mailer
43393 - ITM Adapter
38121 - Gather Schema Statistics
42789 - OAM Applications Dashboard Collection
31556 - Planning Manager
Monitors pending jobs exceeds the specified threshold. Excessive pending jobs may indicate an issue with the Concurrent Manager. Uses the following query:
select a.concurrent_program_name, b.REQUEST_ID, a.description,
to_char(b.request_date, 'mm/dd/yyyy hh:mi:ss') ""request_date"",
to_char(b.requested_start_date, 'mm/dd/yyyy hh:mi:ss') ""request_start""
from APPS.FND_CONCURRENT_PROGRAMS_VL a,
APPS.FND_CONCURRENT_REQUESTS b
where a.CONCURRENT_PROGRAM_ID = b.CONCURRENT_PROGRAM_ID
and a.APPLICATION_ID = b.PROGRAM_APPLICATION_ID
and b.PHASE_CODE = 'P'
and b.requested_start_date <= sysdate
The error threshold monitor will alert if the number of failed jobs exceeds the user defined threshold in a 30 minute period.
select a.concurrent_program_name, b.REQUEST_ID, a.description,
b.status_code, b.phase_code
from APPS.FND_CONCURRENT_PROGRAMS_VL a,
APPS.FND_CONCURRENT_REQUESTS b
where a.CONCURRENT_PROGRAM_ID = b.CONCURRENT_PROGRAM_ID
and a.APPLICATION_ID = b.PROGRAM_APPLICATION_ID
and b.STATUS_CODE IN ('E')
and b.actual_completion_date > sysdate - 1/48
Find request which are put on hold
SQL> select REQUEST_ID from fnd_concurrent_requests where phase_code ='P' and hold_flag='Y';
To find oracle_process id for a request id to pull trace file from udump:
select oracle_process_id ,
decode(status_code,'R','Running','D','Canceled','E','Error','X','Terminated','G','Warning','T','Terminating')""Status_code"",
phase_code,to_char(actual_start_date,'DD-MON-YYYY=>hh24:mi:ss') ""Login Time""
from apps.fnd_concurrent_requests where request_id='&Enter_conn_req_id'
To find spid of a request to get the trace file
prompt accept request prompt 'Please enter the concurrent request id for the appropriate concurrent program:
prompt
column traceid format a8
column tracename format a80
column user_concurrent_program_name format a40
column execname format a15
column enable_trace format a12
set lines 80
set pages 22
set head off
SELECT 'Request id: '||request_id ,'Trace id: '||oracle_Process_id, 'Trace Flag: '||req.enable_trace,
'Trace Name:'||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc', 'Prog. Name: '||prog.user_concurrent_program_name,'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running') ||'-'||decode(status_code,'R','Normal'),'SID Serial: '||ses.sid||','|| ses.serial#,'Module : '||ses.module
from fnd_concurrent_requests req, v$session ses, v$process proc,
v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog,
fnd_executables execname
where req.request_id ='&request'
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id
To check failed jobs submitted by an user
set lines 1000 pages 100
clear columns
col "Submitted By" format a15 word_wrap
select user_name "Submitted By", request_id "Request #",
to_char(cr.request_date,'dd-mon-rr hh24:mi') "Submitted on",
to_char(cr.last_update_date,'dd-mon-rr hh24:mi') "Failed on"
from applsys.fnd_concurrent_requests cr,
applsys.fnd_user u
where u.user_id = cr.requested_by
and user_name like '%BATCH%'
and cr.status_code ='E'
and cr.phase_code ='C'
and cr.request_date > sysdate - 1
order by 1
Statement to put the jobs on hold and release them lateron
To hold the requests (as apps user):
1) Drop table apps.str_dba_conc_req_hold ;
2) Create table apps.str_dba_conc_req_hold as select * from fnd_Concurrent_requests where PHASE_CODE='P' and hold_flag='N';
3) select count(*) from apps.str_dba_conc_req_hold ;
4) update fnd_Concurrent_requests set hold_flag='Y' where PHASE_CODE='P' and hold_flag='N' and request_id in (select request_id from apps.str_dba_conc_req_hold) ;
NOTE: You have to commit if select & update are same number of records. Otherwise rollback and try again till the numbers are same
5) Commit;
To Release these requests in prod after patching, here is the step :
6) a. update fnd_Concurrent_requests set hold_flag='N' where request_id in (select request_id from apps.str_dba_conc_req_hold);
b. commit;
How to take cm program trace.
Responsibility: System Administrator
Navigate: Concurrent > Program > Define
Query Concurrent Program
Select the Enable Trace Checkbox
Responsibility: System Administrator
Navigate: Profiles > System
Query Profile Option Concurrent: Allow Debugging
Set profile to Yes
Logon to the Responsibility that runs the Concurrent Program
In the Submit Request Screen click on Debug Options (B)
Select the Checkbox for SQL Trace
To submit active user request from backend
CONCSUB APPS/APPS SYSADMIN "System Administrator" SYSADMIN CONCURRENT FND FNDSCURS PROGRAM_NAME='"Active Users"'
sql program to submit request from backend
"SET SERVEROUTPUT ON
declare
req_id number;
begin
DBMS_OUTPUT.PUT_LINE('In begin');
fnd_global.APPS_INITIALIZE (0, 21758, 671);
req_id := FND_REQUEST.SUBMIT_REQUEST(application => 'FND',program => 'FNDSCURS',description => '',start_time =>
'',sub_request => FALSE);
if (req_id = 0) then
/* Handle submission error */
DBMS_OUTPUT.PUT_LINE('Request ID :' || req_id);
DBMS_OUTPUT.PUT_LINE('As the request ID is 0, the request was not submitted');
DBMS_OUTPUT.PUT_LINE('Please verify this part again');
else
DBMS_OUTPUT.PUT_LINE('Request ID :' || req_id);
DBMS_OUTPUT.PUT_LINE('Request submitted successfully');
commit;
end if;
end;
/
Meaning of status_code and phase_code in FND_CONCURRENT_REQUESTS table STATUS_CODE Column:
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
PHASE_CODE column
C - Completed
I - Inactive
P - Pending
R - Running
