RE: db_file_multiblock_read_count causing full scans to takelonger?

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

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