Script: DB Access Monitoring Report

-- ################################################################

--

-- %Purpose: DB Access Monitoring Report 

--           (Full Table and Index Scans, Chained Rows)

--

-- Use:      Needs Oracle DB Access

--

-- ################################################################

--

set feed off;

set pagesize 10000;

set wrap off;

set linesize 200;

set heading on;

set tab on;

set scan on;

set verify off;

set termout on;

column  STATISTIC#  form 999        head 'Id'

column  NA          form a32        head 'Statistic'

column  RIA         form 990.90     head 'Row Access via|Index [%]'

column  RTS         form 990.90     head 'Row Access via|Table Scan [%]'

column  RA          form 9999999990 head 'Rows accessed'

column  PCR         form 990.90     head 'Chained|Rows [%]'

colum   CL          form 990.90     head 'Cluster|Length'

ttitle  left  'DB Access Monitoring Report'  skip 2

spool   db_access.log

select  rpad (NAME, 32, '.') as NA,

        VALUE

  from  V$SYSSTAT

 where  NAME like '%table scan%'

    or  NAME like '%table fetch%'

    or  NAME like '%cluster%';

ttitle  off

select  A.VALUE + B.VALUE                     as RA,

        A.VALUE / (A.VALUE + B.VALUE) * 100.0 as RIA,

        B.VALUE / (A.VALUE + B.VALUE) * 100.0 as RTS,

        C.VALUE / (A.VALUE + B.VALUE) * 100.0 as PCR,

        E.VALUE / D.VALUE                     as CL

  from  V$SYSSTAT A,

        V$SYSSTAT B,

        V$SYSSTAT C,

        V$SYSSTAT D,

        V$SYSSTAT E

 where  A.NAME = 'table fetch by rowid'

   and  B.NAME = 'table scan rows gotten'

   and  C.NAME = 'table fetch continued row'

   and  D.NAME = 'cluster key scans'

   and  E.NAME = 'cluster key scan block gets'

/