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 ...
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.
[Edited by ramaral on 08-29-2002 at 09:58 AM]
Thanks a lot !
Anyone else have any ideas about this subject ?
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!
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).
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.
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Basically I am using this kind of rule .
Total SGA 60~70% of RAM
DB_BLOCK_BUFFER 60~80% of SGA
Very very good advice !
This help me a lot !
It's was very helpful to me ...
Click Here to Expand Forum to Full Width