--			SQL Script

--Description

--This script lists the key database performance indicators for a quick overview of current database performance

--Parameters 

--None.

--SQL Source 
set feed off
set ver off
set echo off

select trunc((1- (sum(decode(name,'physical reads',value,0))/
	     (sum(decode(name,'db block gets',value,0))
	   + (sum(decode(name,'consistent gets',value,0)))))) * 100) "BUFFER HIT RATIO"
from v$sysstat;

select a.value+b.value "Logical reads",
       c.value         "Physical Reads",
       d.value	       "Physical Writes",
       round(100*((a.value+b.value)-c.value)/(a.value+b.value)) "BUFFER HIT RATIO",
       round(c.value*100/(a.value+b.value)) "Missed"
from v$sysstat a,v$sysstat b,v$sysstat c,v$sysstat d
where a.statistic#=37
and   b.statiStic#=38
and   c.statiStic#=39
and   d.statistic#=40	

prompt
prompt DATA DICTIONARY HIT RATIO

select sum(gets) "Data,Dict.Gets",
       sum(getmisses) "Data Dict. Cache Misses",
       round((1-(sum(getmisses)/sum(gets)))*100) "DATA DICT CACHE HIT RATIO",
       round(sum(getmisses)*100/sum(gets)) "%MISSED"
from v$rowcache;

prompt 
prompt LIBRARY CACHE MISS RATIO :

select sum(pins) "executions",
       Sum(reloads) "Cache Misses",
       1-(sum(reloads)/ sum(PINS)) "LIBRARY CACHE HIT RATIO",
	ROUND(SUM(RELOADS)*100/SUM(PINS))  "%MISSED"
from v$librarycache;


select namespace,
	trunc(gethitratio*100) "Hit Ratio",
	trunc(pinhitratio*100) "Pin Hit Ratio",
	reloads "Reloads"
from v$librarycache;

prompt
prompt REDO LOG BUFFER

select substr(name,1,30),value
from v$sysstat where name='redo log space requests';
select name, bytes from v$sgastat where name='free memory';

select sum(executions) "TotSQL since startup",
       sum(users_executing) "SQL executing now"
from v$sqlarea;

SELECT 'STARTUP DATE    : '||DECODE(KEY,'STARTUP TIME - JULIAN' , TO_CHAR(TO_DATE(VALUE,'J'),'DD-MON-YY ')) "Startup Date"  FROM V$INSTANCE WHERE
KEY = 'STARTUP TIME - JULIAN'
union
SELECT 'STARTUP TIME    : '||DECODE(KEY,'STARTUP TIME - SECONDS' ,ROUND(VALUE/60/60))||':'||DECODE(KEY,'STARTUP TIME - SECONDS', ROUND(((VALUE/60/60)-ROUND(VALUE/60/60))*60)) 
 "Startup Time"  
FROM V$INSTANCE WHERE
KEY = 'STARTUP TIME - SECONDS'
/

--select * from v$instance;
prompt
prompt  LATCH CONTENTION RATIOS

select substr(l.name,1,30)name,
	(misses/(gets+.001))*100 miss_ratio,
	(immediate_misses/(immediate_gets+.001))*100 miss_ratio
from v$latch l,v$latchname ln 
Where l.latch#=ln.latch#
and (misses/(gets+.001)) * 100>  .2
or
    (immediate_misses/(immediate_gets+.001)) * 100 >.2
order by l.name;
prompt  NOTE:    if missratio or immediate_miss_ratio > 1 then latch
prompt  .        contention exists, (decrease LOG_SMALL_ENTRY_MAX_SIZE)

prompt NOTE:    if these are <1% of Total Number of Requests for Data
prompt .        then extra rollback seqments are needed.
select class, count
from v$waitstat
where class in ('free list','system undo header',
		'system undo block','undo header',
		'undo block')
group by class,count;
promp
prompt  TOTAL NUMBER OF REQUESTS FOR DATA 
select sum(value) from v$sysstat
where name in ('db block gets','consistent gets');

set feed on