I’ve had a lot of folks ask me about this parameter, I have received a recommendation from a fellow analyst that with 10g it is better not to set the parameter db_file_multiblock_read_count and let oracle determine the best value for the number of blocks read in multi-block I/O operations.

We have not set this parameter on our 4 node (RAC) CRM data warehouse and we find that the value of db_file_multiblock_readcount is different in the 4 instances e.g. 22, 45, 46 and 42 respectively.

I am arguing that based on the above numbers the cost of full table scan will vary from one instance to another and hence the optimizer can choose a different execution plan (good or bad) for the same query when executed in different instances.

I am confused so I wanted your opinion about setting (hardcoding or dynamic determination) the db_file_multi_block_read_count in a RAC environment.

and we said…
Your conclusion is correct – that due to the different settings – you might see different plans.

But – to that – I say “so”. The fact that you have different values would indicate to me that you have different workloads on your nodes. This value is determined by the database based on your historical actual multi-block read count.

You see – when you set this manually (say to 64), you are telling Oracle “hey, you will read 64 blocks at a time, that is pretty good”. But in reality, you don’t really ever read 64 (on three of your nodes, you read about 40-45, on the other only about 22) when you do a multiblock IO – you do less than 64. So, while the query was costed with a read count of 64, it is really only doing 22 – which is not nearly as efficient as 64 (but it cannot do 64 – it can only do 22 – some of the 64 blocks you would have read are already in the buffer cache, we cannot read them from disk again – so the big read never actually happens)

I recommend letting everything that you can default.