LOG_FILE=$HOME/scripts/log/Monitor_Running_jobs.log
. $HOME/.profile > /dev/null
EMAIL_LIST=seetharam.nanubalu@psc.RAM.gov
sqlplus -s / as sysdba >/dev/null <<EOF
SET LINESIZE 160 PAGESIZE 200 FEEDBACK ON
SET ECHO OFF MARKUP HTML ON ENTMAP OFF SPOOL ON HEAD '' TABLE 'align=centre border=1 color:Black background:#f7f7e7'
SPOOL $LOG_FILE
PROMPT <h1>YE and Standard Concurrent Manager Status</h1>
select q.concurrent_queue_name || ' - ' || target_node qname,
q.running_processes actual,
q.max_processes target,
sum(decode(r.phase_code,'R',1,0)) - sum(decode(r.status_code,'W',1,0)) running,
sum(decode(r.phase_code,'P',1,0)) pending,
nvl(sum(decode(r.status_code,'W',1,'H',1,'S',1,'A',1,'Z',1,0)),0) paused,
nvl(sum(decode(r.status_code,'F',1,'Q',1,'B',1,'T',0)),0) influx,
avg((nvl(r.actual_start_date,r.requested_start_date) - r.requested_start_date)*1440) avgqtime
from applsys.fnd_concurrent_requests r,
applsys.fnd_concurrent_processes p,
applsys.fnd_concurrent_queues q
where r.controlling_manager (+) = p.concurrent_process_id
and p.queue_application_id = q.application_id
and p.concurrent_queue_id = q.concurrent_queue_id
and q.max_processes > 0
and ((r.phase_code in ('R','P','I') and upper('N') = 'Y') or
upper('N') != 'Y')
and q.concurrent_queue_name in( 'STANDARD','STANDARD2', 'YETSMANAGER')
group by q.concurrent_queue_name || ' - ' || target_node, q.running_processes, q.max_processes;
PROMPT <h1> Stale Stats of FV and GL </h1>
select distinct OWNER,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,NUM_ROWS,LAST_ANALYZED,stale_stats from dba_tab_statistics where stale_stats='YES'
and owner in ('FV','GL') order by owner;
select distinct owner,INDEX_NAME,TABLE_NAME from DBA_IND_STATISTICS where stale_stats='YES' and owner in ('FV','GL') order by owner;
PROMPT <h1> Locks </h1>
SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type
FROM gv\$LOCK
WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM gv\$LOCK WHERE request>0) ORDER BY id1, request;
PROMPT <h1> Blocking Session Details </h1>
select
(select username from gv\$session where sid=a.sid) blocker,
a.sid,
' is blocking ',
(select username from gv\$session where sid=b.sid) blockee,
b.sid
from
gv\$lock a,
gv\$lock b
where
a.block = 1
and
b.request > 0
and
a.id1 = b.id1
and
a.id2 = b.id2;
PROMPT <h1> Tables with locked stats GL and FV </h1>
SELECT owner,
table_name,
stattype_locked
FROM dba_tab_statistics
WHERE stattype_locked IS NOT NULL and owner in ('GL','FV')
ORDER BY owner, table_name;
PROMPT <h1> TOP 10 SQL statements that are using the most resources</h1>
SELECT *
FROM (SELECT a.inst_id,a.sql_id,a.plan_hash_value,Substr(a.sql_text,1,50) sql_text,
Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_execution,
a.buffer_gets,
a.disk_reads,
a.executions,
a.sorts,
a.address
FROM gv\$sqlarea a
ORDER BY 2 DESC)
WHERE rownum <= 10;
PROMPT <h1> Library Cache locks and pin </h1>
select /*+ all_rows */ w1.sid waiting_session,
h1.sid holding_session,
w.kgllktype lock_or_pin,
w.kgllkhdl address,
decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_held,
decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_requested
from dba_kgllock w, dba_kgllock h, gv\$session w1, gv\$session h1
where
(((h.kgllkmod != 0) and (h.kgllkmod != 1)
and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
and
(((w.kgllkmod = 0) or (w.kgllkmod= 1))
and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
and w.kgllktype = h.kgllktype
and w.kgllkhdl = h.kgllkhdl
and w.kgllkuse = w1.saddr
and h.kgllkuse = h1.saddr;
PROMPT <h1> OCI Objects Invalid Status </h1>
select count(*) from dba_objects where object_name like 'OCI_PO_IMPORT%' and status='INVALID';
PROMPT <h1> Mandatory Workflow Programs </h1>
select distinct(ARGUMENT_TEXT),USER_CONCURRENT_PROGRAM_NAME
,REQUEST_ID,REQUESTED_START_DATE, fcr.PHASE_CODE
from apps.fnd_concurrent_requests fcr,
apps.fnd_concurrent_programs_vl fpl,
apps.fnd_application_vl fal
where fcr.REQUESTED_START_DATE > trunc(sysdate)
--AND fcr.REQUESTED_START_DATE < sysdate
AND fcr.concurrent_program_id = fpl.concurrent_program_id
AND fcr.program_application_id = fpl.application_id
and fcr.program_application_id = fal.application_id
and user_concurrent_program_name = 'Workflow Background Process'
--and ARGUMENT_TEXT like ',%'
and fcr.PHASE_CODE in ('P','R')
ORDER BY ARGUMENT_TEXT DESC;
PROMPT <h1> Year End Specific Concurrent Programs </h1>
SELECT DISTINCT
cq.concurrent_queue_name || ' - ' || target_node qname,
cr.request_id "Req Id",
TO_NUMBER(DECODE (cr.parent_request_id, -1, NULL, cr.parent_request_id)) "Parent Id",
DECODE(SUBSTR(ct.user_concurrent_program_name,1,10),'Report Set',
cr.description||' (Report Set)',ct.user_concurrent_program_name) "Concurrent Program",
pr.concurrent_program_name "Program",
us.user_name "User Name",
to_char(cr.actual_start_date,'MM/DD HH24:MI:SS') "Start Date/Time",
DECODE(cr.phase_code,
'C', 'Completed', 'P', 'Pending',
'R', 'Running', 'I', 'Inactive','T','Terminated',
cr.phase_code) "Phase",
DECODE(cr.status_code,
'A', 'Waiting', 'B', 'Resuming',
'C', 'Normal', 'D', 'Cancelled', 'E', 'Error',
'G', 'Warning', 'H', 'On Hold', 'I', 'Normal',
'Q', 'Standby', 'R', 'Normal', 'S', 'Suspended',
'U', 'Disabled', 'W', 'Paused', 'X', 'Terminated',
'F', 'Scheduled','M', 'NO Manager','T', 'Terminated',
'Z', 'Waiting', cr.status_code) "Status",
ROUND((NVL(actual_completion_date,SYSDATE)-actual_start_date)*1440,2) "Time (mins)",
DECODE
(cr.phase_code,'R',
to_char(trunc( sysdate - cr.actual_start_date ),'FM0') || ':' ||
to_char(trunc( mod( (sysdate - cr.actual_start_date)*24, 24 ) ), 'FM00') || ':' ||
to_char(trunc( mod( (sysdate - cr.actual_start_date)*24*60, 60 ) ), 'FM00') || ':' ||
to_char(trunc( mod( (sysdate - cr.actual_start_date)*24*60*60, 60 ) ), 'FM00') ,
to_char(trunc( cr.actual_completion_date - cr.actual_start_date ),'FM0') || ':' ||
to_char(trunc( mod( (cr.actual_completion_date - cr.actual_start_date)*24, 24 ) ), 'FM00') || ':' ||
to_char(trunc( mod( (cr.actual_completion_date - cr.actual_start_date)*24*60, 60 ) ), 'FM00') || ':' ||
to_char(trunc( mod( (cr.actual_completion_date - cr.actual_start_date)*24*60*60, 60 ) ), 'FM00')
) "Time"
FROM
apps.fnd_Concurrent_requests cr,
apps.fnd_concurrent_processes cp,
apps.fnd_concurrent_queues cq,
apps.fnd_concurrent_programs_tl ct,
apps.fnd_concurrent_programs pr,
apps.fnd_user us
WHERE cr.controlling_manager = cp.concurrent_process_id
AND cr.concurrent_program_id = pr.concurrent_program_id
AND cr.program_application_id = pr.application_id
AND ct.concurrent_program_id = pr.concurrent_program_id
AND cp.queue_application_id = cq.application_id
AND cp.concurrent_queue_id = cq.concurrent_queue_id
AND ct.language = 'US'
AND cr.requested_by = us.user_id
AND status_code = 'R'
AND ROUND((NVL(actual_completion_date,SYSDATE)-actual_start_date)*1440,2) >= 5
and ct.user_concurrent_program_name in ('Run MassAllocations',
'RAM Net Cost Allocation Program',
'Year End Closing Process',
'Add/Delete Summary Accounts',
'Open Period',
'Posting',
'Journal Import',
'RAM CFRS Trial Balance Extract',
'Open Encumbrance Year',
'Open Budget Year',
'GTAS / Data Act Processes',
'RAM GTAS - Update TPTAS Information',
'RAM GTAS Pre-Process',
'RAM GTAS Bulk File',
'Federal File Creation Process',
'Federal Accounts Creation Process',
'Federal Activity Attributes Creation',
'RAM CFRS Trial Balance Extract',
'RAM Consolidated CFRS/IDDA Extract Process',
'RAM Net Cost Allocation Program',
'RAM Budget Execution Configuration Roll Forward Process',
'RAM UDO Audit Extract Process',
'RAM GL Transactions Audit Extract Process',
'RAM Expense Audit Extract Process',
'RAM AR Audit Extract Process',
'RAM AP 2110 Audit Extract Process',
'RAM AP 2190 Audit Extract Process',
'RAM Cash Audit Extract Process',
'RAM Trial Balance Audit Extract Process',
'RAM Revenue Audit Extract Process')
ORDER BY ROUND((NVL(actual_completion_date,SYSDATE)-actual_start_date)*1440,2) DESC
/
PROMPT <h3><br>Year End Specific Concurrent Programs - Sessions</h3>
SELECT DISTINCT
cr.request_id "Request ID",
DECODE(SUBSTR(cptl.user_concurrent_program_name,1,10),'Report Set',
cr.description||' (Report Set)',cptl.user_concurrent_program_name) ||'<br>'||
'('||cr.argument_text||')' "Concurrent Program/Parameters",
'Queue name: '||cq.concurrent_queue_name || '<br>'||
'APP Server: '||upper(target_node)|| '<br>'||
'PID : '||cr.os_process_id "APP Process",
(select
'DB Server : '||upper(vi.host_name)||' Instance: '||vi.instance_name|| ' Instance id: '||vs.inst_id || '<br>'||
'Session : SID: '||vs.sid|| ' Serial#: '||vs.serial#||' PID: '||cr.oracle_process_id||' Status: '||vs.status|| '<br>'||
'SQL ID : '||vs.sql_id|| '<br>'||
'Wait Event: '||w.event||'('||w.seconds_in_wait||') seconds'
FROM gv\$session vs, gv\$process vp, gv\$instance vi, gv\$session_wait w
WHERE vp.addr= vs.paddr
AND vs.module is NOT NULL
AND vs.audsid=cr.oracle_session_id
AND vs.inst_id=vp.inst_id
AND vs.inst_id=w.inst_id
AND vs.inst_id=vi.inst_id
AND w.sid=vs.sid
AND rownum=1
) "DB Session"
from apps.fnd_concurrent_programs cp,
apps.fnd_concurrent_programs_tl cptl,
apps.fnd_concurrent_requests cr,
applsys.fnd_USER us,
apps.fnd_concurrent_queues cq,
apps.fnd_concurrent_processes cpp
where cp.application_id = cptl.application_id
and cp.concurrent_program_id = cptl.concurrent_program_id
and cptl.language='US'
and cr.program_application_id = cp.application_id
and cr.concurrent_program_id = cp.concurrent_program_id
and us.user_id = cr.requested_by
AND cpp.queue_application_id = cq.application_id
AND cpp.concurrent_queue_id = cq.concurrent_queue_id
AND cr.controlling_manager = cpp.concurrent_process_id
AND status_code = 'R'
and cptl.user_concurrent_program_name in ('Run MassAllocations',
'RAM Net Cost Allocation Program',
'Year End Closing Process',
'Add/Delete Summary Accounts',
'Open Period',
'Posting',
'Journal Import',
'RAM CFRS Trial Balance Extract',
'Open Encumbrance Year',
'Open Budget Year',
'GTAS / Data Act Processes',
'RAM GTAS - Update TPTAS Information',
'RAM GTAS Pre-Process',
'RAM GTAS Bulk File',
'Federal File Creation Process',
'Federal Accounts Creation Process',
'Federal Activity Attributes Creation',
'RAM CFRS Trial Balance Extract',
'RAM Consolidated CFRS/IDDA Extract Process',
'RAM Net Cost Allocation Program',
'RAM Budget Execution Configuration Roll Forward Process',
'RAM UDO Audit Extract Process',
'RAM GL Transactions Audit Extract Process',
'RAM Expense Audit Extract Process',
'RAM AR Audit Extract Process',
'RAM AP 2110 Audit Extract Process',
'RAM AP 2190 Audit Extract Process',
'RAM Cash Audit Extract Process',
'RAM Trial Balance Audit Extract Process',
'RAM Revenue Audit Extract Process')
AND ROUND((NVL(actual_completion_date,SYSDATE)-actual_start_date)*1440,2) >= 5
/
PROMPT <h1>Regular Concurrent Programs</h1>
SELECT DISTINCT
cq.concurrent_queue_name || ' - ' || target_node qname,
cr.request_id "Req Id",
TO_NUMBER(DECODE (cr.parent_request_id, -1, NULL, cr.parent_request_id)) "Parent Id",
DECODE(SUBSTR(ct.user_concurrent_program_name,1,10),'Report Set',
cr.description||' (Report Set)',ct.user_concurrent_program_name) "Concurrent Program",
pr.concurrent_program_name "Program",
us.user_name "User Name",
to_char(cr.actual_start_date,'MM/DD HH24:MI:SS') "Start Date/Time",
DECODE(cr.phase_code,
'C', 'Completed', 'P', 'Pending',
'R', 'Running', 'I', 'Inactive','T','Terminated',
cr.phase_code) "Phase",
DECODE(cr.status_code,
'A', 'Waiting', 'B', 'Resuming',
'C', 'Normal', 'D', 'Cancelled', 'E', 'Error',
'G', 'Warning', 'H', 'On Hold', 'I', 'Normal',
'Q', 'Standby', 'R', 'Normal', 'S', 'Suspended',
'U', 'Disabled', 'W', 'Paused', 'X', 'Terminated',
'F', 'Scheduled','M', 'NO Manager','T', 'Terminated',
'Z', 'Waiting', cr.status_code) "Status",
ROUND((NVL(actual_completion_date,SYSDATE)-actual_start_date)*1440,2) "Time (mins)",
DECODE
(cr.phase_code,'R',
to_char(trunc( sysdate - cr.actual_start_date ),'FM0') || ':' ||
to_char(trunc( mod( (sysdate - cr.actual_start_date)*24, 24 ) ), 'FM00') || ':' ||
to_char(trunc( mod( (sysdate - cr.actual_start_date)*24*60, 60 ) ), 'FM00') || ':' ||
to_char(trunc( mod( (sysdate - cr.actual_start_date)*24*60*60, 60 ) ), 'FM00') ,
to_char(trunc( cr.actual_completion_date - cr.actual_start_date ),'FM0') || ':' ||
to_char(trunc( mod( (cr.actual_completion_date - cr.actual_start_date)*24, 24 ) ), 'FM00') || ':' ||
to_char(trunc( mod( (cr.actual_completion_date - cr.actual_start_date)*24*60, 60 ) ), 'FM00') || ':' ||
to_char(trunc( mod( (cr.actual_completion_date - cr.actual_start_date)*24*60*60, 60 ) ), 'FM00')
) "Time"
FROM
apps.fnd_Concurrent_requests cr,
apps.fnd_concurrent_processes cp,
apps.fnd_concurrent_queues cq,
apps.fnd_concurrent_programs_tl ct,
apps.fnd_concurrent_programs pr,
apps.fnd_user us
WHERE cr.controlling_manager = cp.concurrent_process_id
AND cr.concurrent_program_id = pr.concurrent_program_id
AND cr.program_application_id = pr.application_id
AND ct.concurrent_program_id = pr.concurrent_program_id
AND cp.queue_application_id = cq.application_id
AND cp.concurrent_queue_id = cq.concurrent_queue_id
AND ct.language = 'US'
AND cr.requested_by = us.user_id
AND status_code = 'R'
-- AND ROUND((NVL(actual_completion_date,SYSDATE)-actual_start_date)*1440,2) >= 30
ORDER BY ROUND((NVL(actual_completion_date,SYSDATE)-actual_start_date)*1440,2) DESC
/
PROMPT <h1><br>Regular Concurrent Programs - Sessions</h1>
SELECT DISTINCT
cr.request_id "Request ID",
DECODE(SUBSTR(cptl.user_concurrent_program_name,1,10),'Report Set',
cr.description||' (Report Set)',cptl.user_concurrent_program_name) ||'<br>'||
'('||cr.argument_text||')' "Concurrent Program/Parameters",
'Queue name: '||cq.concurrent_queue_name || '<br>'||
'APP Server: '||upper(target_node)|| '<br>'||
'PID : '||cr.os_process_id "APP Process",
(select
'DB Server : '||upper(vi.host_name)||' Instance: '||vi.instance_name|| ' Instance id: '||vs.inst_id || '<br>'||
'Session : SID: '||vs.sid|| ' Serial#: '||vs.serial#||' PID: '||cr.oracle_process_id||' Status: '||vs.status|| '<br>'||
'SQL ID : '||vs.sql_id|| '<br>'||
'Wait Event: '||w.event||'('||w.seconds_in_wait||') seconds'
FROM gv\$session vs, gv\$process vp, gv\$instance vi, gv\$session_wait w
WHERE vp.addr= vs.paddr
AND vs.module is NOT NULL
AND vs.audsid=cr.oracle_session_id
AND vs.inst_id=vp.inst_id
AND vs.inst_id=w.inst_id
AND vs.inst_id=vi.inst_id
AND w.sid=vs.sid
AND rownum=1
) "DB Session"
from apps.fnd_concurrent_programs cp,
apps.fnd_concurrent_programs_tl cptl,
apps.fnd_concurrent_requests cr,
applsys.fnd_USER us,
apps.fnd_concurrent_queues cq,
apps.fnd_concurrent_processes cpp
where cp.application_id = cptl.application_id
and cp.concurrent_program_id = cptl.concurrent_program_id
and cptl.language='US'
and cr.program_application_id = cp.application_id
and cr.concurrent_program_id = cp.concurrent_program_id
and us.user_id = cr.requested_by
AND cpp.queue_application_id = cq.application_id
AND cpp.concurrent_queue_id = cq.concurrent_queue_id
AND cr.controlling_manager = cpp.concurrent_process_id
AND status_code = 'R'
-- AND ROUND((NVL(actual_completion_date,SYSDATE)-actual_start_date)*1440,2) >= 30
/
SPOOL OFF
EOF
echo "<html>" > $LOG_FILE.html
echo "<title>$MSUB</title>" >> $LOG_FILE.html
echo "<style>" >> $LOG_FILE.html
echo " h1 {font:11pt Arial,Helvetica,sans-serif; color:black; background:White;line-height:0px;margin-top:16px;margin-bottom:0px;}" >> $LOG_FILE.html
echo " body {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;line-height:0px;margin-top:0px;margin-bottom:0px;}" >> $LOG_FILE.html
echo "</style>" >> $LOG_FILE.html
echo "<head>" >> $LOG_FILE.html
echo "<h3><b>$MSUB</b></h1>" >> $LOG_FILE.html
echo "<b>Enviroment:</b> $ORACLE_SID" >> $LOG_FILE.html
echo "<br><b>Context</b>: $CONTEXT_NAME<br>`date '+Date: %m/%d/%y Time:%H:%M:%S'`<br>" >> $LOG_FILE.html
echo "</head><hr />" >> $LOG_FILE.html
echo "<body>" >> $LOG_FILE.html
cat $LOG_FILE >> $LOG_FILE.html
echo "</body>" >> $LOG_FILE.html
echo "</html>" >> $LOG_FILE.html
if [ `grep -ci "rows selected." $LOG_FILE` -ne 0 -o `grep -ci "row selected." $LOG_FILE` -ne 0 ];then
mailx -s "$(echo "$ORACLE_SID: Running Concurrent Programs\nContent-Type: text/html")" $EMAIL_LIST < $LOG_FILE.html
fi
