Re: Re: Automatic tuning of db_file_multiblock_read_count ???
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <Oracle-L@xxxxxxxxxxxxx>
- Date: Tue, 19 Jul 2005 09:28:28 +0100
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
