Concurrent Manager Scripts

 CONCURRENT REQUESTS COMPLETED WITH ERRORS
=============================================

COL name FORMAT a50
COL st_time FORMAT a7
COL requestor FORMAT a18
SET TRIMSPOOL ON
SET FEEDBACK OFF
SET TERM OFF
SET VERIFY OFF
SET PAGES 9000
SET LINES 120
SELECT a.request_id request_id
, SUBSTR(a.user_concurrent_program_name,1,50) name
, TO_CHAR(a.actual_start_date,'Hh24:MI') st_time
, TO_CHAR(a.actual_completion_date,'Hh24:MI') end_time
, requestor
, DECODE(a.phase_code, 'R'
,'Running', 'P'
,'Inactive', 'C'
,'Completed', a.phase_code) phase_code
, DECODE(a.status_code, 'E'
,'Error', 'C'
,'Normal', 'X'
,'Terminated', 'Q'
,'On Hold', 'D'
,'Cancelled', 'G'
,'Warning', 'R'
,'Normal', 'W'
,'Paused', a.status_code) status_code
FROM apps.fnd_conc_req_summary_v a
WHERE TRUNC(actual_completion_date) >= TRUNC(SYSDATE -1)
AND a.status_code IN ('E','X','D')
ORDER BY actual_start_date
/

USAGE REPORT FOR TODAY

=======================

SET PAGES 900

SELECT fcr.cnt "Conc Reqs"

, icxs.self_serv_user_COUNT "SelfServ Users"

, icxs.self_serv_session_COUNT "SelfServ Sessions"

, fl.forms_user_COUNT "Forms Users"

, fl.forms_session_COUNT "Forms Sessions"

FROM (SELECT COUNT(distinct user_id) self_serv_user_COUNT

, COUNT(*) self_serv_session_COUNT

FROM icx.icx_sessions

WHERE TRUNC(creation_date) = TRUNC(SYSDATE-1)) icxs,

(SELECT COUNT(distinct user_id) forms_user_COUNT

, COUNT(*) forms_session_COUNT

FROM applsys.fnd_logins

WHERE TRUNC(start_time) = TRUNC(SYSDATE-1)) fl,

(SELECT COUNT(*) cnt

FROM apps.fnd_concurrent_requests

WHERE TRUNC(actual_start_date) = TRUNC(SYSDATE-1) ) fcr

/

CONCURRENT REQUESTS WHICH HAS MORE THAN 30 MINUTES OF EXECUTION TIME

========================================================================

SELECT a.request_id

, SUBSTR(user_concurrent_program_name,1,50) name

, TO_CHAR(actual_start_date,'DD-MON-YY Hh34:MI') st_dt

, TO_CHAR(actual_completion_date,'Hh34:MI') end_tm

, TRUNC(((actual_completion_date-actual_start_date)*24*60*60)/60)+(((actual_completion_date-actual_start_date)*24*60*60)-(TRUNC(((actual_completion_date-actual_start_date)*24*60*60)/60)*60))/100 exe_time

, requestor

, DECODE(a.status_code, 'E'

,'Error', 'X'

,'Terminated', 'Normal') status_code

FROM apps.fnd_conc_req_summary_v a

WHERE actual_start_date >= DECODE(TO_CHAR(SYSDATE,'DAY'), 'MONDAY'

,TRUNC(SYSDATE)-3, 'SUNDAY'

,TRUNC(SYSDATE)-2, TRUNC(SYSDATE-1))

AND NVL(actual_completion_date,SYSDATE) - actual_start_date >= 30/24/60

ORDER BY actual_start_date, name

/

PROGRAMS RAN MORE 200 TIMES IN A DAY

=====================================

SET LINES 120

SET PAGES 900

COL program FORMAT a70

COL cnt FORMAT 999999 HEADING "Number of Runs"

ttitle 'Programs that ran for more than 200 times ' skip 2

SELECT SUBSTR(user_concurrent_program_name,1,70) program

, COUNT(*) cnt

FROM apps.fnd_conc_req_summary_v

WHERE TRUNC(actual_start_date) = TRUNC(SYSDATE) -1

GROUP BY SUBSTR(user_concurrent_program_name,1,70)

HAVING COUNT(*) > 200

ORDER BY 2

/

NUMBER OF CONCURRENT REQUESTS IN A DAY

=========================================

SET LINES 120

SET PAGES 0

COL cnt FORMAT 999999 HEADING "Total No of Requests"

SELECT ' Number of Concurrent Requests for ', SYSDATE - 1 FROM dual ;

SET PAGES 900

SELECT COUNT(*) cnt

FROM apps.fnd_concurrent_requests

WHERE TRUNC(actual_start_date) = TRUNC(SYSDATE) - 1

/

SCHEDULED REQUESTS

=====================

SET LINE 130 PAGESIZE 1000

COLUMN request_id FORMAT 999999999

COLUMN conc_prog_name FORMAT A47

COLUMN requestor FORMAT A18

COLUMN phase_code FORMAT A10 head 'Phase Code'

COLUMN status_code FORMAT A10 head 'Status Code'

COLUMN req_start_dt FORMAT A20 head 'Requested Start Date'

SELECT fcr.request_id

, SUBSTR(DECODE(fcp.user_concurrent_program_name, 'Report Set'

,fcp.user_concurrent_program_name || ' ' || fcr.description, fcp.user_concurrent_program_name),1,47) conc_prog_name

, fu.user_name requestor

, DECODE(fcr.phase_code, 'R'

,'Running', 'P'

,DECODE(fcr.hold_flag, 'Y'

,'Inactive', 'Pending'), 'C'

,'Completed', fcr.phase_code) phase_code

, DECODE(fcr.status_code, 'E'

,'Error', 'C'

,'Normal', 'X'

,'Terminated', 'Q'

,DECODE(fcr.hold_flag, 'Y'

,'On Hold', DECODE(SIGN(fcr.requested_start_date-SYSDATE),1

,'Scheduled','Standby')), 'D'

,'Cancelled', 'G'

,'Warning', 'R'

,'Normal', 'W'

,'Paused', 'T'

,'Terminating', 'R'

,'Normal', 'W'

,'Paused', 'T'

,'Terminating', 'I'

,'Scheduled', fcr.status_code) status_code

, TO_CHAR(fcr.requested_start_date,'DD-MON-YYYY Hh24:MI:SS') req_start_dt

FROM apps.fnd_user fu

, apps.fnd_concurrent_programs_vl fcp

, apps.fnd_concurrent_requests fcr

WHERE fcp.concurrent_program_id = fcr.concurrent_program_id

AND fcr.status_code IN ('Q', 'I')

AND fcr.phase_code = 'P'

AND fcr.requested_by = fu.user_id

ORDER BY 1

/

TO CHECK THE MANAGERS RUNNING OR NOT SHOULD BE ACTIVE
========================================================

set lines 180

set pages 300

col OSID for a30;

Select distinct Concurrent_Process_Id CpId, PID Opid,

Os_Process_ID Osid,

Q.Concurrent_Queue_Name Manager,

P.NODE_NAME node,

P.process_status_code Status,

To_Char(P.Process_Start_Date, 'MM-DD-YYYY HH:MI:SSAM') Started_At

from Fnd_Concurrent_Processes P, Fnd_Concurrent_Queues Q, FND_V$Process

where Q.Application_Id = Queue_Application_ID

And (Q.Concurrent_Queue_ID = P.Concurrent_Queue_ID)

And ( Spid = Os_Process_ID )

And Process_Status_Code not in ('K','S')

Order by Concurrent_Process_ID, Os_Process_Id, Q.Concurrent_Queue_Name; 

PRESENTLY RUNNING REQUEST

===========================

set lines 180

set pages 500

col USER_CONCURRENT_PROGRAM_NAME for a50

col USER_NAME for a30

SELECT fcr.request_id, ftp.user_concurrent_program_name, fcu.user_name

FROM apps.fnd_concurrent_requests fcr,

apps.fnd_concurrent_programs_tl ftp,

apps.fnd_user fcu

WHERE fcr.status_code = 'R'

AND fcr.phase_code = 'R'

AND fcu.user_id = fcr.requested_by

AND fcr.concurrent_program_id = ftp.concurrent_program_id;

TO CHECK PARTICULAR REQUEST STATUS

====================================

set lines 180

set pages 300

col name format a20

col QUEUE for a20

col U_NAME for a20

select fcr.request_id req_id,

substr(fcq.concurrent_queue_name, 1, 20) queue,

to_char(fcr.actual_start_date,'hh24:mi') s_time,

substr(fcr.user_concurrent_program_name, 1, 60) name,

substr(fcr.requestor, 1, 9 ) u_name,

round((sysdate -actual_start_date) *24, 2) elap,

decode(fcr.phase_code,'R','Running','P','Inactive','C','Completed', fcr.phase_code) Phase,

substr(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', 'TERMINATED',

'U', 'DISABLED', 'W', 'PAUSED', 'X', 'TERMINATED', 'Z', 'WAITING',

'UNKNOWN'), 1, 10)

from

apps.fnd_concurrent_queues fcq,

apps.fnd_concurrent_processes fcp,

apps.fnd_conc_req_summary_v fcr

where fcp.concurrent_queue_id = fcq.concurrent_queue_id

and fcp.queue_application_id = fcq.application_id

and fcr.controlling_manager = fcp.concurrent_process_id

and fcr.request_id = '&RequstID'

order by request_id ;

TO CHECK PARTICULAR PROGRAM REPORT

=====================================

set lines 180

set pages 300

SELECT to_char(a.request_id) ||'~'||

decode(to_char(parent_request_id),'-1',null,to_char(parent_request_id)) ||'~'||

a.user_concurrent_program_name ||'~'||

to_char(a.requested_start_date,'DD-MON-RR HH24:MI:SS') ||'~'||

to_char(a.actual_start_date,'DD-MON-RR HH24:MI:SS') ||'~'||

to_char(a.actual_completion_date,'DD-MON-RR HH24:MI:SS') ||'~'||

round(trunc(((actual_completion_date-actual_start_date)*24*60*60)/60)+(((actual_completion_date-actual_start_date)*24*60*60)-(trunc(((actual_completion_date-actual_start_date)*24*60*60)/60)*60))/100,2) ||'~'||

a.requestor ||'~'||

decode(a.phase_code,'R','Running','P','Inactive','C','Completed', a.phase_code) ||'~'||

decode(a.status_code,'E','Error', 'C','Normal', 'X','Terminated', 'Q','On Hold', 'D','Cancelled', 'G','Warning', 'R','Normal', 'W', 'Paused', a.status_code) ||'~'||

a.argument_text

FROM apps.fnd_conc_req_summary_v a

WHERE a.user_concurrent_program_name like ('&programname%')

order by a.user_concurrent_program_name,a.actual_start_date,a.phase_code;

MV refresh

=========

SELECT (process_end_time-process_start_time)*86400, cycle_start_time

FROM MARS.mars_batch_process_log

WHERE process_name='REFRESH MARS_BALANCES_GL_MV VIEW'

and trunc(cycle_start_time) >= trunc(sysdate-1)

ORDER BY log_seq DESC;

TO FINDOUT PAST ONE MONTH HISTORY

==================================

set pause off

set pagesize 2000

set linesize 120

set wrap off

column user_concurrent_program_name format a45 noprint

column argument_text format a45 print

column user_name format a15

column start_time format a15

column end_time format a15

column comp_time format 9999.99

select request_id,

user_concurrent_program_name,

to_char(actual_start_date,'DD/MON HH24:MI:SS') START_TIME,

to_char(ACTUAL_COMPLETION_DATE,'DD/MON HH24:MI:SS') END_TIME,

(actual_completion_date-actual_start_date)*24*60 comp_time, argument_text,user_name, status_code, phase_code

from apps.fnd_concurrent_requests, apps.fnd_concurrent_programs_tl,apps.fnd_user

where fnd_concurrent_requests.concurrent_program_id = fnd_concurrent_programs_tl.concurrent_program_id

and user_concurrent_program_name like '%Gather Schema%'

and fnd_concurrent_programs_tl.language='US'

and requested_by=user_id

order by actual_start_date desc,ACTUAL_COMPLETION_DATE desc;

TO CHECK WHICH MANAGER IS RUNNING ON WHICH NODE AND MANAGER STATUS

=======================================================================

set verify off

set lines 256

set trims ON

set pages 60

col concurrent_queue_id format 99999 heading "QUEUE Id"

col concurrent_queue_name format a20 trunc heading "QUEUE Code"

col user_concurrent_queue_name format a30 trunc heading "Concurrent Queue Name"

col max_processes format 999 heading "Max"

col running_processes format 999 heading "Act"

col running format 999 heading "Run"

col target_node format a15 heading "Node"

col status format a12 trunc heading "Status"

col run format 9999 heading 'Run'

col pend format 9999 heading 'Pending'

col cmgr_program FOR a65;

SELECT 'Instance : '

||NAME instance_name

FROM v$database;

Prompt ===========================

Prompt concurrent manager status

Prompt ===========================

SELECT q.concurrent_queue_id,

q.concurrent_queue_name,

q.user_concurrent_queue_name,

q.target_node,

q.max_processes,

q.running_processes,

running.run running,

pending.pend,

Decode(q.control_code, 'D', 'Deactivating',

'E', 'Deactivated',

'N', 'Node unavai',

'A', 'Activating',

'X', 'Terminated',

'T', 'Terminating',

'V', 'Verifying',

'O', 'Suspending',

'P', 'Suspended',

'Q', 'Resuming',

'R', 'Restarting') status

FROM (SELECT concurrent_queue_name,

COUNT(phase_code) run

FROM fnd_concurrent_worker_requests

WHERE phase_code = 'R'

AND hold_flag != 'Y'

AND requested_start_date <= SYSDATE GROUP BY concurrent_queue_name) running, (SELECT concurrent_queue_name, COUNT(phase_code) pend FROM fnd_concurrent_worker_requests WHERE phase_code = 'P' AND hold_flag != 'Y' AND requested_start_date <= SYSDATE GROUP BY concurrent_queue_name) pending, apps.fnd_concurrent_queues_vl q WHERE q.concurrent_queue_name = running.concurrent_queue_name(+) AND q.concurrent_queue_name = pending.concurrent_queue_name(+) AND q.enabled_flag = 'Y' ORDER BY Decode(q.application_id, 0, Decode(q.concurrent_queue_id, 1, 1,4, 2)), Sign(q.max_processes) DESC, q.concurrent_queue_name, q.application_id;

TO FINDOUT THE REQUEST LOGFILE NAME AND LOCATION

===================================================

SELECT REQUEST_ID,logfile_name, outfile_name, outfile_node_name, last_update_date FROM apps.FND_CONCURRENT_REQUESTS WHERE REQUEST_ID =&Req_ID; 

TO FINDOUT THE ICM CURRENT LOGFILE NAME AND LOCATION

=======================================================

SELECT 'LOG=' || fcp.logfile_name LogFile

FROM fnd_concurrent_processes fcp, fnd_concurrent_queues fcq

WHERE fcp.concurrent_queue_id = fcq.concurrent_queue_id

AND fcp.queue_application_id = fcq.application_id

AND fcq.manager_type = '0'AND fcp.process_status_code = 'A';

TO FINDOUT WHICH MANAGER RAN THE 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 = '&requistno';

CONCURRENT REQUESTS PERFORMANCE HISTORY (PER DAY)

=====================================================

SELECT TO_CHAR(TRUNC(ACTUAL_START_DATE),'DD-MON-YY DY') STARTDATE,

COUNT(*) COUNT, ROUND(SUM(ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE) * 24, 2) RUNNING_HOURS,

ROUND(AVG(ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE) * 24, 2) AVG_RUNNING_HOURS,

ROUND(SUM(ACTUAL_START_DATE - REQUESTED_START_DATE) * 24, 2) PENDING_HOURS,

ROUND(AVG(ACTUAL_START_DATE - REQUESTED_START_DATE) * 24, 2) AVG_PENDING_HOURS

FROM APPLSYS.FND_CONCURRENT_PROGRAMS P,APPLSYS.FND_CONCURRENT_REQUESTS R

WHERE R.PROGRAM_APPLICATION_ID = P.APPLICATION_ID

AND R.CONCURRENT_PROGRAM_ID = P.CONCURRENT_PROGRAM_ID

AND R.STATUS_CODE IN ('C','G')

AND TRUNC(ACTUAL_COMPLETION_DATE) > TRUNC(SYSDATE-6)

AND TO_CHAR(TRUNC(ACTUAL_START_DATE),'DD-MON-YY DY') IS NOT NULL

GROUP BY TRUNC(ACTUAL_START_DATE) 

ORDER BY TRUNC(ACTUAL_START_DATE) ASC;

TO KNOW THE REQUEST SINCE HOW LONG ITS RUNNING

================================================

select user_concurrent_program_name,request_id,status_code,phase_code,to_char(actual_start_date,'DD-MON-YY HH24:MI:SS'),to_char(actual_completion_date,'DD-MON-YY HH24:MI:SS') from apps.fnd_conc_req_summary_v where request_id='&Requstno';

TO FINDOUT THE TRACEFILE OF A PARTICULAR REQUEST

==================================================

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||'.trcrog.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 apps.fnd_concurrent_requests req, v$session ses, v$process proc,

v$parameter dest, v$parameter dbnm, apps.fnd_concurrent_programs_vl prog,

apps.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_destd dbnm.name='db_named 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;

LIST ALL THE REGISTERED CONCURRENT PROGRAMS BY MODULE

=========================================================

set lines 180

set pages 300

col SHORT_NAME for a10

col APPLICATION_NAME for a30

SELECT SUBSTR(a.application_name,1,60) Application_NAME

, b.application_short_name SHORT_NAME

, DECODE(SUBSTR(cp.user_concurrent_program_name,4,1),':'

, 'Concurrent Manager Executable'

, 'Subprogram or Function') TYPE

, SUBSTR(d.concurrent_program_name,1,16) PROGRAM 

, SUBSTR(cp.user_concurrent_program_name,1,55) USER_PROGRAM_NAME

FROM applsys.FND_CONCURRENT_PROGRAMS_TL cp, applsys.FND_CONCURRENT_PROGRAMS d, applsys.FND_APPLICATION_TL a, applsys.fnd_application b

WHERE cp.application_id = a.application_id

AND d.CONCURRENT_PROGRAM_ID = cp.CONCURRENT_PROGRAM_ID 

AND a.APPLICATION_ID = b.APPLICATION_ID

AND b.application_short_name LIKE UPPER('PA')

UNION ALL

SELECT SUBSTR(a.application_name,1,60) c1

, b.application_short_name c2 , 'Form Executable' c3

, SUBSTR(f.form_name,1,16) c4 , 

SUBSTR(d.user_form_name,1,55) c5

FROM applsys.fnd_form f , applsys.FND_APPLICATION_TL a, applsys.fnd_application b, applsys.FND_FORM_TL d

WHERE f.application_id = a.application_id 

AND d.FORM_ID = f.FORM_ID

AND a.APPLICATION_ID = b.APPLICATION_ID

AND b.application_short_name LIKE UPPER('PA') ORDER BY 1,2,3,4;