Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> 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
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
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-lReceived on Thu Jul 14 2005 - 03:30:34 CDT
![]() |
![]() |