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.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;
select s.sid ,s.username,l.address,l.sql_text sql_text , round(last_call_et/60,2) called_at
from v$sqltext_with_newlines l, v$session s
where l.address=s.sql_address
and l.hash_value=s.sql_hash_value
and s.sid in (select sid from v$session where paddr=(select addr from v$process where spid in
(select oracle_process_id from apps.Fnd_Concurrent_Requests where request_id='&1')))
order by s.sid,l.address,l.piece
15.Concurrent Request Status
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 b.inst_id,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.inst_id,c.username,c.sid,c.serial#,
c.program,d.spid from gv$session c, gv$process d
where c.paddr=d.addr) b,
apps.fnd_concurrent_programs_tl e,
apps.fnd_user f,
gv$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';
16.Concurrent Program which has tracing
select concurrent_program_name from apps.fnd_concurrent_programs where enable_trace='Y';
17.How to put request in Complete State from backend
update fnd_concurrent_requests set status_code='D', phase_code='C' where request_id='28406642';
18.Processors and Session info for one Concurrent Request
bde_request.sql - Process and Session info for one Concurrent Request (11.5) [ID 187504.1]
================================================================================================
SET ver OFF term ON feed OFF pages 255 lin 255 trims ON;
PROMPT Usage:
PROMPT sqlplus apps/apps
PROMPT SQL> START bde_request.sql <request_id>
PROMPT
SET term OFF;
DEF p_request_id = &1;
COL p_requested_by NEW_VALUE p_requested_by -
FORMAT 999999999999999;
COL p_phase_code NEW_VALUE p_phase_code -
FORMAT A1;
COL p_status_code NEW_VALUE p_status_code -
FORMAT A1;
COL p_program_application_id NEW_VALUE p_program_application_id -
FORMAT 999999999999999;
COL p_concurrent_program_id NEW_VALUE p_concurrent_program_id -
FORMAT 999999999999999;
COL p_controlling_manager NEW_VALUE p_controlling_manager -
FORMAT 999999999999999;
COL p_oracle_process_id NEW_VALUE p_oracle_process_id -
FORMAT A30;
COL p_oracle_session_id NEW_VALUE p_oracle_session_id -
FORMAT 999999999999999;
COL p_executable_application_id NEW_VALUE p_executable_application_id -
FORMAT 999999999999999;
COL p_executable_id NEW_VALUE p_executable_id -
FORMAT 999999999999999;
COL p_addr NEW_VALUE p_addr -
FORMAT A8;
COL p_sid NEW_VALUE p_sid -
FORMAT 99999;
COL request_id FORMAT 9999999999;
COL request_date FORMAT A20;
COL requested_by FORMAT 999999999999;
COL phase_code FORMAT A10;
COL status_code FORMAT A11;
COL requested_start_date FORMAT A20;
COL program_application_id FORMAT 9999999999999999999999;
COL concurrent_program_id FORMAT 999999999999999999999;
COL controlling_manager FORMAT 9999999999999999999;
COL actual_start_date FORMAT A20;
COL actual_completion_date FORMAT A22;
COL current_date FORMAT A20;
COL duration FORMAT 99999.99;
COL logfile_name FORMAT A70;
COL outfile_name FORMAT A70;
COL oracle_process_id FORMAT A17;
COL oracle_session_id FORMAT 99999999999999999;
COL os_process_id FORMAT A13;
COL enable_trace FORMAT A12;
COL user_id FORMAT 999999999999;
COL user_name FORMAT A20;
COL description FORMAT A60;
COL lookup_type FORMAT A11;
COL meaning FORMAT A11;
COL application_id FORMAT 99999999999999;
COL concurrent_program_name FORMAT A23;
COL executable_application_id FORMAT 9999999999999999999999999;
COL executable_id FORMAT 9999999999999;
COL optimizer_mode FORMAT A14;
COL language FORMAT A20;
COL user_concurrent_program_name FORMAT A80;
COL executable_name FORMAT A15;
COL execution_file_name FORMAT A19;
COL subroutine_name FORMAT A15;
COL concurrent_process_id FORMAT 999999999999999999999;
COL session_id FORMAT 999999999999999;
COL node_name FORMAT A20;
COL db_name FORMAT A8;
COL db_domain FORMAT A30;
COL logfile_name FORMAT A70;
COL addr FORMAT A8;
COL pid FORMAT 99999999;
COL spid FORMAT A9;
COL serial# FORMAT 9999999999;
COL saddr FORMAT A8;
COL sid FORMAT 9999999999;
COL serial# FORMAT 9999999999;
COL audsid FORMAT 9999999999;
COL paddr FORMAT A8;
COL process FORMAT A9;
COL module FORMAT A24;
COL action FORMAT A32;
COL client_info FORMAT A64;
SELECT requested_by p_requested_by,
phase_code p_phase_code,
status_code p_status_code,
program_application_id p_program_application_id,
concurrent_program_id p_concurrent_program_id,
controlling_manager p_controlling_manager,
oracle_process_id p_oracle_process_id,
oracle_session_id p_oracle_session_id
FROM fnd_concurrent_requests
WHERE request_id = TO_NUMBER('&p_request_id');
SELECT executable_application_id p_executable_application_id,
executable_id p_executable_id
FROM fnd_concurrent_programs
WHERE application_id = &p_program_application_id
AND concurrent_program_id = &p_concurrent_program_id;
SELECT addr p_addr
FROM v$process
WHERE spid = '&p_oracle_process_id';
SELECT sid p_sid
FROM v$session
WHERE paddr = '&p_addr';
SPOOL bde_request.txt;
SET term ON;
PROMPT
PROMPT
PROMPT FND_CONCURRENT_REQUESTS
PROMPT =======================
PROMPT
SELECT request_id,
requested_by,
phase_code,
status_code,
program_application_id,
concurrent_program_id,
controlling_manager,
oracle_process_id,
oracle_session_id,
os_process_id,
enable_trace
FROM fnd_concurrent_requests
WHERE request_id = TO_NUMBER('&p_request_id');
PROMPT
PROMPT REQUESTED_BY ref FND_USER.USER_ID
PROMPT PHASE_CODE ref FND_LOOKUPS.LOOKUP_CODE (LOOKUP_TYPE = 'CP_PHASE_CODE')
PROMPT STATUS_CODE ref FND_LOOKUPS.LOOKUP_CODE (LOOKUP_TYPE = 'CP_STATUS_CODE')
PROMPT PROGRAM_APPLICATION_ID ref FND_CONCURRENT_PROGRAMS.APPLICATION_ID
PROMPT CONCURRENT_PROGRAM_ID ref FND_CONCURRENT_PROGRAMS.CONCURRENT_PROGRAM_ID
PROMPT CONTROLLING_MANAGER ref FND_CONCURRENT_PROCESSES.CONCURRENT_PROCESS_ID
PROMPT ORACLE_PROCESS_ID ref V$PROCESS.SPID (identifies SQL Trace filename)
PROMPT ORACLE_SESSION_ID ref V$SESSION.AUDSID
PROMPT
PROMPT OS_PROCESS_ID is Operating System Process ID for Concurrent Program
PROMPT
SELECT request_id,
TO_CHAR( request_date, 'DD-MON-YYYY HH24:MI:SS' )
request_date,
TO_CHAR( requested_start_date,'DD-MON-YYYY HH24:MI:SS' )
requested_start_date,
TO_CHAR( actual_start_date, 'DD-MON-YYYY HH24:MI:SS' )
actual_start_date,
TO_CHAR( actual_completion_date, 'DD-MON-YYYY HH24:MI:SS' )
actual_completion_date,
TO_CHAR( sysdate, 'DD-MON-YYYY HH24:MI:SS' )
current_date,
ROUND( ( NVL( actual_completion_date, sysdate ) - actual_start_date ) * 24, 2 )
duration
FROM fnd_concurrent_requests
WHERE request_id = TO_NUMBER('&p_request_id');
SELECT request_id,
logfile_name,
outfile_name,
oracle_process_id
FROM fnd_concurrent_requests
WHERE request_id = TO_NUMBER('&p_request_id');
PROMPT
PROMPT ORACLE_PROCESS_ID identifies SQL Trace filename under udump directory
PROMPT
PROMPT
PROMPT
PROMPT FND_USER
PROMPT ========
PROMPT
SELECT user_id,
user_name,
description
FROM fnd_user
WHERE user_id = &p_requested_by;
PROMPT
PROMPT
PROMPT FND_LOOKUPS
PROMPT ===========
PROMPT
SELECT SUBSTR(flu1.lookup_type,4,11) lookup_type,
fcr.phase_code,
flu1.meaning,
SUBSTR(flu2.lookup_type,4,11) lookup_type,
fcr.status_code,
flu2.meaning
FROM fnd_concurrent_requests fcr,
fnd_lookups flu1,
fnd_lookups flu2
WHERE fcr.request_id = TO_NUMBER('&p_request_id')
AND flu1.lookup_type = 'CP_PHASE_CODE'
AND fcr.phase_code = flu1.lookup_code
AND flu2.lookup_type = 'CP_STATUS_CODE'
AND fcr.status_code = flu2.lookup_code;
PROMPT
PROMPT
PROMPT FND_CONCURRENT_PROGRAMS
PROMPT =======================
PROMPT
SELECT application_id,
concurrent_program_id,
concurrent_program_name,
executable_application_id,
executable_id,
enable_trace,
optimizer_mode
FROM fnd_concurrent_programs
WHERE application_id = &p_program_application_id
AND concurrent_program_id = &p_concurrent_program_id;
PROMPT
PROMPT EXECUTABLE_APPLICATION_ID ref FND_EXECUTABLES.APPLICATION_ID
PROMPT EXECUTABLE_ID ref FND_EXECUTABLES.EXECUTABLE_ID
PROMPT
PROMPT
PROMPT
PROMPT FND_CONCURRENT_PROGRAMS_TL
PROMPT ==========================
PROMPT
SELECT application_id,
concurrent_program_id,
language,
user_concurrent_program_name
FROM fnd_concurrent_programs_tl
WHERE application_id = &p_program_application_id
AND concurrent_program_id = &p_concurrent_program_id
AND language = USERENV('LANG');
PROMPT
PROMPT
PROMPT FND_EXECUTABLES
PROMPT ===============
PROMPT
SELECT application_id,
executable_id,
executable_name,
execution_file_name,
subroutine_name
FROM fnd_executables
WHERE application_id = &p_executable_application_id
AND executable_id = &p_executable_id;
PROMPT
PROMPT
PROMPT FND_CONCURRENT_PROCESSES
PROMPT ========================
PROMPT
SELECT concurrent_process_id,
session_id,
TO_CHAR( oracle_process_id ) oracle_process_id,
os_process_id,
node_name,
db_name,
db_domain
FROM fnd_concurrent_processes
WHERE concurrent_process_id = TO_NUMBER( TO_CHAR( '&p_controlling_manager' ) );
PROMPT
PROMPT OS_PROCESS_ID is Operating System Process ID for Concurrent Manager
PROMPT
SELECT concurrent_process_id,
logfile_name
FROM fnd_concurrent_processes
WHERE concurrent_process_id = TO_NUMBER( TO_CHAR( '&p_controlling_manager' ) );
PROMPT
PROMPT
PROMPT V$PROCESS
PROMPT =========
PROMPT
SELECT addr,
pid,
spid,
serial#
FROM v$process
WHERE spid = '&p_oracle_process_id';
PROMPT
PROMPT
PROMPT V$SESSION
PROMPT =========
PROMPT
SELECT saddr,
sid,
serial#,
audsid,
paddr,
process
FROM v$session
WHERE paddr = '&p_addr'
AND audsid = TO_NUMBER( TO_CHAR( '&p_oracle_session_id' ) );
PROMPT
PROMPT PADDR ref V$PROCESS.ADDR
PROMPT
SELECT saddr,
sid,
serial#,
module,
action,
client_info
FROM v$session
WHERE paddr = '&p_addr'
AND audsid = TO_NUMBER( TO_CHAR( '&p_oracle_session_id' ) );
SPOOL OFF;
SET ver ON feed ON pages 24 lin 80 trims OFF;
PROMPT
PROMPT Executing bde_session.sql for sid &p_sid
PROMPT
START bde_session.sql &p_sid;
PROMPT
COLUMN ENDEDCR FORMAT A21 HEADING 'bde_request.sql ended';
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') ENDEDCR FROM SYS.DUAL;
19.Retrieve the log messages for a Concurrent Request when Debug is Enabled
SELECT LOG.message_text message
FROM FND_LOG_MESSAGES LOG,
FND_LOG_TRANSACTION_CONTEXT CON
WHERE CON.TRANSACTION_ID = '&con_request_id'
AND CON.TRANSACTION_TYPE = 'REQUEST'
AND CON.TRANSACTION_CONTEXT_ID = LOG.TRANSACTION_CONTEXT_ID
ORDER BY LOG.LOG_SEQUENCE;
20.How to see the top10 Requests based on total runtime on a given day.
/* top10jobs.sql
see the top10 jobs based on total runtime for today
*/
col program form A35 trunc head "Program Full Name"
col intprog format a35 trunc head "Internal Name"
col TotTime form 99999
col AvgTime form 99999.90
col qname head "Queue" format a15 trunc
select * from (
select q.concurrent_queue_name qname,
c.concurrent_program_name ||
' (' || to_char(c.concurrent_program_id) || ')' intprog,
ctl.user_concurrent_program_name "program",
sum((nvl(actual_completion_date,sysdate)-actual_start_date)*1440) "TotTime",
count(*),
avg((nvl(actual_completion_date,sysdate)-actual_start_date)*1440) "AvgTime"
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 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'
and trunc(actual_completion_date) = trunc(sysdate)
and actual_start_date is not null
and actual_completion_date is not null
group by q.concurrent_queue_name,
c.concurrent_program_name || ' (' || to_char(c.concurrent_program_id) || ')' ,
ctl.user_concurrent_program_name
order by 4 desc)
where rownum < 11;
21.Requests run by a specific Concurrent Manager/Queue within the date range
* This script returns information on all jobs run through a specific */
/* queue within the date range with a run time >= the minimum execution */
/* time. */
/* Required Input: Start/End date; minimum execution time in seconds; */
/* the long program name */
prompt Enter request dates in DD-MON-YYYY format
accept s_dte char prompt 'Start Date: '
accept e_dte char prompt 'End Date: '
accept min_exec number prompt 'Enter minimum execution time in minutes (0 for all runs) :'
accept que_name char prompt 'Enter beginning or all of the Queue Name: '
ttitle off
set linesize 180
set pagesize 60
set pause off
set verify off
set termout on
ttitle 'CM Analysis Report' skip1
col conc_que format a15 heading "Conc Queue"
col user_name format a15 heading "User"
col reqid format 99999999 heading "Req ID"
col sdate format a9 heading "StDate"
col astart format a8 heading "St Time"
col acomp format a8 heading "End Time"
col rtime format 999,999 heading "ExTme|(Min)"
col wtime format 999,999 heading "WtTme|(Sec)"
col pname1 format a40 heading "Short Name"
col pname2 format a65 heading "Prog Name"
select
trunc(actual_start_date) sdate,
request_id reqid,
substr(user_name,1,15),
to_char(actual_start_date,'HH24:MI:SS') astart,
to_char(actual_completion_date,'HH24:MI:SS') acomp,
((actual_start_date - requested_start_date)*60*60*24) wtime,
((actual_completion_date - actual_start_date)*60*24) rtime,
que.concurrent_queue_name conc_que,
prog.user_concurrent_program_name pname2
from
applsys.fnd_concurrent_queues que,
applsys.fnd_user usr,
-- applsys.fnd_concurrent_programs prog,
apps.fnd_concurrent_programs_vl prog,
applsys.fnd_concurrent_requests req,
applsys.fnd_concurrent_processes proc
where
concurrent_queue_name like '&que_name%'
and
((actual_completion_date - actual_start_date)*60*24) >= &min_exec
and
actual_start_date between
to_date('&s_dte','DD-MON-YYYY') and
(to_date('&e_dte','DD-MON-YYYY') + 1)
and
que.application_id= proc.queue_application_id
and
que.concurrent_queue_id = proc.concurrent_queue_id
and
req.controlling_manager= proc.concurrent_process_id
and
usr.user_id = req.requested_by
and
prog.concurrent_program_id = req.concurrent_program_id
and
prog.application_id = req.program_application_id
and prog.concurrent_program_name not in
('ACTIVATE','ABORT','DEACTIVATE','VERIFY')
order by
concurrent_queue_name,
actual_start_date
-- trunc(actual_start_date),
-- ((actual_completion_date - actual_start_date)*60*60*24) desc/* This script returns information on all jobs run within the date */
/* range with a run time >= the minimum execution time. */
/* Required Input: Start/End date; minimum execution time in minutes */
/* This script also includes running request parameters*/
/
ttitle off
22.All Requests run within the date range
spool off
prompt Enter request dates in DD-MON-YYYY format
accept s_dte char prompt 'Start Date: '
accept e_dte char prompt 'End Date: '
accept min_exec number prompt 'Enter minimum execution time in minutes (0 for all runs) :'
prompt Enter filename for report file.
prompt If no report is desired, simply hit <Enter>
accept spool_file char prompt 'Report File: '
spool &spool_file
ttitle off
set linesize 160
set pagesize 100
set newpage 0
set pause off
set termout on
ttitle 'CM Analysis Report' skip1
break on sdate skip page
--break on rtime
col conc_que format a15 heading "Conc Queue"
col user_name format a12 heading "User ID"
col reqid format 99999999 heading "Req ID"
col sdate format a9 heading "St Date"
col astart format a8 heading "St Time"
col acomp format a8 heading "End Time"
col rtime format 99,999 heading "ExTime|(Min)"
col wtime format 99,999 heading "Wait|Time|(Sec)"
col pname1 format a15 heading "Short|Name"
col pname2 format a51 heading "Prog Name"
col args format a30 heading "Arguments"
select
trunc(actual_start_date) sdate,
req.request_id reqid,
user_name,
to_char(actual_start_date,'HH24:MI:SS') astart,
to_char(actual_completion_date,'HH24:MI:SS') acomp,
((actual_start_date - requested_start_date)*60*24) wtime,
((actual_completion_date - actual_start_date)*60*24) rtime,
que.concurrent_queue_name conc_que,
prog.user_concurrent_program_name pname2,
substr(argument_text,1,30) args
from
applsys.fnd_concurrent_queues que,
applsys.fnd_user usr,
-- applsys.fnd_concurrent_programs prog,
apps.fnd_concurrent_programs_vl prog,
applsys.fnd_concurrent_requests req,
applsys.fnd_concurrent_processes proc
where
actual_start_date between
to_date('&s_dte','DD-MON-YYYY') and
(to_date('&e_dte','DD-MON-YYYY') + 1)
and
((actual_completion_date - actual_start_date)*60*24) >= &min_exec
and
que.application_id= proc.queue_application_id
and
que.concurrent_queue_id = proc.concurrent_queue_id
and
req.controlling_manager= proc.concurrent_process_id
and
usr.user_id = req.requested_by
and
prog.concurrent_program_id = req.concurrent_program_id
and
prog.application_id = req.program_application_id
and prog.concurrent_program_name not in
('ACTIVATE','ABORT','DEACTIVATE','VERIFY')
order by
trunc(actual_start_date),
prog.user_concurrent_program_name,
((actual_completion_date - actual_start_date)*60*60*24) desc
/
spool off
ttitle off
23.Average Pending Time Per Request
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;
24.Script to get duplicated schedules of the same program
----------------------------------------------------------------------------------
--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, REQUESTED_START_DATE, user_name,RESUBMIT_INTERVAL, RESUBMIT_INTERVAL_UNIT_CODE, RESUBMIT_INTERVAL_TYPE_CODE
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, cr.REQUESTED_START_DATE, fu.user_name, cr.RESUBMIT_INTERVAL, cr.RESUBMIT_INTERVAL_UNIT_CODE, cr.RESUBMIT_INTERVAL_TYPE_CODE
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 in ('P','R','I')
AND cr.requested_start_date > SYSDATE
AND cp.LANGUAGE = 'US'
AND fu.user_name like '%') 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, cr.REQUESTED_START_DATE, fu.user_name, cr.RESUBMIT_INTERVAL, cr.RESUBMIT_INTERVAL_UNIT_CODE, cr.RESUBMIT_INTERVAL_TYPE_CODE
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 LIKE '%') 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;
25.Script to get all Concurrent Requests pending with CRM
/* This script returns information on all concurrent manager jobs */
/* pending in the Conflict Resolution Manager */
ttitle off
set linesize 160
set pagesize 100
set newpage 0
set pause off
set termout on
break on reqid skip 1 on rsdate on status_code on user_name
col conc_que format a15 heading "Conc Queue"
col user_name format a12 heading "User ID"
col reqid format 99999999 heading "Req ID"
col sdate format a9 heading "St Date"
col astart format a8 heading "St Time"
col acomp format a8 heading "End Time"
col rtime format 99,999 heading "ExTime|(Min)"
col wtime format 99,999 heading "Wait|Time|(Sec)"
col pname1 format a15 heading "Short|Name"
col pname2 format a25 heading "Prog Name"
col status_code format a8 heading "Status"
col phase_code format a7 heading "Phase"
col rsdate format a15 heading "Req St Time"
select
request_id reqid,
to_char(req.requested_start_date, 'DD-Mon HH24:MI:SS') rsdate,
decode(status_code,'Q', 'Standby', 'F', 'Scheduled', 'T', 'Terminating', 'I', 'Normal', 'R', 'Normal', 'C', 'Normal', 'Other') status_code,
user_name,
req.concurrent_queue_name conc_que,
user_concurrent_program_name pname2
from
fnd_concurrent_worker_requests req,
fnd_concurrent_queues que,
fnd_user usr
where
phase_code = 'P'
and
que.concurrent_queue_name = 'Conflict_Resolution_Manager'
and
que.application_id= req.queue_application_id
and
que.concurrent_queue_id = req.concurrent_queue_id
and
req.requested_by = usr.user_id
and
requested_start_date <= sysdate
and
concurrent_program_name not in
('ACTIVATE','ABORT','DEACTIVATE','VERIFY')
order by request_id, requested_start_date
/
ttitle off
26.Retrieving Concurrent Request Id from the Session SID
Retrieve conc request id and database sid from os pid with the following query
select request_id, p.spid,s.* from apps.fnd_concurrent_requests r , v$process p , v$session s
where r.request_id =
(select request_id from fnd_concurrent_requests fcr
where fcr.oracle_process_id=&OS_PID
and trunc(fcr.request_date)=trunc(sysdate))
and p.SPID = r.ORACLE_PROCESS_ID
and p.ADDR = s.PADDR;
Retrieve the currently running request_id from database sid with the following query.
select f.request_id, v.spid,s.sid, s.username,s.serial#, s.osuser, s.status
from v$process v, v$session s, applsys.fnd_concurrent_requests f
where s.paddr=v.addr
and f.oracle_process_id=v.spid
and trunc(f.request_date)=trunc(sysdate)
and sid=&SID;
27.Concurrent Users
Select ( Select substr( fu.user_name, 1, 20 ) FROM fnd_user fu WHERE fu.user_id = cr.requested_by )
"User_Name", TO_CHAR ( NVL ( cr.actual_start_date, cr.requested_start_date ),
'DD-MON-YYYY HH24:MI' ) "Start_Time", cr.request_id, cr.oracle_process_id spid, vp.pid pid,
to_number(cr.os_process_id) "OS Process Id",
cr.oracle_session_id AUDSID, vs.sid sid, vs.serial# serial#,
( SELECT SUBSTR ( cp.concurrent_program_name || ' - ' || cpl.user_concurrent_program_name, 1, 40 )
FROM fnd_concurrent_programs cp, fnd_concurrent_programs_tl cpl
WHERE cp.application_id = cr.program_application_id
AND cp.concurrent_program_id = cr.concurrent_program_id
AND cpl.application_id = cr.program_application_id
AND cpl.concurrent_program_id = cr.concurrent_program_id
AND cpl.language= USERENV('LANG') ) "Concurrent_Program"
FROM fnd_concurrent_requests cr, v$process vp, v$session vs
WHERE cr.phase_code NOT IN ('I', 'C') AND cr.status_code NOT IN ('U','X','D','E','I','C')
AND cr.oracle_process_id = vp.spid(+) AND cr.oracle_session_id = vs.audsid(+) ORDER BY 1,2,3
/
28.Manager that ran a Concurrent Request
select b.USER_CONCURRENT_QUEUE_NAME from fnd_concurrent_processes a,
fnd_concurrent_queues_vl b, fnd_concurrent_requests c
where a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID
and a.CONCURRENT_PROCESS_ID = c.controlling_manager
and c.request_id = '&1'
/
29.Trace File for a Concurrent Request Id
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
/
30.Currently Running Concurrent Requests
SET PAGESIZE 9999;
SET VERIFY off;
SET FEEDBACK off;
SET LINESIZE 132;
--
COLUMN concreq HEADING 'Conc Req ID' FORMAT A12;
COLUMN clproc HEADING 'Client|Process' FORMAT A11;
COLUMN opid HEADING 'ORACLE PID' FORMAT A10;
COLUMN reqph HEADING 'Req Phase' FORMAT A10;
COLUMN reqst HEADING 'Req Status' FORMAT A10;
COLUMN dbuser HEADING 'DB User' FORMAT A10;
COLUMN svrproc HEADING 'Srvr|Process' FORMAT A10;
COLUMN sid HEADING 'SID' FORMAT 99999;
COLUMN serial# HEADING 'Serial#' FORMAT 99999;
SELECT SUBSTR(LTRIM(req.request_id),1,15) concreq,
SUBSTR(proc.os_process_id,1,15) clproc,
SUBSTR(LTRIM(proc.oracle_process_id),1,15) opid,
SUBSTR(look.meaning,1,10) reqph,
SUBSTR(look1.meaning,1,10) reqst,
SUBSTR(vsess.username,1,10) dbuser,
SUBSTR(vproc.spid,1,10) svrproc,
vsess.sid sid,
vsess.serial# serial#
FROM fnd_concurrent_requests req,
fnd_concurrent_processes proc,
fnd_lookups look,
fnd_lookups look1,
v$process vproc,
v$session vsess
WHERE req.controlling_manager = proc.concurrent_process_id(+)
AND req.status_code = look.lookup_code
AND look.lookup_type = 'CP_STATUS_CODE'
AND req.phase_code = look1.lookup_code
AND look1.lookup_type = 'CP_PHASE_CODE'
AND look1.meaning = 'Running'
AND proc.oracle_process_id = vproc.pid(+)
AND vproc.addr = vsess.paddr(+);
/
31.Time Taken for Requests of a Concurrent Program (Parameter = Program in Upper case)
col ARGUMENT_TEXT format a40;
col USER_CONCURRENT_PROGRAM_NAME format a40;
select
REQUEST_ID,REQUEST_DATE,PHASE_CODE, STATUS_CODE,
ARGUMENT_TEXT,
to_char(ACTUAL_START_DATE,'dd-mon-yy hh:mi:ss') Startdt,
ORACLE_PROCESS_ID,LOGFILE_NAME,
to_char(ACTUAL_COMPLETION_DATE,'dd-mon-yy hh:mi:ss') Stopdt,
(ACTUAL_COMPLETION_DATE-ACTUAL_START_DATE)*24 timetaken_hours ,
clist.USER_CONCURRENT_PROGRAM_NAME
from fnd_concurrent_requests creq,FND_CONCURRENT_PROGRAMS_VL clist
where creq.CONCURRENT_PROGRAM_ID= clist.CONCURRENT_PROGRAM_ID
and upper( clist.USER_CONCURRENT_PROGRAM_NAME)
like '%program%'
order by ACTUAL_START_DATE;
/
32.pending jobs in queue that should be running
set lines 132
set pages 30
col program format a45 trunc
col phase_code format a1 trunc
col statustxt format a15 trunc
col parent format a8 trunc
col reqstarthide noprint
col minlate format 99999.90 head MinLate
set verify off
prompt Note: Minutes Late shown in decimal minutes.....
select a.requested_start_date reqstarthide,
to_char(a.requested_start_date,'mm/dd/yy hh24:mi') reqstart,
(sysdate-requested_start_date)*1440 minlate,
a.request_id "Req Id",
decode(a.parent_request_id,-1,NULL,a.parent_request_id) "Parent",
a.concurrent_program_id "Prg Id",
a.phase_code,
a.status_code || ' - ' ||l1.meaning statustxt,
c.concurrent_program_name||' - '|| c2.user_concurrent_program_name "program"
from APPLSYS.fnd_Concurrent_requests a,
APPLSYS.fnd_concurrent_programs_tl c2,
APPLSYS.fnd_concurrent_programs c,
applsys.fnd_lookup_values l1
where 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.language = 'US'
and c2.application_id = c.application_id
and a.actual_start_date is null
and a.status_code in ('A','H','I','M','P','Q','R')
and a.phase_code in ('P','I')
and sysdate - a.requested_start_date < 2
and a.requested_start_date < sysdate
and l1.lookup_type = 'CP_STATUS_CODE'
and l1.lookup_code = a.status_code
and l1.language = 'US'
and l1.enabled_flag = 'Y'
and (l1.start_date_active <= sysdate and l1.start_date_active is not null)
and (l1.end_date_active > sysdate or l1.end_date_active is null)
order by 1;
/
33.List Scheduled Concurrent Requests
SELECT cr.request_id,
DECODE (cp.user_concurrent_program_name,
'Report Set', 'Report Set:' || cr.description,
cp.user_concurrent_program_name
) NAME,
argument_text, cr.resubmit_interval,
NVL2 (cr.resubmit_interval,
'PERIODICALLY',
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS',
'ONCE')
) schedule_type,
DECODE (NVL2 (cr.resubmit_interval,
'PERIODICALLY',
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS',
'ONCE')
),
'PERIODICALLY', 'EVERY '
|| cr.resubmit_interval
|| ' '
|| cr.resubmit_interval_unit_code
|| ' FROM '
|| cr.resubmit_interval_type_code
|| ' OF PREV RUN',
'ONCE', 'AT :'
|| TO_CHAR (cr.requested_start_date, 'DD-MON-RR
HH24:MI'),
'EVERY: ' || fcr.class_info
) schedule,
fu.user_name, requested_start_date
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_requests cr,
apps.fnd_user fu,
apps.fnd_conc_release_classes fcr
WHERE cp.application_id =
cr.program_application_id
AND cp.concurrent_program_id =
cr.concurrent_program_id
AND cr.requested_by = fu.user_id
AND cr.phase_code = 'P'
AND cr.requested_start_date > SYSDATE
AND cp.LANGUAGE = 'US'
AND fcr.release_class_id(+) = cr.release_class_id
AND fcr.application_id(+) =
cr.release_class_app_id
/
34.concurrent requests that ran for over X minutes
set lines 300
set trims on
set pages 60
set head on
ttit off
btit off
alter session set nls_Date_format='DD-MON-RRRR HH24:MI';
accept minutes prompt "Enter runtime minutes limit to report [DEFAULT=60]: "
col runtime format 9999999 heading "Runtime|[min]"
col asd format a23 Heading "Start Time"
col acd format a23 Heading "End Time"
col cpn format a15 heading "Program"
col ucpn format a25 trunc heading "Program Name"
--col logfile_name format a85 heading "Log File" newline
--col argument_text format a300 heading "Arguments" newline
SELECT f.request_id
, TRUNC(((f.actual_completion_date-f.actual_start_date)/(1/24))*60) runtime
, f.actual_start_date asd
, f.actual_completion_date acd
, p.concurrent_program_name,
, pt.user_concurrent_program_name ucpn
, f.phase_code
, f.status_code
--, f.logfile_name
--, f.argument_text
FROM apps.fnd_concurrent_programs p
, apps.fnd_concurrent_programs_tl pt
, apps.fnd_concurrent_requests f
WHERE TRUNC(((f.actual_completion_date-f.actual_start_date)/(1/24))*60) > NVL('&minutes',60)
and 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')
ORDER by f.actual_completion_date-f.actual_start_date desc
spool a_cm_long_reqs.log
/
35.Long Running Concurrent Requests
SET LINESIZE 132;
--
COLUMN request_id HEADING 'Request ID' FORMAT 99999999;
COLUMN strttime HEADING 'Start|Time' FORMAT A17;
COLUMN endtime HEADING 'End|Time' FORMAT A17;
COLUMN rtime HEADING 'Elapsed|(Min)' FORMAT 9990.99;
SELECT REQUEST_ID,
TO_CHAR(ACTUAL_START_DATE,'MM/DD/YY HH:MI:SS') strttime,
TO_CHAR(ACTUAL_COMPLETION_DATE,'MM/DD/YY HH:MI:SS') endtime,
-- ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE time,
-- TO_NUMBER(TO_CHAR(ACTUAL_COMPLETION_DATE,'JSSSSS') ) - TO_NUMBER(TO_CHAR(ACTUAL_START_DATE,'JSSSSS')) seconds,
ROUND((ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE)*(60*24),2) rtime,
OUTCOME_CODE,
SUBSTR(completion_text,1,20) compl_txt
FROM fnd_concurrent_requests
-- WHERE TO_NUMBER(TO_CHAR(ACTUAL_COMPLETION_DATE,'JSSSSS') ) - TO_NUMBER(TO_CHAR(ACTUAL_START_DATE,'JSSSSS')) > 3600
-- WHERE ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE > 0.01
WHERE ACTUAL_START_DATE > sysdate-10
ORDER BY 2
/
36.SID (Session Id) and SPID (OS Process Id) for a Concurrent Request
set lines 140
set pages 120
select sid,serial#,username,status, to_char(logon_time,'dd-mm-yy hh:mi:ss') "LOGON", program
from gv$session where paddr IN ( select addr from gv$process where spid in (select ORACLE_PROCESS_ID from apps.fnd_concurrent_requests where request_id='&1'));
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.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;
select s.sid ,s.username,l.address,l.sql_text sql_text , round(last_call_et/60,2) called_at
from v$sqltext_with_newlines l, v$session s
where l.address=s.sql_address
and l.hash_value=s.sql_hash_value
and s.sid in (select sid from v$session where paddr=(select addr from v$process where spid in
(select oracle_process_id from apps.Fnd_Concurrent_Requests where request_id='&1')))
order by s.sid,l.address,l.piece
15.Concurrent Request Status
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 b.inst_id,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.inst_id,c.username,c.sid,c.serial#,
c.program,d.spid from gv$session c, gv$process d
where c.paddr=d.addr) b,
apps.fnd_concurrent_programs_tl e,
apps.fnd_user f,
gv$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';
16.Concurrent Program which has tracing
select concurrent_program_name from apps.fnd_concurrent_programs where enable_trace='Y';
17.How to put request in Complete State from backend
update fnd_concurrent_requests set status_code='D', phase_code='C' where request_id='28406642';
18.Processors and Session info for one Concurrent Request
bde_request.sql - Process and Session info for one Concurrent Request (11.5) [ID 187504.1]
================================================================================================
SET ver OFF term ON feed OFF pages 255 lin 255 trims ON;
PROMPT Usage:
PROMPT sqlplus apps/apps
PROMPT SQL> START bde_request.sql <request_id>
PROMPT
SET term OFF;
DEF p_request_id = &1;
COL p_requested_by NEW_VALUE p_requested_by -
FORMAT 999999999999999;
COL p_phase_code NEW_VALUE p_phase_code -
FORMAT A1;
COL p_status_code NEW_VALUE p_status_code -
FORMAT A1;
COL p_program_application_id NEW_VALUE p_program_application_id -
FORMAT 999999999999999;
COL p_concurrent_program_id NEW_VALUE p_concurrent_program_id -
FORMAT 999999999999999;
COL p_controlling_manager NEW_VALUE p_controlling_manager -
FORMAT 999999999999999;
COL p_oracle_process_id NEW_VALUE p_oracle_process_id -
FORMAT A30;
COL p_oracle_session_id NEW_VALUE p_oracle_session_id -
FORMAT 999999999999999;
COL p_executable_application_id NEW_VALUE p_executable_application_id -
FORMAT 999999999999999;
COL p_executable_id NEW_VALUE p_executable_id -
FORMAT 999999999999999;
COL p_addr NEW_VALUE p_addr -
FORMAT A8;
COL p_sid NEW_VALUE p_sid -
FORMAT 99999;
COL request_id FORMAT 9999999999;
COL request_date FORMAT A20;
COL requested_by FORMAT 999999999999;
COL phase_code FORMAT A10;
COL status_code FORMAT A11;
COL requested_start_date FORMAT A20;
COL program_application_id FORMAT 9999999999999999999999;
COL concurrent_program_id FORMAT 999999999999999999999;
COL controlling_manager FORMAT 9999999999999999999;
COL actual_start_date FORMAT A20;
COL actual_completion_date FORMAT A22;
COL current_date FORMAT A20;
COL duration FORMAT 99999.99;
COL logfile_name FORMAT A70;
COL outfile_name FORMAT A70;
COL oracle_process_id FORMAT A17;
COL oracle_session_id FORMAT 99999999999999999;
COL os_process_id FORMAT A13;
COL enable_trace FORMAT A12;
COL user_id FORMAT 999999999999;
COL user_name FORMAT A20;
COL description FORMAT A60;
COL lookup_type FORMAT A11;
COL meaning FORMAT A11;
COL application_id FORMAT 99999999999999;
COL concurrent_program_name FORMAT A23;
COL executable_application_id FORMAT 9999999999999999999999999;
COL executable_id FORMAT 9999999999999;
COL optimizer_mode FORMAT A14;
COL language FORMAT A20;
COL user_concurrent_program_name FORMAT A80;
COL executable_name FORMAT A15;
COL execution_file_name FORMAT A19;
COL subroutine_name FORMAT A15;
COL concurrent_process_id FORMAT 999999999999999999999;
COL session_id FORMAT 999999999999999;
COL node_name FORMAT A20;
COL db_name FORMAT A8;
COL db_domain FORMAT A30;
COL logfile_name FORMAT A70;
COL addr FORMAT A8;
COL pid FORMAT 99999999;
COL spid FORMAT A9;
COL serial# FORMAT 9999999999;
COL saddr FORMAT A8;
COL sid FORMAT 9999999999;
COL serial# FORMAT 9999999999;
COL audsid FORMAT 9999999999;
COL paddr FORMAT A8;
COL process FORMAT A9;
COL module FORMAT A24;
COL action FORMAT A32;
COL client_info FORMAT A64;
SELECT requested_by p_requested_by,
phase_code p_phase_code,
status_code p_status_code,
program_application_id p_program_application_id,
concurrent_program_id p_concurrent_program_id,
controlling_manager p_controlling_manager,
oracle_process_id p_oracle_process_id,
oracle_session_id p_oracle_session_id
FROM fnd_concurrent_requests
WHERE request_id = TO_NUMBER('&p_request_id');
SELECT executable_application_id p_executable_application_id,
executable_id p_executable_id
FROM fnd_concurrent_programs
WHERE application_id = &p_program_application_id
AND concurrent_program_id = &p_concurrent_program_id;
SELECT addr p_addr
FROM v$process
WHERE spid = '&p_oracle_process_id';
SELECT sid p_sid
FROM v$session
WHERE paddr = '&p_addr';
SPOOL bde_request.txt;
SET term ON;
PROMPT
PROMPT
PROMPT FND_CONCURRENT_REQUESTS
PROMPT =======================
PROMPT
SELECT request_id,
requested_by,
phase_code,
status_code,
program_application_id,
concurrent_program_id,
controlling_manager,
oracle_process_id,
oracle_session_id,
os_process_id,
enable_trace
FROM fnd_concurrent_requests
WHERE request_id = TO_NUMBER('&p_request_id');
PROMPT
PROMPT REQUESTED_BY ref FND_USER.USER_ID
PROMPT PHASE_CODE ref FND_LOOKUPS.LOOKUP_CODE (LOOKUP_TYPE = 'CP_PHASE_CODE')
PROMPT STATUS_CODE ref FND_LOOKUPS.LOOKUP_CODE (LOOKUP_TYPE = 'CP_STATUS_CODE')
PROMPT PROGRAM_APPLICATION_ID ref FND_CONCURRENT_PROGRAMS.APPLICATION_ID
PROMPT CONCURRENT_PROGRAM_ID ref FND_CONCURRENT_PROGRAMS.CONCURRENT_PROGRAM_ID
PROMPT CONTROLLING_MANAGER ref FND_CONCURRENT_PROCESSES.CONCURRENT_PROCESS_ID
PROMPT ORACLE_PROCESS_ID ref V$PROCESS.SPID (identifies SQL Trace filename)
PROMPT ORACLE_SESSION_ID ref V$SESSION.AUDSID
PROMPT
PROMPT OS_PROCESS_ID is Operating System Process ID for Concurrent Program
PROMPT
SELECT request_id,
TO_CHAR( request_date, 'DD-MON-YYYY HH24:MI:SS' )
request_date,
TO_CHAR( requested_start_date,'DD-MON-YYYY HH24:MI:SS' )
requested_start_date,
TO_CHAR( actual_start_date, 'DD-MON-YYYY HH24:MI:SS' )
actual_start_date,
TO_CHAR( actual_completion_date, 'DD-MON-YYYY HH24:MI:SS' )
actual_completion_date,
TO_CHAR( sysdate, 'DD-MON-YYYY HH24:MI:SS' )
current_date,
ROUND( ( NVL( actual_completion_date, sysdate ) - actual_start_date ) * 24, 2 )
duration
FROM fnd_concurrent_requests
WHERE request_id = TO_NUMBER('&p_request_id');
SELECT request_id,
logfile_name,
outfile_name,
oracle_process_id
FROM fnd_concurrent_requests
WHERE request_id = TO_NUMBER('&p_request_id');
PROMPT
PROMPT ORACLE_PROCESS_ID identifies SQL Trace filename under udump directory
PROMPT
PROMPT
PROMPT
PROMPT FND_USER
PROMPT ========
PROMPT
SELECT user_id,
user_name,
description
FROM fnd_user
WHERE user_id = &p_requested_by;
PROMPT
PROMPT
PROMPT FND_LOOKUPS
PROMPT ===========
PROMPT
SELECT SUBSTR(flu1.lookup_type,4,11) lookup_type,
fcr.phase_code,
flu1.meaning,
SUBSTR(flu2.lookup_type,4,11) lookup_type,
fcr.status_code,
flu2.meaning
FROM fnd_concurrent_requests fcr,
fnd_lookups flu1,
fnd_lookups flu2
WHERE fcr.request_id = TO_NUMBER('&p_request_id')
AND flu1.lookup_type = 'CP_PHASE_CODE'
AND fcr.phase_code = flu1.lookup_code
AND flu2.lookup_type = 'CP_STATUS_CODE'
AND fcr.status_code = flu2.lookup_code;
PROMPT
PROMPT
PROMPT FND_CONCURRENT_PROGRAMS
PROMPT =======================
PROMPT
SELECT application_id,
concurrent_program_id,
concurrent_program_name,
executable_application_id,
executable_id,
enable_trace,
optimizer_mode
FROM fnd_concurrent_programs
WHERE application_id = &p_program_application_id
AND concurrent_program_id = &p_concurrent_program_id;
PROMPT
PROMPT EXECUTABLE_APPLICATION_ID ref FND_EXECUTABLES.APPLICATION_ID
PROMPT EXECUTABLE_ID ref FND_EXECUTABLES.EXECUTABLE_ID
PROMPT
PROMPT
PROMPT
PROMPT FND_CONCURRENT_PROGRAMS_TL
PROMPT ==========================
PROMPT
SELECT application_id,
concurrent_program_id,
language,
user_concurrent_program_name
FROM fnd_concurrent_programs_tl
WHERE application_id = &p_program_application_id
AND concurrent_program_id = &p_concurrent_program_id
AND language = USERENV('LANG');
PROMPT
PROMPT
PROMPT FND_EXECUTABLES
PROMPT ===============
PROMPT
SELECT application_id,
executable_id,
executable_name,
execution_file_name,
subroutine_name
FROM fnd_executables
WHERE application_id = &p_executable_application_id
AND executable_id = &p_executable_id;
PROMPT
PROMPT
PROMPT FND_CONCURRENT_PROCESSES
PROMPT ========================
PROMPT
SELECT concurrent_process_id,
session_id,
TO_CHAR( oracle_process_id ) oracle_process_id,
os_process_id,
node_name,
db_name,
db_domain
FROM fnd_concurrent_processes
WHERE concurrent_process_id = TO_NUMBER( TO_CHAR( '&p_controlling_manager' ) );
PROMPT
PROMPT OS_PROCESS_ID is Operating System Process ID for Concurrent Manager
PROMPT
SELECT concurrent_process_id,
logfile_name
FROM fnd_concurrent_processes
WHERE concurrent_process_id = TO_NUMBER( TO_CHAR( '&p_controlling_manager' ) );
PROMPT
PROMPT
PROMPT V$PROCESS
PROMPT =========
PROMPT
SELECT addr,
pid,
spid,
serial#
FROM v$process
WHERE spid = '&p_oracle_process_id';
PROMPT
PROMPT
PROMPT V$SESSION
PROMPT =========
PROMPT
SELECT saddr,
sid,
serial#,
audsid,
paddr,
process
FROM v$session
WHERE paddr = '&p_addr'
AND audsid = TO_NUMBER( TO_CHAR( '&p_oracle_session_id' ) );
PROMPT
PROMPT PADDR ref V$PROCESS.ADDR
PROMPT
SELECT saddr,
sid,
serial#,
module,
action,
client_info
FROM v$session
WHERE paddr = '&p_addr'
AND audsid = TO_NUMBER( TO_CHAR( '&p_oracle_session_id' ) );
SPOOL OFF;
SET ver ON feed ON pages 24 lin 80 trims OFF;
PROMPT
PROMPT Executing bde_session.sql for sid &p_sid
PROMPT
START bde_session.sql &p_sid;
PROMPT
COLUMN ENDEDCR FORMAT A21 HEADING 'bde_request.sql ended';
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') ENDEDCR FROM SYS.DUAL;
19.Retrieve the log messages for a Concurrent Request when Debug is Enabled
SELECT LOG.message_text message
FROM FND_LOG_MESSAGES LOG,
FND_LOG_TRANSACTION_CONTEXT CON
WHERE CON.TRANSACTION_ID = '&con_request_id'
AND CON.TRANSACTION_TYPE = 'REQUEST'
AND CON.TRANSACTION_CONTEXT_ID = LOG.TRANSACTION_CONTEXT_ID
ORDER BY LOG.LOG_SEQUENCE;
20.How to see the top10 Requests based on total runtime on a given day.
/* top10jobs.sql
see the top10 jobs based on total runtime for today
*/
col program form A35 trunc head "Program Full Name"
col intprog format a35 trunc head "Internal Name"
col TotTime form 99999
col AvgTime form 99999.90
col qname head "Queue" format a15 trunc
select * from (
select q.concurrent_queue_name qname,
c.concurrent_program_name ||
' (' || to_char(c.concurrent_program_id) || ')' intprog,
ctl.user_concurrent_program_name "program",
sum((nvl(actual_completion_date,sysdate)-actual_start_date)*1440) "TotTime",
count(*),
avg((nvl(actual_completion_date,sysdate)-actual_start_date)*1440) "AvgTime"
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 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'
and trunc(actual_completion_date) = trunc(sysdate)
and actual_start_date is not null
and actual_completion_date is not null
group by q.concurrent_queue_name,
c.concurrent_program_name || ' (' || to_char(c.concurrent_program_id) || ')' ,
ctl.user_concurrent_program_name
order by 4 desc)
where rownum < 11;
21.Requests run by a specific Concurrent Manager/Queue within the date range
* This script returns information on all jobs run through a specific */
/* queue within the date range with a run time >= the minimum execution */
/* time. */
/* Required Input: Start/End date; minimum execution time in seconds; */
/* the long program name */
prompt Enter request dates in DD-MON-YYYY format
accept s_dte char prompt 'Start Date: '
accept e_dte char prompt 'End Date: '
accept min_exec number prompt 'Enter minimum execution time in minutes (0 for all runs) :'
accept que_name char prompt 'Enter beginning or all of the Queue Name: '
ttitle off
set linesize 180
set pagesize 60
set pause off
set verify off
set termout on
ttitle 'CM Analysis Report' skip1
col conc_que format a15 heading "Conc Queue"
col user_name format a15 heading "User"
col reqid format 99999999 heading "Req ID"
col sdate format a9 heading "StDate"
col astart format a8 heading "St Time"
col acomp format a8 heading "End Time"
col rtime format 999,999 heading "ExTme|(Min)"
col wtime format 999,999 heading "WtTme|(Sec)"
col pname1 format a40 heading "Short Name"
col pname2 format a65 heading "Prog Name"
select
trunc(actual_start_date) sdate,
request_id reqid,
substr(user_name,1,15),
to_char(actual_start_date,'HH24:MI:SS') astart,
to_char(actual_completion_date,'HH24:MI:SS') acomp,
((actual_start_date - requested_start_date)*60*60*24) wtime,
((actual_completion_date - actual_start_date)*60*24) rtime,
que.concurrent_queue_name conc_que,
prog.user_concurrent_program_name pname2
from
applsys.fnd_concurrent_queues que,
applsys.fnd_user usr,
-- applsys.fnd_concurrent_programs prog,
apps.fnd_concurrent_programs_vl prog,
applsys.fnd_concurrent_requests req,
applsys.fnd_concurrent_processes proc
where
concurrent_queue_name like '&que_name%'
and
((actual_completion_date - actual_start_date)*60*24) >= &min_exec
and
actual_start_date between
to_date('&s_dte','DD-MON-YYYY') and
(to_date('&e_dte','DD-MON-YYYY') + 1)
and
que.application_id= proc.queue_application_id
and
que.concurrent_queue_id = proc.concurrent_queue_id
and
req.controlling_manager= proc.concurrent_process_id
and
usr.user_id = req.requested_by
and
prog.concurrent_program_id = req.concurrent_program_id
and
prog.application_id = req.program_application_id
and prog.concurrent_program_name not in
('ACTIVATE','ABORT','DEACTIVATE','VERIFY')
order by
concurrent_queue_name,
actual_start_date
-- trunc(actual_start_date),
-- ((actual_completion_date - actual_start_date)*60*60*24) desc/* This script returns information on all jobs run within the date */
/* range with a run time >= the minimum execution time. */
/* Required Input: Start/End date; minimum execution time in minutes */
/* This script also includes running request parameters*/
/
ttitle off
22.All Requests run within the date range
spool off
prompt Enter request dates in DD-MON-YYYY format
accept s_dte char prompt 'Start Date: '
accept e_dte char prompt 'End Date: '
accept min_exec number prompt 'Enter minimum execution time in minutes (0 for all runs) :'
prompt Enter filename for report file.
prompt If no report is desired, simply hit <Enter>
accept spool_file char prompt 'Report File: '
spool &spool_file
ttitle off
set linesize 160
set pagesize 100
set newpage 0
set pause off
set termout on
ttitle 'CM Analysis Report' skip1
break on sdate skip page
--break on rtime
col conc_que format a15 heading "Conc Queue"
col user_name format a12 heading "User ID"
col reqid format 99999999 heading "Req ID"
col sdate format a9 heading "St Date"
col astart format a8 heading "St Time"
col acomp format a8 heading "End Time"
col rtime format 99,999 heading "ExTime|(Min)"
col wtime format 99,999 heading "Wait|Time|(Sec)"
col pname1 format a15 heading "Short|Name"
col pname2 format a51 heading "Prog Name"
col args format a30 heading "Arguments"
select
trunc(actual_start_date) sdate,
req.request_id reqid,
user_name,
to_char(actual_start_date,'HH24:MI:SS') astart,
to_char(actual_completion_date,'HH24:MI:SS') acomp,
((actual_start_date - requested_start_date)*60*24) wtime,
((actual_completion_date - actual_start_date)*60*24) rtime,
que.concurrent_queue_name conc_que,
prog.user_concurrent_program_name pname2,
substr(argument_text,1,30) args
from
applsys.fnd_concurrent_queues que,
applsys.fnd_user usr,
-- applsys.fnd_concurrent_programs prog,
apps.fnd_concurrent_programs_vl prog,
applsys.fnd_concurrent_requests req,
applsys.fnd_concurrent_processes proc
where
actual_start_date between
to_date('&s_dte','DD-MON-YYYY') and
(to_date('&e_dte','DD-MON-YYYY') + 1)
and
((actual_completion_date - actual_start_date)*60*24) >= &min_exec
and
que.application_id= proc.queue_application_id
and
que.concurrent_queue_id = proc.concurrent_queue_id
and
req.controlling_manager= proc.concurrent_process_id
and
usr.user_id = req.requested_by
and
prog.concurrent_program_id = req.concurrent_program_id
and
prog.application_id = req.program_application_id
and prog.concurrent_program_name not in
('ACTIVATE','ABORT','DEACTIVATE','VERIFY')
order by
trunc(actual_start_date),
prog.user_concurrent_program_name,
((actual_completion_date - actual_start_date)*60*60*24) desc
/
spool off
ttitle off
23.Average Pending Time Per Request
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;
24.Script to get duplicated schedules of the same program
----------------------------------------------------------------------------------
--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, REQUESTED_START_DATE, user_name,RESUBMIT_INTERVAL, RESUBMIT_INTERVAL_UNIT_CODE, RESUBMIT_INTERVAL_TYPE_CODE
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, cr.REQUESTED_START_DATE, fu.user_name, cr.RESUBMIT_INTERVAL, cr.RESUBMIT_INTERVAL_UNIT_CODE, cr.RESUBMIT_INTERVAL_TYPE_CODE
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 in ('P','R','I')
AND cr.requested_start_date > SYSDATE
AND cp.LANGUAGE = 'US'
AND fu.user_name like '%') 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, cr.REQUESTED_START_DATE, fu.user_name, cr.RESUBMIT_INTERVAL, cr.RESUBMIT_INTERVAL_UNIT_CODE, cr.RESUBMIT_INTERVAL_TYPE_CODE
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 LIKE '%') 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;
25.Script to get all Concurrent Requests pending with CRM
/* This script returns information on all concurrent manager jobs */
/* pending in the Conflict Resolution Manager */
ttitle off
set linesize 160
set pagesize 100
set newpage 0
set pause off
set termout on
break on reqid skip 1 on rsdate on status_code on user_name
col conc_que format a15 heading "Conc Queue"
col user_name format a12 heading "User ID"
col reqid format 99999999 heading "Req ID"
col sdate format a9 heading "St Date"
col astart format a8 heading "St Time"
col acomp format a8 heading "End Time"
col rtime format 99,999 heading "ExTime|(Min)"
col wtime format 99,999 heading "Wait|Time|(Sec)"
col pname1 format a15 heading "Short|Name"
col pname2 format a25 heading "Prog Name"
col status_code format a8 heading "Status"
col phase_code format a7 heading "Phase"
col rsdate format a15 heading "Req St Time"
select
request_id reqid,
to_char(req.requested_start_date, 'DD-Mon HH24:MI:SS') rsdate,
decode(status_code,'Q', 'Standby', 'F', 'Scheduled', 'T', 'Terminating', 'I', 'Normal', 'R', 'Normal', 'C', 'Normal', 'Other') status_code,
user_name,
req.concurrent_queue_name conc_que,
user_concurrent_program_name pname2
from
fnd_concurrent_worker_requests req,
fnd_concurrent_queues que,
fnd_user usr
where
phase_code = 'P'
and
que.concurrent_queue_name = 'Conflict_Resolution_Manager'
and
que.application_id= req.queue_application_id
and
que.concurrent_queue_id = req.concurrent_queue_id
and
req.requested_by = usr.user_id
and
requested_start_date <= sysdate
and
concurrent_program_name not in
('ACTIVATE','ABORT','DEACTIVATE','VERIFY')
order by request_id, requested_start_date
/
ttitle off
26.Retrieving Concurrent Request Id from the Session SID
Retrieve conc request id and database sid from os pid with the following query
select request_id, p.spid,s.* from apps.fnd_concurrent_requests r , v$process p , v$session s
where r.request_id =
(select request_id from fnd_concurrent_requests fcr
where fcr.oracle_process_id=&OS_PID
and trunc(fcr.request_date)=trunc(sysdate))
and p.SPID = r.ORACLE_PROCESS_ID
and p.ADDR = s.PADDR;
Retrieve the currently running request_id from database sid with the following query.
select f.request_id, v.spid,s.sid, s.username,s.serial#, s.osuser, s.status
from v$process v, v$session s, applsys.fnd_concurrent_requests f
where s.paddr=v.addr
and f.oracle_process_id=v.spid
and trunc(f.request_date)=trunc(sysdate)
and sid=&SID;
27.Concurrent Users
Select ( Select substr( fu.user_name, 1, 20 ) FROM fnd_user fu WHERE fu.user_id = cr.requested_by )
"User_Name", TO_CHAR ( NVL ( cr.actual_start_date, cr.requested_start_date ),
'DD-MON-YYYY HH24:MI' ) "Start_Time", cr.request_id, cr.oracle_process_id spid, vp.pid pid,
to_number(cr.os_process_id) "OS Process Id",
cr.oracle_session_id AUDSID, vs.sid sid, vs.serial# serial#,
( SELECT SUBSTR ( cp.concurrent_program_name || ' - ' || cpl.user_concurrent_program_name, 1, 40 )
FROM fnd_concurrent_programs cp, fnd_concurrent_programs_tl cpl
WHERE cp.application_id = cr.program_application_id
AND cp.concurrent_program_id = cr.concurrent_program_id
AND cpl.application_id = cr.program_application_id
AND cpl.concurrent_program_id = cr.concurrent_program_id
AND cpl.language= USERENV('LANG') ) "Concurrent_Program"
FROM fnd_concurrent_requests cr, v$process vp, v$session vs
WHERE cr.phase_code NOT IN ('I', 'C') AND cr.status_code NOT IN ('U','X','D','E','I','C')
AND cr.oracle_process_id = vp.spid(+) AND cr.oracle_session_id = vs.audsid(+) ORDER BY 1,2,3
/
28.Manager that ran a Concurrent Request
select b.USER_CONCURRENT_QUEUE_NAME from fnd_concurrent_processes a,
fnd_concurrent_queues_vl b, fnd_concurrent_requests c
where a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID
and a.CONCURRENT_PROCESS_ID = c.controlling_manager
and c.request_id = '&1'
/
29.Trace File for a Concurrent Request Id
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
/
30.Currently Running Concurrent Requests
SET PAGESIZE 9999;
SET VERIFY off;
SET FEEDBACK off;
SET LINESIZE 132;
--
COLUMN concreq HEADING 'Conc Req ID' FORMAT A12;
COLUMN clproc HEADING 'Client|Process' FORMAT A11;
COLUMN opid HEADING 'ORACLE PID' FORMAT A10;
COLUMN reqph HEADING 'Req Phase' FORMAT A10;
COLUMN reqst HEADING 'Req Status' FORMAT A10;
COLUMN dbuser HEADING 'DB User' FORMAT A10;
COLUMN svrproc HEADING 'Srvr|Process' FORMAT A10;
COLUMN sid HEADING 'SID' FORMAT 99999;
COLUMN serial# HEADING 'Serial#' FORMAT 99999;
SELECT SUBSTR(LTRIM(req.request_id),1,15) concreq,
SUBSTR(proc.os_process_id,1,15) clproc,
SUBSTR(LTRIM(proc.oracle_process_id),1,15) opid,
SUBSTR(look.meaning,1,10) reqph,
SUBSTR(look1.meaning,1,10) reqst,
SUBSTR(vsess.username,1,10) dbuser,
SUBSTR(vproc.spid,1,10) svrproc,
vsess.sid sid,
vsess.serial# serial#
FROM fnd_concurrent_requests req,
fnd_concurrent_processes proc,
fnd_lookups look,
fnd_lookups look1,
v$process vproc,
v$session vsess
WHERE req.controlling_manager = proc.concurrent_process_id(+)
AND req.status_code = look.lookup_code
AND look.lookup_type = 'CP_STATUS_CODE'
AND req.phase_code = look1.lookup_code
AND look1.lookup_type = 'CP_PHASE_CODE'
AND look1.meaning = 'Running'
AND proc.oracle_process_id = vproc.pid(+)
AND vproc.addr = vsess.paddr(+);
/
31.Time Taken for Requests of a Concurrent Program (Parameter = Program in Upper case)
col ARGUMENT_TEXT format a40;
col USER_CONCURRENT_PROGRAM_NAME format a40;
select
REQUEST_ID,REQUEST_DATE,PHASE_CODE, STATUS_CODE,
ARGUMENT_TEXT,
to_char(ACTUAL_START_DATE,'dd-mon-yy hh:mi:ss') Startdt,
ORACLE_PROCESS_ID,LOGFILE_NAME,
to_char(ACTUAL_COMPLETION_DATE,'dd-mon-yy hh:mi:ss') Stopdt,
(ACTUAL_COMPLETION_DATE-ACTUAL_START_DATE)*24 timetaken_hours ,
clist.USER_CONCURRENT_PROGRAM_NAME
from fnd_concurrent_requests creq,FND_CONCURRENT_PROGRAMS_VL clist
where creq.CONCURRENT_PROGRAM_ID= clist.CONCURRENT_PROGRAM_ID
and upper( clist.USER_CONCURRENT_PROGRAM_NAME)
like '%program%'
order by ACTUAL_START_DATE;
/
32.pending jobs in queue that should be running
set lines 132
set pages 30
col program format a45 trunc
col phase_code format a1 trunc
col statustxt format a15 trunc
col parent format a8 trunc
col reqstarthide noprint
col minlate format 99999.90 head MinLate
set verify off
prompt Note: Minutes Late shown in decimal minutes.....
select a.requested_start_date reqstarthide,
to_char(a.requested_start_date,'mm/dd/yy hh24:mi') reqstart,
(sysdate-requested_start_date)*1440 minlate,
a.request_id "Req Id",
decode(a.parent_request_id,-1,NULL,a.parent_request_id) "Parent",
a.concurrent_program_id "Prg Id",
a.phase_code,
a.status_code || ' - ' ||l1.meaning statustxt,
c.concurrent_program_name||' - '|| c2.user_concurrent_program_name "program"
from APPLSYS.fnd_Concurrent_requests a,
APPLSYS.fnd_concurrent_programs_tl c2,
APPLSYS.fnd_concurrent_programs c,
applsys.fnd_lookup_values l1
where 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.language = 'US'
and c2.application_id = c.application_id
and a.actual_start_date is null
and a.status_code in ('A','H','I','M','P','Q','R')
and a.phase_code in ('P','I')
and sysdate - a.requested_start_date < 2
and a.requested_start_date < sysdate
and l1.lookup_type = 'CP_STATUS_CODE'
and l1.lookup_code = a.status_code
and l1.language = 'US'
and l1.enabled_flag = 'Y'
and (l1.start_date_active <= sysdate and l1.start_date_active is not null)
and (l1.end_date_active > sysdate or l1.end_date_active is null)
order by 1;
/
33.List Scheduled Concurrent Requests
SELECT cr.request_id,
DECODE (cp.user_concurrent_program_name,
'Report Set', 'Report Set:' || cr.description,
cp.user_concurrent_program_name
) NAME,
argument_text, cr.resubmit_interval,
NVL2 (cr.resubmit_interval,
'PERIODICALLY',
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS',
'ONCE')
) schedule_type,
DECODE (NVL2 (cr.resubmit_interval,
'PERIODICALLY',
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS',
'ONCE')
),
'PERIODICALLY', 'EVERY '
|| cr.resubmit_interval
|| ' '
|| cr.resubmit_interval_unit_code
|| ' FROM '
|| cr.resubmit_interval_type_code
|| ' OF PREV RUN',
'ONCE', 'AT :'
|| TO_CHAR (cr.requested_start_date, 'DD-MON-RR
HH24:MI'),
'EVERY: ' || fcr.class_info
) schedule,
fu.user_name, requested_start_date
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_requests cr,
apps.fnd_user fu,
apps.fnd_conc_release_classes fcr
WHERE cp.application_id =
cr.program_application_id
AND cp.concurrent_program_id =
cr.concurrent_program_id
AND cr.requested_by = fu.user_id
AND cr.phase_code = 'P'
AND cr.requested_start_date > SYSDATE
AND cp.LANGUAGE = 'US'
AND fcr.release_class_id(+) = cr.release_class_id
AND fcr.application_id(+) =
cr.release_class_app_id
/
34.concurrent requests that ran for over X minutes
set lines 300
set trims on
set pages 60
set head on
ttit off
btit off
alter session set nls_Date_format='DD-MON-RRRR HH24:MI';
accept minutes prompt "Enter runtime minutes limit to report [DEFAULT=60]: "
col runtime format 9999999 heading "Runtime|[min]"
col asd format a23 Heading "Start Time"
col acd format a23 Heading "End Time"
col cpn format a15 heading "Program"
col ucpn format a25 trunc heading "Program Name"
--col logfile_name format a85 heading "Log File" newline
--col argument_text format a300 heading "Arguments" newline
SELECT f.request_id
, TRUNC(((f.actual_completion_date-f.actual_start_date)/(1/24))*60) runtime
, f.actual_start_date asd
, f.actual_completion_date acd
, p.concurrent_program_name,
, pt.user_concurrent_program_name ucpn
, f.phase_code
, f.status_code
--, f.logfile_name
--, f.argument_text
FROM apps.fnd_concurrent_programs p
, apps.fnd_concurrent_programs_tl pt
, apps.fnd_concurrent_requests f
WHERE TRUNC(((f.actual_completion_date-f.actual_start_date)/(1/24))*60) > NVL('&minutes',60)
and 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')
ORDER by f.actual_completion_date-f.actual_start_date desc
spool a_cm_long_reqs.log
/
35.Long Running Concurrent Requests
SET LINESIZE 132;
--
COLUMN request_id HEADING 'Request ID' FORMAT 99999999;
COLUMN strttime HEADING 'Start|Time' FORMAT A17;
COLUMN endtime HEADING 'End|Time' FORMAT A17;
COLUMN rtime HEADING 'Elapsed|(Min)' FORMAT 9990.99;
SELECT REQUEST_ID,
TO_CHAR(ACTUAL_START_DATE,'MM/DD/YY HH:MI:SS') strttime,
TO_CHAR(ACTUAL_COMPLETION_DATE,'MM/DD/YY HH:MI:SS') endtime,
-- ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE time,
-- TO_NUMBER(TO_CHAR(ACTUAL_COMPLETION_DATE,'JSSSSS') ) - TO_NUMBER(TO_CHAR(ACTUAL_START_DATE,'JSSSSS')) seconds,
ROUND((ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE)*(60*24),2) rtime,
OUTCOME_CODE,
SUBSTR(completion_text,1,20) compl_txt
FROM fnd_concurrent_requests
-- WHERE TO_NUMBER(TO_CHAR(ACTUAL_COMPLETION_DATE,'JSSSSS') ) - TO_NUMBER(TO_CHAR(ACTUAL_START_DATE,'JSSSSS')) > 3600
-- WHERE ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE > 0.01
WHERE ACTUAL_START_DATE > sysdate-10
ORDER BY 2
/
36.SID (Session Id) and SPID (OS Process Id) for a Concurrent Request
set lines 140
set pages 120
select sid,serial#,username,status, to_char(logon_time,'dd-mm-yy hh:mi:ss') "LOGON", program
from gv$session where paddr IN ( select addr from gv$process where spid in (select ORACLE_PROCESS_ID from apps.fnd_concurrent_requests where request_id='&1'));
No comments:
Post a Comment