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

>-----Original Message-----
>From: oracle-l-bounce_at_freelists.org
>[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Christian Antognini
>Sent: Tuesday, December 07, 2004 2:18 AM
>To: ryan_gaffuri_at_comcast.net
>Cc: Oracle-L_at_freelists.org
>Subject: RE: db_file_multiblock_read_count and performance
>
>Hi Ryan
>>I have been testing this extensively over the last few months. I do a
>full table scan with a
>>db_file_multiblock_read_count =3D 1 and then one =3D 128( i check the =
10046
>trace to verify i am
>>getting this much) and I see absolutely no difference whatsoever in
>response time.
>
>Attached you find some results that I get during some tests that I
>performed on different servers by different customers (notice that I =
had
>no influence on the setup, I just run a test script...).
>
>As you can see many different behaviors are to be expected.
>
>System 1: higher values are better, of course they are "technical"
>limits... (notice that 55MB/s is the maximum throughput measured on =
this
>system, i.e. with DFMRC=3D32).
>
>System 2: values higher than 16 give bad performance, i.e. the optimal
>value is 16.
>
>System 3: values less than 17 are useless, i.e. at least 17 should be
>used to have "correct" performance.
>
>System 4: no performance difference was measured. Notice that this
>system, with 230MB/s, is also the faster I tested...
>
>
>HTH
>Chris
>
>
>
>
>--
>http://www.freelists.org/webpage/oracle-l

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