RE: db_file_multiblock_read_count 10g default values
Date: Fri, 6 Nov 2009 09:57:04 -0700
Hi Chris, yes it's helpful - at least nice to get some confirmation of my findings. You may have seen from one of my later posts, the formula I eventually came up with was very close to yours:
Notice the 5% reduction and I also found there appears to be a limit of .28% of db_cache_size in addition to the limit of 1048576/db_block_size (which I think is actually max_io_size/db_block_size, so it could vary on different platforms), so I guess it could be shown more completely like this:
This was with 10.2.0.4 running on Oracle Linux 5.2, so it may be a little different from when you did your tests back in 2005.
I also agree this method of "auto-tuning" isn't sucha a good idea since if you set your db_cache_size/sga_target too low or your sessions too high, then you may be inadvertently throttling down your multiblock reads more than you should. I'm thinking in my specific situation I'll gather system_stats to set mbrc for optimization and then set db_file_multiblock_read_count=128 manually instead.
From: Christian Antognini [mailto:Christian.Antognini_at_trivadis.com]
My conclusion, which was also published in my book (page 178), was the following:
dfmbrc = min(1048576/db_block_size,
BTW, the dependency between db_cache_size and dfmbrc is, IMO, really bad...
Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.
http://www.freelists.org/webpage/oracle-l Received on Fri Nov 06 2009 - 10:57:04 CST