SQL Script Database File Statistics

Updated: 02-Jun-2005
Version: 3.0

Description

Script displays file statistics within the database.

This script displays the read/write IO as a percentage of the total IO on the database

Parameters

None

SQL Source

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 

set pages 200
set lines 140
col tablespace_name form a15
col name form a65
col total form 999.99 heading "% Total IO"
col readio form 999.99 heading "% Read IO"
col writeio form 999.99 heading "% Write IO"
col Avg_Rd_Time     Format 90.9999999     Heading "Average |Read Time|Per Block"
col Avg_Wrt_Time    Format 90.9999999     Heading "Average |Write Time|Per Block"

spool filestat.lst

select a.tablespacE_name, d.name,s.PHYRDS,s.PHYWRTS,
       ((s.ReadTim / Decode(s.Phyrds, 0, 1, s.Phyblkrd)) / 100) Avg_Rd_Time,
       ((s.WriteTim / Decode(s.PhyWrts, 0, 1, s.PhyblkWrt)) / 100) Avg_Wrt_Time
from v$datafile d, v$filestat s, dba_data_files a
where d.file#=s.file#
  And d.File# = a.File_Id
order by 1
/

PROMPT Percentage of Reads vs Writes on the Database

select sum(PHYWRTS) *100 / sum(PHYWRTS+ PHYRDS) writeio
      ,sum(PHYRDS) *100 / sum(PHYWRTS+ PHYRDS) readio
from v$filestat s
/

PROMPT Percentage Of Reads on Database by file

select a.tablespacE_name, d.name,(s.PHYRDS)*100/(t.total) total
from v$datafile d, v$filestat s, dba_data_files a
 , (select sum(PHYRDS) total from v$filestat) t
where d.file#=s.file#
  And d.File# = a.File_Id
order by 3 desc 
/

PROMPT Percentage Of Writes on Database by file

select a.tablespacE_name, d.name,(s.PHYWRTS)*100/(t.total) total
from v$datafile d, v$filestat s, dba_data_files a
 , (select sum(PHYWRTS) total from v$filestat) t
where d.file#=s.file#
  And d.File# = a.File_Id
order by 3 desc 
/

PROMPT Percentage Of Total IO Database by file

select a.tablespace_name, d.name,(s.PHYWRTS+s.PHYRDS)*100/(t.total) total
from v$datafile d, v$filestat s, dba_data_files a
 , (select sum(PHYWRTS+PHYRDS) total from v$filestat) t
where d.file#=s.file#
  And d.File# = a.File_Id
order by 3 desc 
/

spool off

Return to Index of SQL Scripts

Site Map

General Information : Home | Company Profile | Contact Us | Legal
Database Support : Proactive Support | Pay Per Incident Support
Expert Services : High Availability | Creative Solutions | Training
Build Services : Oracle Installations | Revive your Oracle Setup | Low Cost Development
Free Oracle Resources: DBA Scripts | Oracle Links