Scripts Used in Critical Production Oracle-EBiz Time Monitoring

=== Temp Usage ===

select sum(blocks)/1024*8 "Size in MB" FROM v$sort_usage;

SELECT ss.sid, sum(st.blocks)/1024*8 FROM v$sort_usage st, v$session ss where ss.saddr=st.session_addr group by ss.sid having sum(st.blocks)/1024*8 > 100 order by 2;

SELECT  /*+ RULE */ s.username, s.osuser, s.sid, NVL(s.machine,'N/A'),

                         NVL(s.module,'N/A'), NVL(s.action,'N/A'), NVL(s.program,'N/A'),

                         s.status ||' for '||

                         LPAD(((last_call_et/60)-mod((last_call_et/60),60))/60,2,'0') ||':'||

                         LPAD(ROUND(mod((last_call_et/60),60)),2,'0') ||' Hr' ,

                         u.tablespace, u.contents, u.extents, round((u.blocks*8)/1024),

                         s.sql_address, s.sql_hash_value

                 FROM    v$session s, v$sort_usage u

                 WHERE   s.saddr    = u.session_addr

                 AND     u.contents = 'TEMPORARY'

                 AND     s.audsid != USERENV('sessionid')

                 AND    (u.blocks*8)/1024 >= 1000

                 ORDER   BY 1,2,3,4,5 Desc;

=== High Redo ===

SELECT s.inst_id,s.sid, s.serial#, s.username, s.program, i.block_changes

FROM gv$session s, gv$sess_io i

WHERE s.sid = i.sid

AND i.block_changes > 10000000

ORDER BY 6 desc, 1, 2, 3, 4;

=== Rollback Used ===

                                 

SELECT rn.name, ROUND(rs.rssize/1024/1024),

                        s.username, s.osuser, s.sid, NVL(s.machine,'N/A'),

                        NVL(s.module,'N/A'), NVL(s.action,'N/A'), NVL(s.program,'N/A'),

                        s.sql_address, s.sql_hash_value, p.spid,

                        s.status ||' for '||

                        LPAD(((last_call_et/60)-mod((last_call_et/60),60))/60,2,'0') ||':'||

                        LPAD(ROUND(mod((last_call_et/60),60)),2,'0') ||' Hr' ,

                        round(ceil((t.used_ublk*8)/1024),1)

                 FROM   v$rollname rn, v$rollstat rs,

                        v$session s, v$transaction t, v$process p

                 WHERE  rn.usn = rs.usn

                 AND    round((t.used_ublk*8)/1024) >= 1000

                 AND    rs.usn = t.xidusn

                 AND    s.sid = p.pid (+)

                 AND    s.taddr = t.addr

                 ORDER  BY 2 desc, s.sid ,s.status

=== UNDO segement Information ====

 

select tablespace_name, status segment_status, count(extent_id) "Extent Count", sum(blocks) "Total Blocks", sum(blocks)*8/(1024*1024) "Total Space in GB"

from dba_undo_extents where tablespace_name like '%UNDO%'

group by tablespace_name, status;

=== Shared Pool Usage ===

SELECT INST_ID,ROUND(bytes/1024/1024, 2)||' MB' FROM gv$sgastat  WHERE name='free memory' AND pool='shared pool';

=== Archive Generation for last 5 hours ===

SELECT TO_CHAR(first_time, 'DD-MM-YY') AS Day,TO_CHAR(first_time, 'HH24') AS Hour,COUNT(*)  FROM v$log_history WHERE TO_CHAR(first_time, 'DD-MM-YY') = TO_CHAR(sysdate, 'DD-MM-YY') AND  TO_CHAR(first_time, 'HH24') >= TO_CHAR(sysdate, 'HH24') - 5 GROUP BY  TO_CHAR(first_time, 'DD-MM-YY'), TO_CHAR(first_time, 'HH24') ORDER BY 2;

=== High Memory ===

select s.inst_id, s.sid, name, round(value/1024/1024) Mem_in_mb, p.*

from gv$sesstat s, v$statname n, gv$session p

where n.statistic# = s.statistic#

and s.inst_id = p.inst_id

and s.sid = p.sid

and name like '%pga memory%'

and value > 1*1024*1024*512;

=== Performance ===

select sql_hash_value, count(*) from v$session where event like 'enq%' group by sql_hash_value;

select sql_hash_value,username,osuser, count(*) from v$session where event like 'enq%' and SQL_HASH_VALUE='&event' group by sql_hash_value,username,osuser;

select sql_text from v$sqlarea where hash_value = '&hash_value';

select s1.sid,FETCHES,ROWS_PROCESSED from v$sql s,v$session s1 where s.HASH_VALUE=s1.SQL_HASH_VALUE and s1.sid=4885;

col EVENT for a50;

col OSUSER for a20;

col USERNAME for a20;

set linesize 175;

select s.sid,s.serial#,s.status,s.username,s.module,s.osuser,x.event

from v$session s,v$session_wait x where s.paddr in

( select addr from v$process where spid in (2340,23869,13827,18261,14880,2381))

and x.sid=s.sid;

col EVENT for a50;

col OSUSER for a20;

col USERNAME for a20;

set linesize 175;

select s.sid,s.serial#,s.status,s.username,s.module,s.osuser,x.event from v$session s,v$session_wait x

where x.sid=s.sid and x.event like '&event_name';

=== Top 10 Memory Process at OS level ===

UNIX95=1 ps -eo vsz,pid,args | sort +0n -1 | grep -i `echo $ORACLE_SID` | tail -10

=== Other Script to update the daily report ===

select status,count(1) from gv$session group by status; 

STATUS     COUNT(1)

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

ACTIVE           22

INACTIVE        155

select count(1) from dba_tables where logging='NO';

  COUNT(1)

----------

       919

select distinct status,count(1) from dba_indexes group by status;

STATUS     COUNT(1)

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

N/A            1639

UNUSABLE          1

VALID          6162

select count(1) from dba_objects where status='INVALID';

  COUNT(1)

----------

       681