Re: db file multiblock read count

And there I was trying to avoid getting into the mire of how all the variations of arithmetic could work.

Just to throw in a (potentially) important point, the default setting of db_file_multiblock_read_count (hence _db_file_exec_read_count) is dictated by the db_cache_size(s) and the sessions parameter (which is dictated by the processes parameter).

Approximately: db_file_multiblock_read_count = db_cache_size / sessions (but you have to convert to blocks, and mess around if you have set up multiple block sizes) with a maximum (in general) of 128 because so many people have linux systems with 8KB block sizes. Then the "gather_system_stats('Exadata')" option copies this value into MBRC. Most people have very large values for db_cache_size (or sga_target) and end up with 128 as the default - but some people set systems up with massively oversized processes (which goes up by ca. 50% to get sessions) leading to smaller values appearing as the default db_file_multiblock_read_count.

Regards
Jonathan Lewis

On Sun, 17 Jan 2021 at 11:36, Neil Chandler <neil_chandler_at_hotmail.com> wrote:

> I agree with all the previous replies - and generally you should leave it
> alone - but it's worth understanding that setting it explicitly to 128 (8K
> block size default) is not the same as leaving it to default. Changing it
> could affect EVERY SQL statement in the system.
>
> Multi-block reads are (if left to default) controlled by 2 hidden
> parameters:
>
> _db_file_optimizer_read_count for costing (8)
>
> _db_file_exec_read_count for execution (128 assuming 8k block
> size, 64 for a 16k block size. Anything else is probably the wrong block
> size.)
>
> Explicitly setting the MBRC will override the optimizer cost parameter "_
> db_file_optimizer_read_count", which is 8 by default.
>
> This will have an effect on your system statistics (assuming you've left
> them to default***), and therefore an effect on your execution plans.
>
>
<http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail> Virus-free.
www.avg.com
<http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail> <#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

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