Re: Does anybody really use Oracle 8i on Win2k?

Niall Litchfield wrote:
>
> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message
> news:anjrr8$fsi$1$8300dec7_at_news.demon.co.uk...
> > The buffer cache hit ratio means:
> >
> > This is proportion of block accesses that
> > appeared to have been satisfied in memory
> > based on the counts that Oracle gave you,
> > but those numbers could have very little
> > relationship to what actually happened and
> > could easily be out by a factor of two.
>
> I wasn't aware of this. Is there a simple demonstration for this?
>
> > you've also forgotten to factor in some of the
> > Stats that Oracle did give you but usually
> > don't get referenced in the standard formula.
>
> I omitted this discussion on the basis that even if you do get the 'right'
> measure for hit ratio it's still not a good thing to be measuring. (Except
> for those of us with perfectly tuned apps).
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
>
> ******************************************

Throw in a select from v$systat, and you see a bucketload more types of reads that just: phyiscal, current, consistent. Very few hit ratio formulas take that into account. And even if we had some totally precise mechanism in Oracle for working out the buffer cache hit ratio, it will always involve the concept of "physical read".

In my humble experience, I've seen about 5% databases on raw, 15% on direct IO and 80% on standard plain old file systems. (I'm excluding Windows here which is always direct io). So to coin a "worst" case scenario

For a block read X:

Oracle thinks its a physical read,
The file system thinks its a physical read, The OS thinks it a physical read,
The hardware cache thinks its a physical read, But as it happens, the data is in a track buffer, a facility offered by the disk drive itself...

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."