Re: Re: Automatic tuning of db_file_multiblock_read_count ???



It looks to me like the manual is wrong.
Quote:
   "the optimizer uses the value of mbrc when performing full table scans"
end quote

Very unlikely - the optimizer doesn't perform tablescans, it
makes a decision based on predicted resource consumption
and tells the run-time engine to perform the tablescan. The
run-time engine will try to do that tablescan as quickly as
possible if told to do a tablescan. This should probably say
"the optimizer uses the value of mbrc when estimating the cost of full table scans"


Quote
   "However, the optimizer uses mbrc=8 for costing"
end quote

   See above - the optimizer only ever does costing, so how
   can it have to deal with two different values, and why a
   completely arbitrary 8 ?

Quote
   The "real" mbrc is actually somewhere in between
end quote
   No - the 'real' mbrc is what Oracle has captured
   as the MBRC.  The explanation about buffered
   blocks etc. is a good explanation of why it would
   be bad to use the value of db_file_multiblock_read_count,
   and why the stats gathering records a 'real' (although
   'typical', or 'average' might be a better word) size
   for the achievable multiblock read counts.


quote
"The mbrc value gathered as part of workload statistics is thus useful for FTS estimation".
end quote
True - so why tell us that
a) Oracle uses it at RUNTIME, when its used for estimation
b) Oracle uses 8 instead of the MBRC




NOTE - when running 10.1, if you don't collect system statistics,
and the optimizer is using the 'noworkload' statistics as a consequence,
then it uses your setting of db_file_multiblock_read_count as the
MBRC in the calculation of the cost of a tablescan.


Regards

Jonathan Lewis

Now waiting on the publishers:    Cost Based Oracle - Volume 1

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 8th July 2005







----- Original Message ----- From: "Peter Alteheld" <palteheld@xxxxxxxx>
To: "Jared Still" <jkstill@xxxxxxxxx>
Cc: <Oracle-L@xxxxxxxxxxxxx>
Sent: Thursday, July 14, 2005 8:00 AM
Subject: Re: Re: Automatic tuning of db_file_multiblock_read_count ???



Jared,

it seems the theme I was looking for isn't yet published on that site - it might be in the upcoming Part 3.

But I have found some stuff in the docs, Perf. Tuning Guide, 14.4:

"In release 10.2, the optimizer uses the value of mbrc when performing full table scans (FTS). The value of db_file_multiblock_read_count is set to the maximum allowed by the operating system by default. However, the optimizer uses mbrc=8 for costing. The "real" mbrc is actually somewhere in between since serial multiblock read requests are processed by the buffer cache and split in two or more requests if some blocks are already pinned in the buffer cache, or when the segment size is smaller than the read size. The mbrc value gathered as part of workload statistics is thus useful for FTS estimation."

Peter




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

Other related posts: