Extremes
Last week I got to look at a version 9 Statspack Report from a database where the buffer cache was set to 32M. That’s right, 32M. I don’t think you can buy a thumb drive that small. They were using 16K blocks too, so what’s that:
(32*1024*1024)/(16*1024) = 2,048 blocks
Not very many blocks for that database. Needless to say it wasn’t working very well for the multi-hour batch job they were trying to run.
Then over the weekend someone sent an AWR report from a version 10 database and it had a 60G buffer cache. That’s the biggest one I’ve ever seen!
So it was a pretty interesting week (from a geeky technical standpoint anyway)!
It reminded me of Muggsy Bogues guarding Shawn Bradley, or Manute Bol, or MJ, or just about anybody.
Anyone seen a bigger (or smaller) buffer cache recently?
I’d love to hear about it.
>> it had a 60G buffer cache
My God, a 60 GB buffer cache (the bigger the better 😉 )…..
This is the biggest cache size I have ever heard of.
[…] di Cristian Cudizio Stamattina, leggendo l’ultimo post di Kerry Osborne intitolato “Extremes“, mi è venuto in mente un bizzarro comportamento segnalatomi da un programmatore su un […]
60 Gb…bah!
Wow – 100G buffer cache! Awsome!
What’s your buffer hit ratio? ;.)
Seriously though, any issues caused just by having it that big?
We had to turn off db_cache_advice. Startup and shutdown takes a REALLY long time. We appear to have an increase in CBC latch waits since going to 10g and doubling the cache… It used to be around 50 GB, and I don’t recall the keep pool being anywhere nearly that large.
The hit ratio is 90.7, so the cache is obviously too small 😀
Ha! That’s funny. The 60G DB is spending about 25% of it’s time on i/o – (so I guess it needs a bigger cache as well). Actually it has a very small keep pool (relative to the overall buffer cache size) and it looks like they’ve got some objects assigned that won’t fit, so that’s what’s causing all the i/o.
[…] Osborne was playing with numbers too, in his own way—pointing out some extremes in buffer cache sizes, and asking for his readers’ […]
In descending order of size:
120GB, 80GB, 64MB.
The 64MB was a few years ago. The machine had 8GB of RAM, but they weren’t an Oracle site and didn’t have a DBA.
Remember how Oracle used to ship an init.ora file with things like:
# db_block_buffers = 8000 # big
# db_block_buffers = 4000 # medium
# db_block_buffers = 2000 # small.
Well they read the file and decided that their database was big, so …
Yeah, I remember the big,mediuum,small settings, back before there was even an spfile. It’s funny how some of those things get carried along, like an 11g database with _spin_count set (carried forward for 10 years and no one knows why anymore).
120G is impressive. Did it work well?
160 MB for Oracle e-business suite database in production 🙂
The implementer of EBS did not send any dba to that site, so they found a place in the docs that states 160 MB as a minimum and used the number as an recomendation. They called me 1 month after the database went in production, to spped the things up. At that time they had 8 GB of (mainly free) physical memory and have placed an order for 8 more, hoping that adding more memory will help.
Well, it was a problem with too little memory, indeed 🙂
Dallas,
I’m curious–what statistics did you use to determine that 50G of keep is better than 0?
Mike
[…] actually built some databases with 32M buffer caches (the one I mentioned in this previous post on Buffer Cache Extremes was one of his). He went so far as to say that he thought that developers should be using […]
New record! Last week I just saw a 9.2 database with a 24M buffer cache.
Kerry
10.1 data warehouse on a 24GB RAM machine – 16 MB of buffer cache.
This was 10.1.0.3 I think with SGA_TARGET set to 8GB. However the shared pool kept growing at the expense of buffer cache due some bug and eventually used almost all of SGA_TARGET, leaving only 1 x 16MB granule for buffer cache – and the instance crashed a while later.
Well the 24MB 9.2 database at least stayed up. But still, 16MB is the new record!
By the way, I really don’t like AMM (I think it is an acronym for “Automatic Memory Messer-upper”). Even in 11g, it still seems to way over-allocate the shared pool on top of the occasional flurries of re-allocations. Seems like a feature that has lot’s of problems but doesn’t provide much benefit to most systems.
Kerry
I just saw a report of testing a 540G SGA by the Solaris Internals guys. No real data (they didn’t say how much was buffer cache vs. shared pool) but here’s a link anyway.
http://www.solarisinternals.com/wiki/index.php/Application_Specific_Tuning
Kerry