value of db_block_buffers
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: value of db_block_buffers

  1. #1
    Join Date
    Dec 2001
    Posts
    141
    Hi everybody !

    What do you think about db_block_buffers=2048 in a DSS database with db_block_size=8196 and 30Gb of total database size ?

    The default value proposed by Oracle for db_block_buffers in the sample init.ora file, is 100-550-3200 for small-medium-large database.

    I saw db_block_buffers=100000 (db_block_size=8196) in a OLTP database which size is 10Gb.

    I'm confused ...

    Can I rely the Oracle's default value ?

    Thanks a lot in advance ...

  2. #2
    Join Date
    Mar 2001
    Posts
    144
    No you can't.

    Seems to me those values in the sample init.ora were generated in a test system with little activity (just a guess but that is what it looks like to me). They are definitely too small. I tried using those numbers once before - just once - and I got a lot of swapping activity happening.

    I have found that usually in a production environment the numbers are usually much, much higher.

    For example, I have a 12 gig db right now and the 131072 because a number of my tables have inline LOB's.

    On another system, a 3 gig db has 25000.

    If you are unsure about the numbers set it the buffers high initially then monitor the SGA to see if you have a lot of free mem available. If you do then resize the buffers down a bit. Do this iterative approach bit by bit until you have about 5% free mem available (the number is not a hard and fast rule - some will recommend a slightly lower percentage and some higher). You do not want to give too much too Oracle because then you may not have enough for the OS to use. So it's fine line there and requires some effort and time.

    Mind you this is one approach and others may have some other ways to do this.

    HOH

    [Edited by ramaral on 08-29-2002 at 09:58 AM]

  3. #3
    Join Date
    Dec 2001
    Posts
    141

    Wink

    Thanks a lot !

    Anyone else have any ideas about this subject ?

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by ramaral
    Seems to me those values in the sample init.ora were generated in a test system with little activity (just a guess but that is what it looks like to me).
    It's not only that those values might originated from a test system, it is more important that they vere derived about 10 years ago! If my memory serves me right, that kind of sample intit.ora with those values for small/medium/large database started to be distributed with the advent of Oracle7 (wasn't that about 10 years ago?) and haven't changed untill about when 8i came out!

    And if you think about what it ment "large database" 10 years ago, I belive those values were not so absurd back then. Of course, for the nowadays database those numbers seems more like an innocent joke.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    Basically I am using this kind of rule .

    Total SGA 60~70% of RAM
    DB_BLOCK_BUFFER 60~80% of SGA
    1. www.dbasupport.com
    2. www.dbforums.com
    3. www.itpub.net
    4. www.csdn.net
    5. www.umlchina.com
    6. www.tek-tips.com
    7. www.cnforyou.com
    8. fm365.federal.com.cn
    9. www.programmersheaven.com
    10.http://msdn.microsoft.com/library/default.asp
    ligang1000@hotmail.com

  6. #6
    Join Date
    Dec 2001
    Posts
    141

    Wink

    Very very good advice !
    This help me a lot !
    It's was very helpful to me ...
    Helene

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width