-- Purpose: Monitor Data Access Activities (Full Table and Index Scans, Chained Rows)
-- Use: Needs Oracle DBA 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 'Monitor Data Access Activities' skip 2
spool monitor_data_activites.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'
/