Re: Help with reporting file IO activity for each tablesapce

From: Stephen Ashmore <sashmore_at_neonramp.com>
Date: Sun, 20 Jan 2002 09:46:48 -0600
Message-ID: <u4lpj53q1fto42@corp.supernews.com>

You can query v$filestat ,
it will show you pysical read and write per datafile.

Here is one sample that breaks the I/O up by mount point

create view max_total_view
  as select
   max(x.phyrds + x.phywrts) max_total
   from sys.v_$filestat x, sys.ts$ ts, sys.v_$datafile i, sys.file$ f   where i.file#=f.file#
  and ts.ts#=f.ts#
  and x.file#=f.file#;
rem
column drive format a13
column filename format a45
column total_io format 999999999
column weight format 999.99
break on drive skip 1 on report
compute sum LABEL 'Totals:' of weight on drive compute sum of total_io on drive
compute sum LABEL 'Total I/O:' of total_io on report set linesize 80 pagesize 200 feedback off ttitle skip center "DATABASE FILE IO WEIGHTS" skip center "ORDERED BY DRIVE"

skip center "---------------------------" skip

rem
select
  substr(i.name,1,13) "DRIVE", /*assumes a 13 letter drive name*/

  i.name filename,
  x.phyrds +
  x.phywrts "TOTAL_IO",

  ROUND(100 * ( x.phyrds + x.phywrts ) / m.max_total,2) weight   from sys.v_$filestat x, sys.ts$ ts, sys.v_$datafile i,  sys.file$ f, max_total_view m
  where i.file#=f.file#
  and ts.ts#=f.ts#
  and x.file#=f.file#
  order by 1, 2;

Stephen C. Ashmore
Brainbench MVP for Oracle Administration http://www.brainbench.com

"Andy Trigg" <atrigg_at_optusnet.com.au> wrote in message news:3c4a9f47$0$4019$afc38c87_at_news.optusnet.com.au...
> I am trying to design a script that will provide me with the file IO
> activity that is occuring for each table space. I have had a look at the
> utlbstat.sql/utlestat.sql script pair. These produce closely to what I
want
> except they only show the number of reads and writes that have occured and
> not the actualy number of bytes read or written. I am really after the
> activity measured in bytes.
>
> Can any body give me any idea how I can extrapolate this type of
> information? I need the solution to be OS independant. So suggesting using
> perfmon on NT is not an option. I really would like an Oracle script to
> achieve this.
> Thanks in advance.
>
> Andy
>
>
Received on Sun Jan 20 2002 - 09:46:48 CST