Re: Buffer cache statistics (ratios) and CBO SQL optimization?

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:bssbr2$mq0$1$8300dec7_at_news.demon.co.uk...

> I've been trying to avoid the whole buffer cache
> argument, but had to comment below.

Jeez, was it THAT bad? I do recall a lot worse being said... ;)

> Only with regard to cache that is in the same box
> as the SGA - not for cache lurking at the end of
> bits of wet string.

Yet, it's there. And effective in those wet strings. And its effect is essentially hidden from Oracle's internal timers and counters. Which may tend to inflate/deflate a lot of otherwise useful info. That is one major problem I see with these "hidden" caches. Not their usefulness or otherwise, but their potential to bring havoc to performance timers and counters.

> I disagree - if Oracle needs to read a block in the
> file-system cache then the session has to set up
> a callback, block a buffer header, go into its waitstate,
> and go off the run queue; on being awoken, it has to
> clear the wait state and change the state of the
> buffer header before continuing. In the meantime
> other processes could have been queueing on
> the exclusive lock on the buffer header - also
> going into waitstates and setting callbacks

Yet note: it does all that because it refuses to acknowledge (or "know") that the data to be read is probably in a hardware cache already. A bit more co-operation between Oracle and the hardware might be warranted in helping it reduce all that jazz for what is a simple hardware cache access.

Quite frankly, I don't feel Oracle's attitude in ignoring most I/O hardware nowadays HAS a cache is a great advantage...

> If the block had been in the Oracle cache, all
> the processes could have acquired shared pins
> on it without waiting - reducing task switches,
> extra CPU costs and so on.

Sure. But the difference in timings is not that important WITH average loads. Not when taken in isolation, which is what happens when people are measuring these things in dedicated systems.

What you said is of course important when issues such as scalability jump in: an SGA cache access will ALWAYS scale better than one from a hardware cache, in a system under stress.

But in a non-saturated system, it's gonna be mighty difficult to establish how much faster (or lighter) the Oracle cache access is on a single access. Or even a few hundred. A few thousand may spring the trap, but that's about it. And that's assuming ideal conditions.

How many people do you know that monitor BOTH the OS and the Oracle counters when looking at these things? Precious few. And the stuff in "tuning" books we all know and love glosses over these issues. I'm being very generous with the word "gloss"...

The potential is always there for these "hidden" caches to falsify performance numbers. I'm reminded of an EMC 4Gb cache that "insisted" on cutting off at about 250Mb of writes on an ADD DATAFILE. Until we found out about EMC's cache partitioning and its effects. But try and convince the disk farm manager it wasn't Oracle's fault or limitation that caused the system to go "clunk" on that size? I wish you'd been there...

-- 
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam