Re: sequential read on full-table scan?
- From: Martic Zoran <zoran_martic@xxxxxxxxx>
- To: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- Date: Fri, 20 May 2005 23:18:23 -0700 (PDT)
Thanks a lot Wolfgang,
That is very nice explanation.
I have read your great white papers already.
And of course forget the effective mbrc again even I
know it exists :)
I hope very soon the majority of Oracle DBA community
will know well why system statistics are important.
Many DBAs never heard about these statistics yet.
The main question is how good is that prediction based
on the global system statistics only.
Not sure how these statistics if collected per file or
object level will be more useful???
Also, with better caching it looks that the MBR speed
is going to be more near SBR time, or even for
somebody MBR < SBR (I assume this is mainly due to
other caches discrepancies and alogrithms how are they
doing MBR with caching).
But you are correct that Oracle is using system
statistics averages to predict the cost of FTS.
Of course without system statistics FTS is going to be
very cheap and it is not good.
As you said in one of your papers, this is the biggest
reason somebody will opt to use optimizer_index_*
parameters to reduce this cost.
Thanks again Wolfgang.
Regards,
Zoran Martic
--- Wolfgang Breitling <breitliw@xxxxxxxxxxxxx> wrote:
> 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
__________________________________
Yahoo! Mail Mobile
Take Yahoo! Mail with you! Check email on your mobile phone.
http://mobile.yahoo.com/learn/mail
--
//www.freelists.org/webpage/oracle-l
Other related posts:
