Home » RDBMS Server » Server Administration » How to calculate db_block_buffer?
How to calculate db_block_buffer? [message #270740] Thu, 27 September 2007 22:48 Go to next message
trantuananh24hg
Messages: 668
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 #270792 is a reply to message #270740] Fri, 28 September 2007 01:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is a complete nonsense.

Regards
Michel
Re: How to calculate db_block_buffer? [message #270796 is a reply to message #270740] Fri, 28 September 2007 01:10 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>I want to know about the db_block_buffer size exactly.
Either you already know the exact answer & therefore don't need answers or you don't know the answer & can't differentiate between right & wrong answers.

The answer is 42!
Re: How to calculate db_block_buffer? [message #271064 is a reply to message #270740] Fri, 28 September 2007 21:47 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
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
(db_block_buffer/4). 


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 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
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 #271087 is a reply to message #271083] Sat, 29 September 2007 02:16 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Oh, thank you very much, Michael!

It's my mistake.

Thanks again!
icon3.gif  Re: How to calculate db_block_buffer? [message #271126 is a reply to message #271087] Sat, 29 September 2007 13:40 Go to previous message
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

Previous Topic: 2 Oracle installations (merged)
Next Topic: Test Environment for oracle
Goto Forum:
  


Current Time: Wed Dec 07 18:55:21 CST 2016

Total time taken to generate the page: 0.18430 seconds