Monitoring Script

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>