| How to calculate db_block_buffer? [message #270740] |
Thu, 27 September 2007 22:48  |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Dear all!
My Database is 10.2.0.1, and the parameter db_cache_advice is set to be ON, so that, the db_block_buffer must be set by zero.
And, because of the parameter db_multiblock_read_count does not exceed to (db_block_buffers/4), it's the reason that I want to know about the db_block_buffer size exactly.
With my server is IBM AIX, running Windows 2003, default db_block_size is 8KB, db_multiblock_read_count is 16KB.
Thank you very much!
|
|
|
|
|
|
|
|
| Re: How to calculate db_block_buffer? [message #271064 is a reply to message #270740] |
Fri, 28 September 2007 21:47  |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Yeah!
Thank you so much, Michael and Anna!
The parameter db_block_buffer is calculated by the formula
db_block_buffer = Buffer_cache/db_block_size
And, the section that I wonder about is to follow this Note 1037322.6 in Metalink:
| Quote: |
The cost of setting db_file_multiblock_read_count too high is that the server
will consume additional memory and may cause full table scans to be chosen by
the Cost-Based Optimizer more frequently.
The maximum value of the overall multiblock read factor is calculated as:
(db_block_size * db_file_multiblock_read_count)
The value of db_file_multiblock_read_count is limited by the multiblock
read factor in the operating system level.
|
Why?
| Quote: |
Regardless of the MAX_IO_SIZE value allowed by your operating system,
db_file_multiblock_read_count cannot exceed:
(db_block_buffers/4)
|
Assuming that OS block in Windows is 4048 bytes, db_block_size and Buffer cache parameters are 8096 bytes and 469762048 bytes (448MB), the db_block_buffer =
469762048/8096 = 58720256 bytes
And, db_file_multiblock_read_count = 58720256 /4 = 14680064 bytes
But, the default parameter in Database is set to 16MB = 16777216 bytes.
It exceeds to
Do you explain to me?
Thank you very much!
[Updated on: Fri, 28 September 2007 21:49] Report message to a moderator |
|
|
|
| Re: How to calculate db_block_buffer? [message #271083 is a reply to message #271064] |
Sat, 29 September 2007 01:57 |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
You completly misread the stuff.
| Quote: | 469762048/8096 = 58720256 bytes
|
not BYTES but BLOCKS
| Quote: | the default parameter in Database is set to 16MB
|
Not 16MB but 16 BLOCKS.
In addition, DB_BLOCK_BUFFER is obsolete since 6 years, you have to use DB_CACHE_SIZE or better SGA_TARGET and no more have a look at buffer size (unless you have some issue and are able to understand this is a cache size one). Don't you see the note header:
| Quote: | RDBMS Versions: 7.x - 8.x
|
Regards
Michel
[Updated on: Sat, 29 September 2007 01:58] Report message to a moderator |
|
|
|
|
|
 Re: How to calculate db_block_buffer? [message #271126 is a reply to message #271087] |
Sat, 29 September 2007 13:40 |
OracleDisected
Messages: 25
Registered: September 2007
Location: Mexico
|
Junior Member |
|
|
You really want to miss 10gR2 features for automatic memory management?
The buffer cache advice activates the routines for cache usage statistic gathering, then after a while your DB has been working you'll be able to tune the memory assigned... manually.
On the other hand there is the Automatic Memory Management which allows you to specify the whole SGA space and Oracle adjusts sizes depending on usage for every sub-space. I recommend it...
Regards
[Edit MC: url to poster blog removed. We don't care about your blog in this post. If you have an answer on your site, post a link to this answer. If you don't, post your blog url in Marketplace]
[Updated on: Thu, 11 October 2007 12:43] by Moderator Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
