Re: sequential read on full-table scan?

Yes, it is much more costly than a single multiblock read with a high MBRC. If you collected system statistics during such a time, the gathered statistics will reflect the low actually achieved MBRC count as opposed to the db_file_multiblock_read_count init ora setting. With that, the CBO will calculate a higher cost for a full scan.

e.g. for a table of 1024 blocks

with a dfmrc of 16: cost of FTS = 1152/16 = 72 ( + 1 for _table_scan_cost_plus_one = true) = 73
with a real mbrc of 3: cost of FTS = 1152/3 = 384 ( + 1 for _table_scan_cost_plus_one = true) = 385

Both cost values would still be multiplied by the mreadtm/sreadtm ratio for the final FTS cost

At 11:23 PM 5/20/2005, Martic Zoran wrote:
>I am confident that this is probably much more costly
>then doing one MBR.
>It is the interesting fact how Oracle data cache may
>ruin performances sometimes.
>We are not aware of this fact many times.
>
>I believe that this is showing how important is to
>collect system statistics to reveal to Oracle
>optimizer the real speed of MBR.
>The only problem is that Oracle is not predicting in
>his cost how many MBR/SBR operation are going to
>happen instead of 1 MBR? Is this true?

Regards

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com

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