=== 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
