Home » RDBMS Server » Performance Tuning » setting db_file_multiblock_read_count
setting db_file_multiblock_read_count [message #221913] Thu, 01 March 2007 00:56 Go to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

I have an Data Warehouse environment with Oracle 9.2.

I have set the db_file_multiblock_read_count = 8

The recommended count will be from the following

max I/O chunk size
db_file_multiblock_read_count = -------------------
db_block_size

How to get the "max I/O chunk size" ??

Brian.
Re: setting db_file_multiblock_read_count [message #221915 is a reply to message #221913] Thu, 01 March 2007 00:59 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

Formula not formatted properly..

db_file_multiblock_read_count = (max I/O chunk size) / (db_block_size)

Brian.
Re: setting db_file_multiblock_read_count [message #221917 is a reply to message #221913] Thu, 01 March 2007 01:20 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Hi.

AFAIK (max I/O chunk size) is OS-dependant and is 64K for Unix systems.
However IMHO Oracle does NOT consider it and just uses db_file_multiblock_read_count number during full tabl;e scans (the greater that number is - the cheaper FTS cost), so
assigning large value for it may cause your system to abandon index accesses.

HTH.
Michael
Re: setting db_file_multiblock_read_count [message #221963 is a reply to message #221917] Thu, 01 March 2007 05:46 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> assigning large value for it may cause your system to abandon index accesses.
Very true. Well said.
Re: setting db_file_multiblock_read_count [message #222003 is a reply to message #221913] Thu, 01 March 2007 07:48 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Tom Kyte in his Effective book (possibly also his new one, I don't know) has a nice walkthrough of this parameter and how to determine how to set it. Especially if you are running 9iR2 in production, it is a good book to have, even well beyond this one issue.
Previous Topic: Dynamic Table List Selection for STATS
Next Topic: Row Chaining Issue
Goto Forum:
  


Current Time: Sun Dec 11 08:29:37 CST 2016

Total time taken to generate the page: 0.06609 seconds