Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: IO waits in SAP/Oracle

Re: IO waits in SAP/Oracle

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 9 Jul 1999 15:07:16 +0100
Message-ID: <931530316.28898.0.nnrp-13.9e984b29@news.demon.co.uk>

It is a common misconception that the buffer cache hit ratio is an automatic guide to performance. It is only a very rough rule ot thumb that 95%+ indicates a sound system.

Two points to consider -
a) it is easy to write a CPU intensive piece of SQL that

    requires millions of logical I/Os on a very small table;     such a piece of SQL will inevitably make the hit ratio     look good.

b) If you need to do 100,000,000 logical I/Os per hour,

    a 99% hit ratio means 1,000,000 physical I/Os per     hour, which means 278 I/Os per second - unless     your I/O is spread very evenly over 15 to 20 discs     then you will see a lot of I/O contention (in the     form of queuing) even at that high a hit ratio.

And that hasn't touched on the effect of logs being written, and online logs being read for copying !

A hint that may help to identify the problem - try looking at v$filestat and v$system_event for average file io access times - anything worse that 20 milliseconds is a threat. Unfortunately these times can be meaningless if you have multiple IO slaves, or async IO enabled.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

thuge wrote in message <7ko1l5$894$1_at_nnrp1.deja.com>...
>Hello,
>
>I have a "philosophical" problem about my SAP/Oracle/AIX database :
>I'm quite sure i have some IO waits problem :
>- sar + vmstat give me about 50% of the time is spent in IO wait
>- iostat : mainly the most accessed disks are those which contains the
>Oracle data files.
>- Precise/SQL, SQL tuning tool, tells me that 95% of the SQL statements
>spent their time in IO wait
>
>BUT the Oracle Buffer Cache Hit Ratio is always between 95% and 98%,
>that is to say Oracle finds more than 95% of the data in its buffer then
>there's no disk access. Am i wrong ?
Received on Fri Jul 09 1999 - 09:07:16 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US