io.sql

/* The total i/o for a single disk is the sum of phyrds and phwrts 	*/
/* for all the database files managed by the oracle instance on the disk.*/
/* Determine the hardware/operating system limits on the capacity of the*/
/* disk.  Disks operating at or near full capacity are potential sites	*/
/* disk contention.							*/								

/* RATIO represents the number of physical reads to physical blocks read */
/* The closer this number is to MULTIBLOCK_READ_COUNT, the more likely   */
/* that there are a lot of full table scans.                             */

select substr(name,1,30) DATA_FILE, substr(tablespace_name,1,10) tablespace, 
	phyrds, phyblkrd, 
	substr(trunc(phyblkrd/decode(phyrds,0,1,phyrds)),1,4) RATIO,
	phywrts
from v$datafile df, v$filestat fs, sys.dba_data_files dbadf
where df.file# = fs.file# and
	df.name = dbadf.file_name
order by substr(name,1,4); 


select name, value 
from v$sysstat
where name like 'table scans%';