Concurrent manager requires

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';

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. Find out process id from request id?

select request_id,to_char(ACTUAL_START_DATE,'DD-MM-YYYY HH24:MI:SS'),to_char(ACTUAL_COMPLETION_DATE,'DD-MM-YYYY HH24:MI:SS'),phase_code,status_code,os_process_id,oracle_process_id from apps.fnd_concurrent_requests where request_id=&req_id;

6.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;

7. 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'
/

8. 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';

9. Cancelling Concurrent request?

update fnd_concurrent_requests
set status_code='D', phase_code='C'
where request_id=&req_id;

10. Kill sessions program wise request?

select 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' immediate;' from v$session where MODULE like '';

11. To find out 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;

12. To find out Concurrent Request Error Script (requests which were errored 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. To find out request submitted by which 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. To find Concurrent Request completion time?

select u.user_name,r.request_id,p.user_concurrent_program_name,r.actual_start_date,r.actual_completion_date,r.completion_text,
(r.actual_completion_date-r.actual_start_date)*1440 "Minutes"  from
fnd_concurrent_requests r,
fnd_user u,
fnd_concurrent_programs_tl p
where  u.user_id=r.requested_by
and p.concurrent_program_id=r.concurrent_program_id
and  phase_code='C'  /* Completed */
 and  request_id > 13246690   /* limit the number of rows for quick processing */
 and actual_completion_date is not null
 and actual_start_date is not null
 and actual_start_date > sysdate-1   /* requests completed in last 24 hrs */
 order by "Minutes" desc  ;

16.  To find the trace file generated by this request id?

Execute the following query and give the request id when asked, that would display the location and name of the trace file generated.

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;

17. To 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%')) 

/

18. To change the number of processes for the standard manager?

update FND_CONCURRENT_QUEUE_SIZE 

set min_processes = 4

where concurrent_queue_id = 0;

19. 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.

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

20 . 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');

21. To  find pending concurrent requests from backend?

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;"

22. To put all concurrent jobs on hold?

Update applsys.fnd_concurrent_requests set hold_flag='Y' where phase_code in ('R','P','I');

23. To 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

24. Monitors pending jobs exceeds the specified threshold?

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;

25. 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;

26. To find request which are put on hold ?

select REQUEST_ID from fnd_concurrent_requests where phase_code ='P' and hold_flag='Y';

27. 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;

====

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