#################################################################################
---------------------- Database health Monitor ---------------------------------
#################################################################################
break on today
column today noprint new_value tdate
select substr(to_char(sysdate,'fmMonth DD, YYYY HH:MI:SS P.M.'),1,35) today from dual;
column name noprint new_value DB
select name from v$database;
set heading on
set feedback off
spool health.lst
--spool Windows Path or Unix Path\health.lst
prompt **********************************************************
prompt ***** Database Information *****
prompt **********************************************************
ttitle left "DATABASE: "DB" (AS OF: "tdate")"
select name, created, log_mode from v$database;
prompt
prompt **********************************************************
ttitle off
rem -------------------------------------------------------------
rem DB Block Buffer - Hit Ratio
rem -------------------------------------------------------------
clear breaks
clear computes
set heading off
set feedback off
set termout off
create table dbbb (
PR number,
CG number,
dbbg number);
insert into DBBB values (0,0,0);
UPDATE dbbb
set dbbg =
(SELECT VALUE FROM V$SYSSTAT WHERE NAME = 'db block gets');
UPDATE dbbb
set cg =
(SELECT VALUE FROM V$SYSSTAT WHERE NAME = 'consistent gets');
UPDATE dbbb
set pr =
(SELECT VALUE FROM V$SYSSTAT WHERE NAME = 'physical reads');
set heading on
set termout on
column "Physical Reads" format 99,999,999,999
column "Consistent Gets" format 99,999,999,999
column "DB Block Gets" format 99,999,999,999
column "Percent (Above 70% ?)" format 999.99
TTitle left "***** Database: "db", DB Block Buffers ( As of: "tdate" ) *****" skip 1 - left "Percent = (100*(1-(Physical Reads/(Consistent Gets + DB Block Gets))))" skip 2
SELECT pr "Physical Reads",
cg "Consistent Gets",
dbbg "DB Block Gets",
(100*(1-(PR/(CG+dbbg)))) "Percent (Above 70% ?)"
from dbbb;
set heading off
set termout off
drop table dbbb;
ttitle off
clear breaks
clear computes
set heading on
set termout on
rem -------------------------------------------------------------
rem Shared Pool Size - Gets and Misses
rem -------------------------------------------------------------
set line 150
column "Executions" format 999,999,990
column "Cache Misses Executing" format 999,999,990
column "Data Dictionary Gets" format 999,999,999
column "Get Misses" format 999,999,999
ttitle left skip 1 - left "********** Shared Pool Size (Execution Misses) **********" skip 1
select sum(pins) "Executions",
sum(reloads) "Cache Misses Executing",
(sum(reloads)/sum(pins)*100) "% Ratio (STAY UNDER 1%)"
from v$librarycache;
ttitle left "********** Shared Pool Size (Dictionary Gets) **********" skip 1
select sum(gets) "Data Dictionary Gets",
sum(getmisses) "Get Misses",
100*(sum(getmisses)/sum(gets)) "% Ratio (STAY UNDER 12%)"
from v$rowcache;
ttitle off
rem -------------------------------------------------------------
rem Log Buffer
rem -------------------------------------------------------------
ttitle left "********** Log Buffers **********" skip 1
select substr(name,1,25) Name,
substr(value,1,15) "VALUE (Near 0?)"
from v$sysstat
where name = 'redo log space requests';
ttitle off
rem -------------------------------------------------------------
rem Latch Contention
rem -------------------------------------------------------------
ttitle left "********** Latch Information **********" skip 1
select substr(l.name,1,25) Name,
l.gets, l.misses,
l.immediate_gets, l.immediate_misses
from v$latch l, v$latchname ln
where ln.name in ('redo allocation', 'redo copy')
and ln.latch# = l.latch#;
ttitle off
rem -------------------------------------------------------------
rem Reinstates the xdbname parameter
rem -------------------------------------------------------------
column name noprint new_value xdbname
select name from v$database;
rem -------------------------------------------------------------
rem Tablespace Usage
rem -------------------------------------------------------------
set pagesize 66
set line 132
clear breaks
clear computes
column "Total Bytes" format 9,999,999,999
column "SQL Blocks" format 999,999,999
column "VMS Blocks" format 999,999,999
column "Bytes Free" format 9,999,999,999
column "Bytes Used" format 9,999,999,999
column "% Free" format 9999.999
column "% Used" format 9999.999
break on report
compute sum of "Total Bytes" on report
compute sum of "SQL Blocks" on report
compute sum of "VMS Blocks" on report
compute sum of "Bytes Free" on report
compute sum of "Bytes Used" on report
compute avg of "% Free" on report
compute avg of "% Used" on report
TTitle left "******* Database: "db", Current Tablespace Usage ( As of: "tdate" ) *******" skip 1
select substr(fs.FILE_ID,1,3) "ID#",
fs.tablespace_name,
df.bytes "Total Bytes",
df.blocks "SQL Blocks",
df.bytes/512 "VMS Blocks",
sum(fs.bytes) "Bytes Free",
(100*((sum(fs.bytes))/df.bytes)) "% Free",
df.bytes-sum(fs.bytes) "Bytes Used",
(100*((df.bytes-sum(fs.bytes))/df.bytes)) "% Used"
from sys.dba_data_files df, sys.dba_free_space fs
where df.file_id(+) = fs.file_id
group by fs.FILE_ID, fs.tablespace_name, df.bytes, df.blocks
order by fs.tablespace_name;
ttitle off
rem -------------------------------------------------------------
rem Disk Activity
rem -------------------------------------------------------------
column "File Total" format 99,999,990
set line 132
set pagesize 33
ttitle " ***** Database: "db", DataFile's Disk Activity (As of:" tdate " ) *****"
select substr(df.file#,1,2) "ID",
rpad(substr(name,1,52),52,'.') "File Name",
rpad(substr(phyrds,1,10),10,'.') "Phy Reads",
rpad(substr(phywrts,1,10),10,'.') "Phy Writes",
rpad(substr(phyblkrd,1,10),10,'.') "Blk Reads",
rpad(substr(phyblkwrt,1,10),10,'.') "Blk Writes",
rpad(substr(readtim,1,9),9,'.') "Read Time",
rpad(substr(writetim,1,10),10,'.') "Write Time",
(sum(phyrds+phywrts+phyblkrd+phyblkwrt+readtim)) "File Total"
from v$filestat fs, v$datafile df
where fs.file# = df.file#
group by df.file#, df.name, phyrds, phywrts, phyblkrd,
phyblkwrt, readtim, writetim
order by sum(phyrds+phywrts+phyblkrd+phyblkwrt+readtim) desc, df.name;
ttitle off
rem -------------------------------------------------------------
rem Fragmentation Need
rem -------------------------------------------------------------
set heading on
set termout on
set pagesize 66
set line 132
ttitle left " ***** Database: "db", DEFRAGMENTATION NEED, AS OF: " tdate " *****"
select substr(de.owner,1,8) "Owner",
substr(de.segment_type,1,8) "Seg Type",
substr(de.segment_name,1,35) "Table Name (Segment)",
substr(de.tablespace_name,1,20) "Tablespace Name",
count(*) "Frag NEED",
substr(df.name,1,40) "DataFile Name"
from sys.dba_extents de, v$datafile df
where de.owner <> 'SYS'
and de.file_id = df.file#
and de.segment_type = 'TABLE'
group by de.owner, de.segment_name, de.segment_type, de.tablespace_name,
df.name
having count(*) > 1
order by count(*) desc;
ttitle off
rem -------------------------------------------------------------
rem Rollback Information
rem -------------------------------------------------------------
set pagesize 66
set line 132
TTitle left "*** Database: "db", Rollback Information ( As of: " tdate " ) ***" skip 2
select substr(sys.dba_rollback_segs.SEGMENT_ID,1,5) "ID#",
substr(sys.dba_segments.OWNER,1,8) "Owner",
substr(sys.dba_segments.TABLESPACE_NAME,1,17) "Tablespace Name",
substr(sys.dba_segments.SEGMENT_NAME,1,17) "Rollback Name",
substr(sys.dba_rollback_segs.INITIAL_EXTENT,1,10) "INI_Extent",
substr(sys.dba_rollback_segs.NEXT_EXTENT,1,10) "Next Exts",
substr(sys.dba_segments.MIN_EXTENTS,1,5) "MinEx",
substr(sys.dba_segments.MAX_EXTENTS,1,5) "MaxEx",
substr(sys.dba_segments.PCT_INCREASE,1,5) "%Incr",
substr(sys.dba_segments.BYTES,1,15) "Size (Bytes)",
substr(sys.dba_segments.EXTENTS,1,6) "Extent#",
substr(sys.dba_rollback_segs.STATUS,1,10) "Status"
from sys.dba_segments, sys.dba_rollback_segs
where sys.dba_segments.segment_name = sys.dba_rollback_segs.segment_name and
sys.dba_segments.segment_type = 'ROLLBACK'
order by sys.dba_rollback_segs.segment_id;
ttitle off
TTitle left " " skip 2 - left "*** Database: "db", Rollback Status ( As of: " tdate " ) ***" skip 2
select substr(V$rollname.NAME,1,20) "Rollback_Name",
substr(V$rollstat.EXTENTS,1,6) "EXTENT",
v$rollstat.RSSIZE, v$rollstat.WRITES,
substr(v$rollstat.XACTS,1,6) "XACTS",
v$rollstat.GETS,
substr(v$rollstat.WAITS,1,6) "WAITS",
v$rollstat.HWMSIZE, v$rollstat.SHRINKS,
substr(v$rollstat.WRAPS,1,6) "WRAPS",
substr(v$rollstat.EXTENDS,1,6) "EXTEND",
v$rollstat.AVESHRINK,
v$rollstat.AVEACTIVE
from v$rollname, v$rollstat
where v$rollname.USN = v$rollstat.USN
order by v$rollname.USN;
ttitle off
TTitle left " " skip 2 - left "*** Database: "db", Rollback Segment Mapping ( As of: " tdate " ) ***" skip 2
select r.name Rollback_Name,
p.pid Oracle_PID,
p.spid VMS_PID,
nvl(p.username,'NO TRANSACTION') Transaction,
p.terminal Terminal
from v$lock l, v$process p, v$rollname r
where l.addr = p.addr(+)
and trunc(l.id1(+)/65536)=r.usn
and l.type(+) = 'TX'
and l.lmode(+) = 6
order by r.name;
ttitle off
rem -------------------------------------------------------------
rem Current Users
rem -------------------------------------------------------------
set line 132
set pagesize 66
TTitle left "*** Database: "db", Current User Info (As of: "tdate") ***" skip 1
select substr(s.username,1,15) "DB UserName",
s.osuser "OS UserName",
substr(a.object,1,25) Object,
a.type , s.command,
substr(s.machine,1,15) Machine,
substr(s.terminal,1,15) Terminal, s.process, s.status
from v$access a, v$session s
where a.sid = s.sid
order by username;
TTitle left "*** Database: "db", Current Sessions (As of: "tdate") ***" skip 1
select substr(username,1,15) "DB UserName",
substr(osuser,1,15) "OS UserName",
substr(command,1,3) CMD,
substr(machine,1,10) Machine,
terminal, process, status,
substr(program,1,50) "OS Program Name"
from v$session
where type = 'USER'
order by username;
TTitle left "*** Database: "db", Current Access (As of: "tdate") ***" skip 1
select sid,
substr(owner,1,15) Owner,
substr(object,1,25) Object,
type
from v$access
order by owner;
rem -------------------------------------------------------------
rem -------------------------------------------------------------
spool off
set feedback on
