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

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:3ff14ead$0$13347$ed9e5944_at_reading.news.pipex.net...

> That is true, but how much of that working set is sitting in the SGA anyway?

Hopefully all of it! :D

Seriously: this is where things like FTS can get a system unstuck. If you rely on timings obtained doing a FTS that hit the hardware cache but not the Oracle cache and you thought it was OK to do the FTS, then you run it on a system with a slightly different working set and it doesn't hit ANY cache and runtime blows out, who do you blame then and where do you start tuning?

> How many cache's does one need in a system?

Tell that to the disk farm brigade! Yet, it is a fact: modern systems have caches EVERYWHERE. CPU, disk controllers, I/O subsystems, network hardware, virtual machine engines, you name it.

Yet, Oracle insists on getting everyone to use its own. That's where the logical versus physical I/O speed and timing conundrum comes in: exactly what constitutes a logical or a physical I/O nowadays? Does it even make sense to STILL use those terms?

I don't think so. To me, there are two types of I/O in a system: cached and un-cached. Who caches it is for performance evaluation purposes essentially irrelevant. All else I'm afraid is just nuances, "versionitis" and "feature-itis".

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