Script to 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.
8.To get the Concurrent Manager Name from Concurrent Program:
9.To find the list of Running Concurrent Requests:
10.To find Oracle SID from Request ID:
11.Query to find the Flexfields and valuesets
12.Query to find the Responsibilites :
13.Query to find the Descriptive Field information for PO and GL:
14.Query to find the Menus
15.Query to find the Responsibility,Attached Menu and Application to the particular responsibility
1. List of Concurrent Jobs that completed with error in last one hour.
-------------------------------------------------------------------------------------
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
2. List of Concurrent Jobs running for more then 1 hour.
-----------------------------------------------------------------------------
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
3. List of concurrent Jobs completed with Warning in last one hour
--------------------------------------------------------------------------------------------------
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'
4. List of Jobs that are Pending Normal for more than 10 Minutes.
---------------------------------------------------------------------------------
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
5. List of Hung sessions or Orphan sessions.
-----------------------------------------------------------------------
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
6. List of Concurrent managers with Pending Normal jobs.
-----------------------------------------------------------------------
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
7. Critical Jobs completed in last one hour with completion time:
---------------------------------------------------------------------------------------------------------------------------------
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
8.To get the Concurrent Manager Name from Concurrent Program:
------------------------------------------------------------------------------------------------------------------------------------
SELECT user_concurrent_program_name, user_concurrent_queue_name
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_queue_content cqc,
apps.fnd_concurrent_queues_tl cq
WHERE cqc.type_application_id(+) = cp.application_id
AND cqc.type_id(+) = cp.concurrent_program_id
AND cqc.type_code(+) = 'P'
AND cqc.include_flag(+) = 'I'
AND cp.LANGUAGE = 'US'
AND cp.user_concurrent_program_name = '&USER_CONCURRENT_PROGRAM_NAME'
AND NVL (cqc.concurrent_queue_id, 0) = cq.concurrent_queue_id
AND NVL (cqc.queue_application_id, 0) = cq.application_id
AND cq.LANGUAGE = 'US';
9.To find the list of Running Concurrent Requests:
--------------------------------------------------------------------------------------------------------------------------------------
select fcr.request_id,
substr(fcp.USER_CONCURRENT_PROGRAM_NAME,1,35) conc_prog_name,
fu.user_name requestor
, sid
, vs.process
, vp.spid
-- , fcr.oracle_process_id
from
v$session vs,
v$process vp,
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.program_application_id = fcp.application_id
and fcr.status_code = 'R'
and fcr.phase_code = 'R'
and fcr.requested_by = fu.user_id
and fcr.oracle_process_id = vp.spid(+)
and vp.addr = vs.paddr (+)
;
10.To find Oracle SID from Request ID:
--------------------------------------------------------
select r.request_id,
r.oracle_process_id,
r.oracle_session_id,
r.os_process_id,
s.sid,
s.serial#,
s.paddr
from fnd_concurrent_requests r,
v$session s
where request_id = &reqid
and r.oracle_session_id = s.audsid(+);
Req. Analysis by Request ID
Run analreq.sql from apps user
11.Query to find the Flexfields and valuesets
----------------------------------------------------------------
SELECT A.ID_FLEX_STRUCTURE_CODE,
B.ID_FLEX_CODE,
E.LANGUAGE,
D.FLEX_VALUE,
E.DESCRIPTION,
E.FLEX_VALUE_MEANING,
B.FLEX_VALUE_SET_ID,
B.APPLICATION_COLUMN_NAME,
B.SEGMENT_NAME,
C.FLEX_VALUE_SET_NAME,
D.FLEX_VALUE_ID
FROM APPS.fnd_id_flex_structures A,
APPS.fnd_id_flex_segments B,
APPS.fnd_flex_value_sets C,
APPS.fnd_flex_values D,
APPS.fnd_flex_values_tl E
WHERE A.ID_FLEX_NUM=B.ID_FLEX_NUM
--AND A.ID_FLEX_CODE='GL#'
--AND B.ID_FLEX_CODE='GL#'
AND B.FLEX_VALUE_SET_ID=C.FLEX_VALUE_SET_ID
AND C.FLEX_VALUE_SET_ID=D.FLEX_VALUE_SET_ID
AND D.FLEX_VALUE_ID=E.FLEX_VALUE_ID
AND A.APPLICATION_ID IN (101,201)
AND E.LANGUAGE='US'
ORDER BY A.ID_FLEX_STRUCTURE_CODE
12.Query to find the Responsibilites :
------------------------------------------------
SELECT B.RESPONSIBILITY_ID,
A.RESPONSIBILITY_NAME,
A.LANGUAGE,
B.RESPONSIBILITY_KEY ,
B.APPLICATION_ID
FROM APPS.FND_RESPONSIBILITY_TL A,
APPS.FND_RESPONSIBILITY B
WHERE A.RESPONSIBILITY_ID(+)=B.RESPONSIBILITY_ID
AND A.LANGUAGE = USERENV('LANG')
ORDER BY A.RESPONSIBILITY_NAME
13.Query to find the Descriptive Field information for PO and GL:
------------------------------------------------------------------------------------
select flex_vl.title,flex.descriptive_flexfield_name,
context_vl.descriptive_flex_context_code,context_vl.description,
col_usage_vl.application_column_name, col_usage_vl.end_user_column_name
from apps.FND_DESCRIPTIVE_FLEXS flex,
apps.FND_DESCRIPTIVE_FLEXS_vl flex_vl
,apps.FND_DESCR_FLEX_CONTEXTS_VL context_vl
,apps.FND_DESCR_FLEX_COL_USAGE_VL col_usage_vl
where flex_vl.application_id in (201,101)
and flex_vl.title = 'Approved Supplier List'
and flex.descriptive_flexfield_name = flex_vl.descriptive_flexfield_name
--'PO_APPROVED_SUPPLIER_LIST'
and flex.descriptive_flexfield_name = context_vl.descriptive_flexfield_name
and flex.descriptive_flexfield_name = col_usage_vl.descriptive_flexfield_name
and col_usage_vl.descriptive_flex_context_code = context_vl.descriptive_flex_context_code
--'2346'
order by col_usage_vl.descriptive_flex_context_code,col_usage_vl.application_column_name
14.Query to find the Menus
--------------------------------
SELECT
B.ROWID ROW_ID,
B.MENU_ID,
B.MENU_NAME,
B.TYPE,
B.LAST_UPDATE_DATE,
B.LAST_UPDATED_BY ,
B.LAST_UPDATE_LOGIN ,
B.CREATION_DATE ,
B.CREATED_BY ,
T.USER_MENU_NAME ,
T.DESCRIPTION,
T.LANGUAGE,
C.SUB_MENU_ID
FROM
apps.FND_MENUS_TL T,
apps.FND_MENUS B,
apps.FND_MENU_ENTRIES C
-- (SELECT USER_MENU_NAME,MENU_ID FROM FND_MENUS_TL)SUB
WHERE
B.MENU_ID = T.MENU_ID
AND T.LANGUAGE = USERENV('LANG')
--AND T.USER_MENU_NAME = 'Activity Based Management'
AND B.MENU_ID=C.MENU_ID
15.Query to find the Responsibility,Attached Menu and Application to the particular responsibility
-------------------------------------------------------------------------------------------------------------------
SELECT distinct
B.RESPONSIBILITY_ID,
A.RESPONSIBILITY_NAME,
A.LANGUAGE,
B.RESPONSIBILITY_KEY ,
B.APPLICATION_ID,
C.USER_MENU_NAME,
E.APPLICATION_NAME
FROM APPS.FND_RESPONSIBILITY_TL A,
APPS.FND_RESPONSIBILITY B,
APPS.FND_MENUS_TL C,
APPS.FND_MENUS D,
apps.FND_APPLICATION_TL E,
apps.FND_APPLICATION F
WHERE A.RESPONSIBILITY_ID(+)=B.RESPONSIBILITY_ID
AND B.MENU_ID=C.MENU_ID
AND B.MENU_ID=D.MENU_ID
AND E.APPLICATION_ID=F.APPLICATION_ID
AND F.APPLICATION_ID=B.APPLICATION_ID
AND A.LANGUAGE='US'
MADHUAPPSDBA
-------------------------------------------------------------
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.
8.To get the Concurrent Manager Name from Concurrent Program:
9.To find the list of Running Concurrent Requests:
10.To find Oracle SID from Request ID:
11.Query to find the Flexfields and valuesets
12.Query to find the Responsibilites :
13.Query to find the Descriptive Field information for PO and GL:
14.Query to find the Menus
15.Query to find the Responsibility,Attached Menu and Application to the particular responsibility
1. List of Concurrent Jobs that completed with error in last one hour.
-------------------------------------------------------------------------------------
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
2. List of Concurrent Jobs running for more then 1 hour.
-----------------------------------------------------------------------------
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
3. List of concurrent Jobs completed with Warning in last one hour
--------------------------------------------------------------------------------------------------
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'
4. List of Jobs that are Pending Normal for more than 10 Minutes.
---------------------------------------------------------------------------------
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
5. List of Hung sessions or Orphan sessions.
-----------------------------------------------------------------------
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
6. List of Concurrent managers with Pending Normal jobs.
-----------------------------------------------------------------------
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
7. Critical Jobs completed in last one hour with completion time:
---------------------------------------------------------------------------------------------------------------------------------
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
8.To get the Concurrent Manager Name from Concurrent Program:
------------------------------------------------------------------------------------------------------------------------------------
SELECT user_concurrent_program_name, user_concurrent_queue_name
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_queue_content cqc,
apps.fnd_concurrent_queues_tl cq
WHERE cqc.type_application_id(+) = cp.application_id
AND cqc.type_id(+) = cp.concurrent_program_id
AND cqc.type_code(+) = 'P'
AND cqc.include_flag(+) = 'I'
AND cp.LANGUAGE = 'US'
AND cp.user_concurrent_program_name = '&USER_CONCURRENT_PROGRAM_NAME'
AND NVL (cqc.concurrent_queue_id, 0) = cq.concurrent_queue_id
AND NVL (cqc.queue_application_id, 0) = cq.application_id
AND cq.LANGUAGE = 'US';
9.To find the list of Running Concurrent Requests:
--------------------------------------------------------------------------------------------------------------------------------------
select fcr.request_id,
substr(fcp.USER_CONCURRENT_PROGRAM_NAME,1,35) conc_prog_name,
fu.user_name requestor
, sid
, vs.process
, vp.spid
-- , fcr.oracle_process_id
from
v$session vs,
v$process vp,
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.program_application_id = fcp.application_id
and fcr.status_code = 'R'
and fcr.phase_code = 'R'
and fcr.requested_by = fu.user_id
and fcr.oracle_process_id = vp.spid(+)
and vp.addr = vs.paddr (+)
;
10.To find Oracle SID from Request ID:
--------------------------------------------------------
select r.request_id,
r.oracle_process_id,
r.oracle_session_id,
r.os_process_id,
s.sid,
s.serial#,
s.paddr
from fnd_concurrent_requests r,
v$session s
where request_id = &reqid
and r.oracle_session_id = s.audsid(+);
Req. Analysis by Request ID
Run analreq.sql from apps user
11.Query to find the Flexfields and valuesets
----------------------------------------------------------------
SELECT A.ID_FLEX_STRUCTURE_CODE,
B.ID_FLEX_CODE,
E.LANGUAGE,
D.FLEX_VALUE,
E.DESCRIPTION,
E.FLEX_VALUE_MEANING,
B.FLEX_VALUE_SET_ID,
B.APPLICATION_COLUMN_NAME,
B.SEGMENT_NAME,
C.FLEX_VALUE_SET_NAME,
D.FLEX_VALUE_ID
FROM APPS.fnd_id_flex_structures A,
APPS.fnd_id_flex_segments B,
APPS.fnd_flex_value_sets C,
APPS.fnd_flex_values D,
APPS.fnd_flex_values_tl E
WHERE A.ID_FLEX_NUM=B.ID_FLEX_NUM
--AND A.ID_FLEX_CODE='GL#'
--AND B.ID_FLEX_CODE='GL#'
AND B.FLEX_VALUE_SET_ID=C.FLEX_VALUE_SET_ID
AND C.FLEX_VALUE_SET_ID=D.FLEX_VALUE_SET_ID
AND D.FLEX_VALUE_ID=E.FLEX_VALUE_ID
AND A.APPLICATION_ID IN (101,201)
AND E.LANGUAGE='US'
ORDER BY A.ID_FLEX_STRUCTURE_CODE
12.Query to find the Responsibilites :
------------------------------------------------
SELECT B.RESPONSIBILITY_ID,
A.RESPONSIBILITY_NAME,
A.LANGUAGE,
B.RESPONSIBILITY_KEY ,
B.APPLICATION_ID
FROM APPS.FND_RESPONSIBILITY_TL A,
APPS.FND_RESPONSIBILITY B
WHERE A.RESPONSIBILITY_ID(+)=B.RESPONSIBILITY_ID
AND A.LANGUAGE = USERENV('LANG')
ORDER BY A.RESPONSIBILITY_NAME
13.Query to find the Descriptive Field information for PO and GL:
------------------------------------------------------------------------------------
select flex_vl.title,flex.descriptive_flexfield_name,
context_vl.descriptive_flex_context_code,context_vl.description,
col_usage_vl.application_column_name, col_usage_vl.end_user_column_name
from apps.FND_DESCRIPTIVE_FLEXS flex,
apps.FND_DESCRIPTIVE_FLEXS_vl flex_vl
,apps.FND_DESCR_FLEX_CONTEXTS_VL context_vl
,apps.FND_DESCR_FLEX_COL_USAGE_VL col_usage_vl
where flex_vl.application_id in (201,101)
and flex_vl.title = 'Approved Supplier List'
and flex.descriptive_flexfield_name = flex_vl.descriptive_flexfield_name
--'PO_APPROVED_SUPPLIER_LIST'
and flex.descriptive_flexfield_name = context_vl.descriptive_flexfield_name
and flex.descriptive_flexfield_name = col_usage_vl.descriptive_flexfield_name
and col_usage_vl.descriptive_flex_context_code = context_vl.descriptive_flex_context_code
--'2346'
order by col_usage_vl.descriptive_flex_context_code,col_usage_vl.application_column_name
14.Query to find the Menus
--------------------------------
SELECT
B.ROWID ROW_ID,
B.MENU_ID,
B.MENU_NAME,
B.TYPE,
B.LAST_UPDATE_DATE,
B.LAST_UPDATED_BY ,
B.LAST_UPDATE_LOGIN ,
B.CREATION_DATE ,
B.CREATED_BY ,
T.USER_MENU_NAME ,
T.DESCRIPTION,
T.LANGUAGE,
C.SUB_MENU_ID
FROM
apps.FND_MENUS_TL T,
apps.FND_MENUS B,
apps.FND_MENU_ENTRIES C
-- (SELECT USER_MENU_NAME,MENU_ID FROM FND_MENUS_TL)SUB
WHERE
B.MENU_ID = T.MENU_ID
AND T.LANGUAGE = USERENV('LANG')
--AND T.USER_MENU_NAME = 'Activity Based Management'
AND B.MENU_ID=C.MENU_ID
15.Query to find the Responsibility,Attached Menu and Application to the particular responsibility
-------------------------------------------------------------------------------------------------------------------
SELECT distinct
B.RESPONSIBILITY_ID,
A.RESPONSIBILITY_NAME,
A.LANGUAGE,
B.RESPONSIBILITY_KEY ,
B.APPLICATION_ID,
C.USER_MENU_NAME,
E.APPLICATION_NAME
FROM APPS.FND_RESPONSIBILITY_TL A,
APPS.FND_RESPONSIBILITY B,
APPS.FND_MENUS_TL C,
APPS.FND_MENUS D,
apps.FND_APPLICATION_TL E,
apps.FND_APPLICATION F
WHERE A.RESPONSIBILITY_ID(+)=B.RESPONSIBILITY_ID
AND B.MENU_ID=C.MENU_ID
AND B.MENU_ID=D.MENU_ID
AND E.APPLICATION_ID=F.APPLICATION_ID
AND F.APPLICATION_ID=B.APPLICATION_ID
AND A.LANGUAGE='US'
MADHUAPPSDBA
No comments:
Post a Comment