SQL Script Snapshot of Database Performance

Updated : 2-November-1998
Version : 1.0

Description

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

Parameters

None.

SQL Source

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
prompt

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
prompt

select sum(pins) "executions",
       sum(reloads) "Cache Misses",
       round((1-(sum(reloads)/sum(pins)))*100) "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
prompt

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) "Tot SQL since startup",
       sum(users_executing) "SQL executing now"
from v$sqlarea;
select * from v$instance ;
prompt
prompt if miss_ratio or immediate_miss_ratio > 1 then latch
prompt contention exists, (decrease LOG_SMALL_ENTRY_MAX_SIZE

select substr(l.name,1,30) name,
       (misses/(gets+.001))*100 mis_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 If these are < 1% of Total Number of Requests for Data
prompt then extra rollback segments 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;

prompt Total Number of Requests for Data
select sum(value) from v$sysstat 
where name in ('db block gets','consistent gets');

Return to Index of SQL Scripts

Home | Company Profile | Services | Contact Us | SQL scripts and tips | Quiz Legal