-- ################################################################
--
-- %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'
/
