Thursday, June 19, 2008

Script to Monitor Concurrent Jobs and Hanging Sessions

Print this post

 

Here is a monitoring system to monitor all concurrent jobs, concurrent managers and hung sessions every

hour proactively and take appropriate action immediately. It gives the following reports

1. List of Concurrent Jobs that completed with error in last one hour.
2. List of Concurrent Jobs running for more then 1 hour.
3. List of concurrent Jobs completed with Warning in last one hour
4. List of Jobs that are Pending Normal for more than 10 Minutes.
5. List of Hung sessions or Orphan sessions.
6. List of Concurrent managers with Pending Normal jobs.
7. Critical Jobs completed in last one hour with completion time.

 

SELECT A FROM

(

select 'CONCURRENT PROGRAMS COMPLETED WITH ERROR STATUS BETWEEN '||to_char(sysdate - (1/24),

'dd-mon-yyyy hh24:mi:ss') || ' AND '|| to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') A, 'A' B,1 SRT from dual

UNION

select RPAD('-',125,'-') A, 'A' B,1.1 SRT from dual

UNION

SELECT to_char( rpad('REQUEST_ID',10) ||' '||rpad('ACTUAL START DATE',20)|| ' ' ||

rpad('CONCURRENT PROGRAM NAME',65)||' '||rpad('REQUESTOR',10)||' '||'P REQ ID'), 'A' B,1.2 FROM DUAL

UNION

select to_char( rpad(to_char(Request_ID),10) ||' '|| RPAD(NVL(to_char(actual_start_date,

'dd-mon-yyyy hh24:mi:ss'),' '),20) || ' ' || rpad(substr(Program,1,65),65)||' '||rpad(substr(requestor,1,10),

10)||' '||to_char(Parent_Request_ID) ) A, 'A' B, 1.4 SRT from fnd_conc_req_summary_v conc

where actual_completion_date > sysdate - (1/24) and phase_code = 'C' and status_code = 'E'

UNION

select RPAD('-',125,'-') A, 'A' B,1.6 SRT from dual

UNION

SELECT ' ', 'A', 1.8 FROM DUAL

UNION

SELECT ' ', 'A', 1.86 FROM DUAL

-----------------------------------------------------------

UNION

select 'CONCURRENT PROGRAMS COMPLETED WITH WARNING STATUS BETWEEN '||to_char(sysdate - (1/24),

'dd-mon-yyyy hh24:mi:ss') || ' AND '|| to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') A, 'D' B,1 SRT from dual

UNION

select RPAD('-',125,'-') A, 'D' B, 1.1 SRT from dual

UNION

SELECT to_char( rpad('REQUEST_ID',10) ||' '||rpad('ACTUAL START DATE',20)|| ' ' ||

rpad('CONCURRENT PROGRAM NAME',65)||' '||rpad('REQUESTOR',10)||' '||'P REQ ID'), 'D' B, 1.2 FROM DUAL

UNION

select to_char( rpad(to_char(Request_ID),10) ||' '|| RPAD(NVL(to_char(actual_start_date,

'dd-mon-yyyy hh24:mi:ss'),' '),20) || ' ' || rpad(substr(Program,1,65),65)||' '||rpad(substr(requestor,1,10),10)

||' '||to_char(Parent_Request_ID) ) A, 'D' B, 1.4 SRT from fnd_conc_req_summary_v conc where

actual_completion_date > sysdate - (1/24) and phase_code = 'C' and status_code = 'G'

and concurrent_program_id not in (47654,31881,47737)

UNION

select RPAD('-',125,'-') A, 'D' B, 1.8 SRT from dual

-----------------------------------------------------------

UNION

SELECT ' ', 'D', 1.86 FROM DUAL

UNION

SELECT ' ', 'D', 1.88 FROM DUAL

UNION

select 'CONCURRENT PROGRAMS THAT ARE PENDING NORMAL FOR THE PAST 10 MINUTES ' A, 'E' B,1 SRT

from dual

UNION

select RPAD('-',125,'-') A, 'E' B, 1.1 SRT from dual

UNION

SELECT to_char( rpad('REQUEST_ID',10) ||' '||rpad('ACTUAL START DATE',20)|| ' ' ||

rpad('CONCURRENT PROGRAM NAME',65)||' '||rpad('REQUESTOR',10)||' '||'P REQ ID'), 'E' B, 1.2 FROM DUAL

UNION

select to_char( rpad(to_char(Request_ID),10) ||' '|| RPAD(NVL(to_char(actual_start_date,

'dd-mon-yyyy hh24:mi:ss'),' '),20) || ' ' || rpad(substr(Program,1,65),65)||' '||rpad(substr(requestor,1,10),10)

||' '||to_char(Parent_Request_ID) ) A, 'E' B, 2 SRT FROM FND_CONC_REQ_SUMMARY_V CONC

WHERE SYSDATE - REQUEST_DATE > 0.00694444444444444 AND REQUESTED_START_DATE < SYSDATE

AND PHASE_CODE = 'P' AND STATUS_CODE = 'Q'

UNION

select RPAD('-',125,'-') A, 'E' B, 3 SRT from dual

UNION

SELECT chr(10)||chr(10) A, 'E' B, 4.4 SRT FROM DUAL

UNION

select 'CONCURRENT PROGRAMS THAT STARTED BEFORE '||to_char(sysdate - (1/24),'dd-mon-yyyy hh24:mi:ss')

||' AND ARE STILL RUNNING ' A, 'B' B,4.6 SRT FROM DUAL

UNION

SELECT RPAD('-',125,'-') A, 'B' B, 4.8 SRT FROM DUAL

UNION

SELECT to_char( rpad('REQUEST_ID',10) ||' '||rpad('ACTUAL START DATE',20)|| ' ' ||

rpad('CONCURRENT PROGRAM NAME',65)||' '||rpad('REQUESTOR',10)||' '||'P REQ ID'), 'B' B, 4.84 SRT FROM DUAL

UNION

SELECT to_char( rpad(to_char(Request_ID),10) ||' '|| RPAD(NVL(to_char(actual_start_date,

'dd-mon-yyyy hh24:mi:ss'),'-'),20) || ' ' || rpad(substr(Program,1,65),65)||' '||rpad(substr(requestor,1,10),

10)||' '||to_char(Parent_Request_ID) ) A, 'B' B, 4.86 SRT FROM FND_CONC_REQ_SUMMARY_V CONC

WHERE SYSDATE - ACTUAL_START_DATE > 0.0416666666666667 AND PHASE_CODE = 'R' AND STATUS_CODE = 'R'

-----------------------------------------------------------------------

UNION

SELECT RPAD('-',125,'-') A, 'C' B, 1.1 SRT FROM DUAL

UNION

SELECT ' ', 'C', 1.2 FROM DUAL

UNION

SELECT ' ', 'C', 5.8 FROM DUAL

UNION

select ' FOLLOWING ARE THE DETAILS OF HUNG OR ORPHAN SESSIONS AS OF '||to_char(sysdate ,

'dd-mon-yyyy hh24:mi:ss') A, 'C' B,1.5 SRT from dual

UNION

select RPAD('-',125,'-') A, 'C' B, 1.6 SRT from dual

UNION

SELECT to_char(rpad(to_char('SID'),5) ||' '||rpad('PROCESS',12)|| ' ' ||rpad('MODULE',10)||' '||rpad('ACTION',

25)||' '||rpad('USERNAME',15)||' '||rpad('PROGRAM',20)||' '||rpad('EVENT',25)) A, 'C' B, 5.2 FROM DUAL

UNION

select to_char(rpad(nvl(to_char(a.sid), ' '),7,' ')||' '||rpad(nvl(a.process, ' '),19,' ')||' '||rpad(nvl(a.module,

' '),10)||' '||rpad(nvl(a.action, ' '),20)||' '||rpad(nvl(a.username, ' '),15)||' '||rpad(nvl(a.program, ' '),20)||' '||

rpad(c.event,25)) A,'C' B, 5.4 SRT from gv$session a, gv$process b, gv$session_Wait c where c.event not

like 'SQL%' and c.event not in ('pmon timer','rdbms ipc message','pipe get','queue messages','smon timer',

'wakeup time manager','PL/SQL lock timer','jobq slave wait','ges remote message','async disk IO','gcs remote

message','PX Deq: reap credit','PX Deq: Execute Reply') and a.paddr=b.addr and a.sid=c.sid and a.inst_id=

c.inst_id and a.inst_id=b.inst_id and a.last_call_et >1800

UNION

select RPAD('-',125,'-') A, 'C' B, 5.6 SRT from dual

UNION

-----------------------------------------------------------------------

SELECT ' ', 'F', 1.01 FROM DUAL

UNION

select 'PENDING NORMAL MANAGERS IN LAST ONE HOUR '|| ' '|| to_char(sysdate - (1/24),

'dd-mon-yyyy hh24:mi:ss') || ' AND '|| to_char(sysdate,'dd-mon-yyyy hh24:mi:ss' ) A, 'F' B,1 SRT from dual

UNION

select RPAD('-',125,'-') A, 'F' B, 1.1 SRT from dual

UNION

SELECT to_char( rpad('CONCURRENT MANAGER NAME',35) || rpad('ACTUAL',25)|| ' ' ||rpad('TARGET',

20)||' '||rpad('RUNNING',25)||' '||'PENDING'), 'F' B, 1.2 FROM DUAL

UNION

select to_char (

decode (

fcq.USER_CONCURRENT_QUEUE_NAME,

'XXXXXX: High Workload',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,53),

'XXXXXX: Standard Manager',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,50),

'XXXXXX: MRP Manager',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,50),

'XXXXXX: Payroll Manager',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,51),

'XXXXXX: Fast Jobs',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,56),

'XXXXXX: Workflow',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,56),

'XXXXXX: Critical Jobs',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,56),

'Inventory Manager',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,56),

'Conflict Resolution Manager',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,51),

null)

||' '||rpad(TO_CHAR(NVL(FCQ.RUNNING_PROCESSES,0)),30)||' '||

rpad(to_char(nvl(FCQ.MAX_PROCESSES,0)),30) ||' '||rpad(to_char(NVL(running,0)),30) || ' '||

to_char(NVL(PENDING,0))) A, 'F' B, 1.3 SRT

from

apps.fnd_concurrent_queues_vl FCQ,

(SELECT nvl(count(*),0) Running, fcwr.concurrent_queue_id

FROM fnd_concurrent_worker_requests fcwr

WHERE fcwr.concurrent_queue_id IN (1755,1756,1757,1758,1759,1760,1754,10,4)

AND (fcwr.phase_code = 'R')

AND fcwr.hold_flag != 'Y'

AND SYSDATE - fcwr.requested_start_date >= 0.00694444444444444

group by fcwr.concurrent_queue_id ) RUNNING ,

( SELECT nvl(count(*),0) Pending, fcwp.concurrent_queue_id

FROM fnd_concurrent_worker_requests fcwp

WHERE fcwp.concurrent_queue_id IN (1755,1756,1757,1758,1759,1760,1754,10,4)

AND (fcwp.phase_code = 'P')

AND fcwp.hold_flag != 'Y'

AND sysdate-fcwp.requested_start_date >= 0.00694444444444444

group by fcwp.concurrent_queue_id ) PENDING

WHERE FCQ.concurrent_queue_id=RUNNING.concurrent_queue_id(+)

AND FCQ.concurrent_queue_id=PENDING.concurrent_queue_id(+)

AND fcQ.concurrent_queue_id IN (1755,1756,1757,1758,1759,1760,1754,10,4)

UNION

select RPAD('-',125,'-') A, 'F' B, 1.4 SRT from dual

UNION

SELECT chr(10)||chr(10) A, 'F' B, 1.5 SRT FROM DUAL

UNION

-----------------------------------------------------------------------

SELECT ' ', 'G', 1.01 FROM DUAL

UNION

select 'CRITICAL PROGRAMS STATUS IN LAST ONE HOUR '|| ' '|| to_char(sysdate - (1/24),

'dd-mon-yyyy hh24:mi:ss') || ' AND '|| to_char(sysdate,'dd-mon-yyyy hh24:mi:ss' ) A , 'G' B,1 SRT from dual

UNION

select RPAD('-',125,'-') A, 'G' B, 1.1 SRT from dual

UNION

SELECT to_char( rpad('CONCURRENT PROGRAM NAME',55) || rpad('AVG TIME',20)|| ' ' ||rpad

('CURR MAX TIME',16)||' '|| 'REQUEST_ID'), 'G' B, 1.1 FROM DUAL

UNION

SELECT to_char (

decode (PROGRAM_NAME,

'AutoCreate Configuration Items',RPAD(PROGRAM_NAME,70),

'Memory-based Snapshot',RPAD(PROGRAM_NAME,71),

'Order Import',RPAD(PROGRAM_NAME,80),

'Workflow Background Process',RPAD(PROGRAM_NAME,69),

PROGRAM_NAME) ||' '||

rpad(TO_CHAR(STATIC.AVG_TIME),25) || ' '||

rpad(TO_CHAR(DYNAMIC.CURR_MAX_TIME),25) || ' '||

to_char(NVL(REQUEST_ID,NULL))) A, 'G' B, 1.2 SRT

FROM

(SELECT

CONCURRENT_PROGRAM_ID,

USER_CONCURRENT_PROGRAM_NAME,

REQUEST_ID,

ROUND((ACTUAL_COMPLETION_DATE-ACTUAL_START_DATE)*24*60,0) CURR_MAX_TIME

FROM APPS.FND_CONC_REQ_SUMMARY_V fcr

WHERE CONCURRENT_PROGRAM_ID IN (36888,

48681,39442,33137,47730,47731,47712,47729,31881)

and phase_code='C'

AND STATUS_CODE='C'

AND ACTUAL_COMPLETION_DATE>=(sysdate - (1/24))

AND REQUEST_ID IN (

SELECT MAX(REQUEST_ID) FROM APPS.FND_CONC_REQ_SUMMARY_V fcr WHERE CONCURRENT_PROGRAM_ID

IN (36888,48681,39442,33137,47730,47731,47712,47729,31881)

and phase_code='C'

AND STATUS_CODE='C'

AND ACTUAL_COMPLETION_DATE>=(sysdate - (1/24))

GROUP BY CONCURRENT_PROGRAM_ID) ) DYNAMIC ,

(select distinct CONCURRENT_PROGRAM_ID "CONCURRENT_PROGRAM_ID",

USER_CONCURRENT_PROGRAM_NAME "PROGRAM_NAME",

DECODE ( CONCURRENT_PROGRAM_ID,36888,39442,33137,31881,10,NULL) AVG_TIME

FROM APPS.FND_CONCURRENT_PROGRAMS_TL fcr

WHERE CONCURRENT_PROGRAM_ID IN

(36888,39442,33137,31881)

AND LANGUAGE='US' ) STATIC

WHERE DYNAMIC.CONCURRENT_PROGRAM_ID(+)=STATIC.CONCURRENT_PROGRAM_ID

UNION

select RPAD('-',125,'-') A, 'G' B, 1.4 SRT from dual

UNION

SELECT chr(10)||chr(10) A, 'G' B, 1.5 SRT FROM DUAL

-----------------------------------------------------------------------

) TEMP

ORDER BY B, SRT, A

No comments: