Re: How to caliculate the IO Demand for IOPS and ThroughtPut

From: Andrew Kerber <andrew.kerber_at_gmail.com>
Date: Sat, 11 Jun 2011 16:01:23 -0500
Message-ID: <BANLkTikvgiLbO8iJyNKGppgY1hSzRtn8gQ_at_mail.gmail.com>

This will get most of the information that you need. You can modifiy it as necessary to get other information:

select

   to_char(sn.end_interval_time,'yyyymmddhh24') the_date,

sum(decode(sn2.startup_time,sn3.startup_time,(newreads.value-oldreads.value),newreads.value)) reads,

sum(decode(sn2.startup_time,sn3.startup_time,(newwrites.value-oldwrites.value),newwrites.value)) writes,

(sum(decode(sn2.startup_time,sn3.startup_time,(newreads.value-oldreads.value),newreads.value)))+

(sum(decode(sn2.startup_time,sn3.startup_time,(newwrites.value-oldwrites.value),newwrites.value))) total
from

   dba_hist_sysstat oldreads,
   dba_hist_sysstat newreads,
   dba_hist_sysstat oldwrites,
   dba_hist_sysstat newwrites,
   dba_hist_snapshot   sn,
   dba_hist_snapshot   sn2,
   dba_hist_snapshot   sn3

where sn.instance_number=dbms_utility.current_instance

   and sn.instance_number=sn2.instance_number    and sn2.instance_number=sn3.instance_number

   and oldreads.instance_number=sn3.instance_number
   and newreads.instance_number=oldreads.instance_number
   and oldreads.instance_number=oldwrites.instance_number

   and oldwrites.instance_number=newwrites.instance_number    and newreads.snap_id=sn.snap_id
   and newwrites.snap_id=newreads.snap_id    and sn.instance_number=oldreads.instance_number    and oldreads.instance_number=newreads.instance_number    and sn.instance_number=oldwrites.instance_number    and oldwrites.instance_number=newwrites.instance_number    and oldreads.snap_id = (select max(sn.snap_id) from dba_hist_snapshot sn where sn.snap_id<newreads.snap_id and
 sn.instance_number=newreads.instance_number and newreads.instance_number=oldreads.instance_number)

   and oldreads.snap_id=sn2.snap_id
   and newreads.snap_id=sn3.snap_id
   and oldwrites.snap_id = (select max(sn.snap_id) from dba_hist_snapshot sn where sn.snap_id<newwrites.snap_id and
 sn.instance_number=newwrites.instance_number and newwrites.instance_number=oldwrites.instance_number)

   and oldreads.stat_name = 'physical reads'    and newreads.stat_name = 'physical reads'    and oldwrites.stat_name = 'physical writes'    and newwrites.stat_name = 'physical writes' group by to_char(sn.end_interval_time,'yyyymmddhh24') order by to_char(sn.end_interval_time,'yyyymmddhh24') ;

On Sat, Jun 11, 2011 at 8:39 AM, Zhang Leyi (Kamus) <kamusis_at_gmail.com>wrote:

> If Karl Arao has seen this post, I'm sure he will recommend you to check
> his blog article: Workload characterization using DBA_HIST tables and kSar
> You can find it in:
> http://karlarao.wordpress.com/2010/01/31/workload-characterization-using-dba_hist-tables-and-ksar/
>
> And also, he has written a couple of scripts to determine the system
> capacity which you can find in his shared Google Docs:
>
> http://docs.google.com/leaf?id=0B5H46jS7ZPdJMDEyMmYyYjEtZDA2MC00NWRlLWIzMWYtMWQyZDlmYTA5YWM1&hl=en
>
> --
> Zhang Leyi (Kamus) <kamusis_at_gmail.com>
>
> Visit my blog for more : http://www.dbform.com
> Join ACOUG: http://www.acoug.org
>
>
>
> On Jun 11, 2011, at 3:24 PM, Ethan Post wrote:
>
> > If Oracle is the only major software running on the server you should be
> able to get this from just grabbing iostat and vmstat data. Oracle has a
> free tool called OSWatcher (Google it) which I run on most of my servers.
> >
> > I would confirm the iostat data with data from gv$filestats and some of
> the values from gv$system_statistics for things like physical writes, reads,
> amount of redo generated to make sure they fall in line as I don't always
> trust the accounting I see in iostat.
> >
> > If you have OEM/Grid Control installed you should be able to see all this
> stuff without homegrown scripts.
> >
> > You could also run some ash reports during peak hours and see what that
> tells you. Look for ash* files in $ORACLE_HOME/rdbms/admin directory.
> >
> >
> > On Fri, Jun 10, 2011 at 3:26 PM, Vamshi Damidi <dbaprimatics_at_gmail.com>
> wrote:
> > Hi All,
> >
> > We are currently on DAS file system and would like to move to SAN. and
> with New servers
> > and we need to calculate the demand for IO and CPU so that we can design
> the disk architecture and buy the server accordingly.
> > Please let me know if you need any more information.
> >
> > Any help would be greatfull.
> >
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

--
http://www.freelists.org/webpage/oracle-l

Received on Sat Jun 11 2011 - 16:01:23 CDT