Re: sequential read on full-table scan?

From: Martic Zoran <zoran_martic_at_yahoo.com>
Date: Fri, 20 May 2005 23:18:23 -0700 (PDT)
Message-ID: <20050521061823.47671.qmail@web52603.mail.yahoo.com>

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_at_centrexcc.com> 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

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

Received on Sat May 21 2005 - 02:23:55 CDT