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.

17 Comments

  1. Asif Momen says:

    >> 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.

  2. [...] 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 [...]

  3. Dallas says:

    60 Gb…bah!

    SQL> select name,value from v$parameter
      2  where name = 'db_cache_size'
      3  /
    
    NAME                  VALUE
    --------------------- ------------------------------
    db_cache_size         107,374,182,400
    
    
    SQL> show sga
    
    Total System Global Area 1.6390E+11 bytes
    Fixed Size                   966888 bytes
    Variable Size            2818572288 bytes
    Database Buffers         1.6108E+11 bytes
    Redo Buffers                4513792 bytes
    
    SQL> l
      1  select name,value from v$parameter
      2* where name like '%cache_size'
    SQL> /
    
    NAME                   VALUE
    ---------------------  ------------------
    db_keep_cache_size         53,687,091,200
    db_recycle_cache_size          16,777,216
    db_2k_cache_size                        0
    db_4k_cache_size                        0
    db_8k_cache_size                        0
    db_16k_cache_size                       0
    db_32k_cache_size                       0
    db_cache_size             107,374,182,400
    
    8 rows selected.
    
  4. osborne says:

    Wow – 100G buffer cache! Awsome!

    What’s your buffer hit ratio? ;.)

    Seriously though, any issues caused just by having it that big?

  5. Dallas says:

    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.

  6. Dallas says:

    The hit ratio is 90.7, so the cache is obviously too small :-D

  7. osborne says:

    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.

  8. [...] Osborne was playing with numbers too, in his own way—pointing out some extremes in buffer cache sizes, and asking for his readers’ [...]

  9. 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 …

  10. osborne says:

    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?

  11. Yavor says:

    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 :)

  12. Mike says:

    Dallas,

    I’m curious–what statistics did you use to determine that 50G of keep is better than 0?

    Mike

  13. [...] 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 [...]

  14. osborne says:

    New record! Last week I just saw a 9.2 database with a 24M buffer cache.

    Kerry

  15. Tanel Poder says:

    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.

  16. osborne says:

    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

  17. osborne says:

    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

Leave a Reply