Re: How to caliculate the IO Demand for IOPS and ThroughtPut
- From: Andrew Kerber <andrew.kerber@xxxxxxxxx>
- To: kamusis@xxxxxxxxx
- Date: Sat, 11 Jun 2011 16:01:23 -0500
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@xxxxxxxxx>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@xxxxxxxxx>
>
> 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@xxxxxxxxx>
> 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.
> >
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
Andrew W. Kerber
'If at first you dont succeed, dont take up skydiving.'
Other related posts:
