RE: db_file_multiblock_read_count and performance - Bayesian Filter detected spam

Putting multiple sessions and optimizer choices aside I would like to know exactly why (assume 128 is max) 128 faster than 1 but not faster than value < 128. Perhaps understanding the reasons for this are not exactly clear with the complexities of the IO subsystem. I agree the best method is likely to run some tests to see what is the faster method to access a row given a full scan then balance that with the # of users and influence things will have on the optimizer. I just don't want to be one of these people who think the sky is falling every time there is a full scan. I can't tell you how many people come to me demanding I somehow magically remove all full scans from some sort of query plan because "all full scans are bad".

-----Original Message-----
From: Christian Antognini [mailto:Christian.Antognini_at_trivadis.com]=20 Sent: Wednesday, December 08, 2004 1:35 AM To: Post, Ethan
Cc: Oracle-L_at_freelists.org; ryan_gaffuri_at_comcast.net Subject: RE: db_file_multiblock_read_count and performance - Bayesian Filter detected spam

Hi=20

(Sorry for the delay... but I'm offline by a customer...)

>This kind of brings up an interesting thought. There is the script on
>Ixora to test the largest MBR size and then you are suppose to set the
>value to that,

Setting it to the largest value is not good on all I/O sub-systems and sometimes very poor for the optimizer. Usually a value that gives good performance (let's say 90-95% of the maximum) lead to much better execution plans. Notice that if system statistics are used, then they automatically "compensate" large values with much better one (e.g. on a real system it happens not very often that you can read 50-60 contiguous blocks with a FTS...).

>maybe it would be a better practice to generate a huge
>table, run tests at different sizes then set.

For my tests I took a table of at least 1GB (on small systems) or 10GB (on big systems), i.e. I just reused a table with real data.

>In theory the largest
>size possible would be fastest but it would be interesting to find out
>if this always was true.

This is not true! You should really do some tests!!!!

Chris

--
http://www.freelists.org/webpage/oracle-l