/* ************************************************ */
/*                                                  */
/* fnctn:   Several statistical reports:            */
/*     1. Calculation of Dictionary Cache Hit Ratio */
/*     2. Calculating Table/Index Access Ratio      */
/*     3. Sort Area Size & Sort Usage               */
/*     4. Calculating Buffer Busy Waits             */
/*                                                  */
/* name:    @c:\oracle\sql\stats.sql                */
/* date:    12-MAY-95                               */
/* creator: Rick Kupcunas (Oracle)                  */
/*                                                  */
/* ************************************************ */

spool stats.rslt;

set verify off
set feedback off

/* ***************************** */
/* 1. Dictionary Cache Hit Ratio */
/* ***************************** */

prompt Calculation of Dictionary Cache Hit Ratio

column count  format 999,990 heading Count
column usage format 99,990 heading Usage  
column gets  format 99,999,990 heading Gets
column getmisses format 99,999,990 heading Misses
column a format 990.99 heading "Hit Ratio"

select substr(parameter,1,25) parameter,
    count,
    usage,
    decode(count-usage,0,'*',' ') Rec,
    gets,
    getmisses,
    ((1-(decode(gets,0,0,getmisses))/decode(gets,0,1,gets))*100) a
from v$rowcache
order by 6 desc;

/* *************************************** */
/* 2. Calculating Table/Index Access Ratio */
/* *************************************** */

prompt
prompt Calculating Table/Index Access Ratio 

column avalue format 999,999,999,990 heading "Table Hits"
column bvalue format 999,999,999,990 heading "Index Hits"
column ht format 990.99 heading "Table %"
column it format 990.99 heading "Index %"

select a.value avalue,
      b.value bvalue,
      (1-b.value/(a.value+b.value))*100 ht,
      (1-a.value/(a.value+b.value))*100 it
from v$sysstat a,
     v$sysstat b
where a.statistic# = 109 and 
      b.statistic# = 111;

/* *************************************** */
/* 3. Sort Area Size & sort usage          */
/* *************************************** */

set head off
prompt Sort Activity

column value format 999,999,990

select 'Sort Area Size : '||value
from v$parameter
where name like '%sort_area_size%';

set head on

column avalue format 999,999,990 heading Sorts(Memory)
column bvalue format 999,999,990 heading Sorts(Disks)
column ht format 990.99 heading "Memory%"
column it format 990.99 heading "Index%"

select a.value avalue,
      b.value bvalue,
      (1-b.value/(a.value+b.value))*100 ht,
      (1-a.value/(a.value+b.value))*100 it
from v$sysstat a,
     v$sysstat b
where a.statistic# =  100
and b.statistic# = 101;

/* *************************************** */
/* 4. Calculating Buffer Busy Waits        */
/* *************************************** */

prompt
prompt Calculating Buffer Busy Waits
prompt 

col count format 999,999,990

select class,count
  from v$waitstat
 where class in ('free list','system undo header',
          'system undo block','undo header','undo block');

spool off;