Monitor Concurrent Jobs and Hanging Sessions

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

By Sujeet jha at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

ORA-00059: Maximum Number Of DB_FILES Exceeded in 19C database

When I am adding datafile to my 19C database facing the below error. SQL> alter tablespace DATA  add datafile '/u01/data/data15.dbf&#...