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

1. Oracle uses it at RUNTIME, when its used for estimation
2. 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_at_yahoo.de> To: "Jared Still" <jkstill_at_gmail.com> Cc: <Oracle-L_at_freelists.org> 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