RE: db_file_multiblock_read_count causing full scans to takelonger?
- From: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>
- To: <Rich.Jesse@xxxxxx>, "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
- Date: Thu, 21 Dec 2006 08:45:10 +0100
Hi Rich, Brandon
A couple of comments from my part...
> Well, almost. Here's what I get on 10.2.0.2.0 on AIX 5.3:
>
> SELECT LEAST(dcs/(ses*dbs), 1048576/dbs) "CALC", dfmbrc
> FROM
> (
> SELECT a.value "DCS", b.value "SES", c.value "DBS", d.value
> "DFMBRC"
> FROM v$parameter a, v$parameter b, v$parameter c, v$parameter d
> WHERE a.name = 'db_cache_size'
> AND b.name = 'sessions'
> AND c.name = 'db_block_size'
> AND d.name = 'db_file_multiblock_read_count'
> );
>
> CALC DFMBRC
> ---------- -------
> 68.2666667 66
Unfortunately I didn't add a "disclaimer" when I posted that formula.
Therefore let me explain from where it comes... While testing 10gR2 in
summer 2005 on one database where the "automatic tuning feature" of
db_file_multiblock_read_count was active I notice that the value of the
parameter changed every time the instance was bounced. Thus I started
investigating... The result of that investigation is the formula that I
posted. I'm aware that it is not very precise, but little percent error
is acceptable. The point is that I want to understand what's going on...
and, therefore, IMHO it is much better than the sentence you find in the
documentation, i.e. "if the number of sessions is extremely large the
multiblock read count value is decreased".
> Also, I'm not clear on whether it uses the value of the SESSIONS
> parameter, or rather a dynamic count, e.g. "select count(*) from
> v$session", in order to adjust the value of dbfmbrc dynamically.
It is the value of the parameter SESSIONS.
> It's been awhile since I've messed with DB_FILE_MULTIBLOCK_READ_COUNT,
> but I didn't think it was dynamically changed during the life of the
> instance, just at instance startup.
Yeap. This matches my observations as well.
> I was worried that the 128 value would lead to more FTSs
I checked this point carefully (I believe to documentation only to some
extents ;-).
If CPU costing is used the query optimizer uses the value of MBRC (from
system stats) for its estimations.
If I/O costing is used the query optimizer uses a value of 8 for its
estimations.
Best regards,
Chris
--
//www.freelists.org/webpage/oracle-l
Other related posts:
