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.
MBRC=default = a single block cost (SBC) to multi block cost (MBC) ratio of
0.270833 (i.e. for the optimizer, 1 multiblock read = almost 4 single block
reads for costing purposes)
MBRC=128 = a SBC to MBC ratio of 0.1732, 1 multiblock read = about 6 single
blockreads. Tablescans just got more attractive to the optimizer.
MBRC=1024 = a SBC to MBC ratio of 0.1675, so tablescans are even more
attractive
***always leave your system statistics to default unless you have a dedicated
data warehouse as the only database on an exadata.
Under those circumstances there's a chance that gathering with 'exadata' might
be better for you.
As well as explicitly setting the MBRC in the system stats it also adjusts the
multiblock read time because the IOTRFSPEED is raised from 4096, making the
SBC/MBC ratio 0.022.
You get about 50 blocks on a multiblock read for every single block read. Your
indexes need to be surgically precise to be used.
regards
Neil Chandler
Database Guy.
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Jonathan Lewis <jlewisoracle@xxxxxxxxx>
Sent: 17 January 2021 09:58
To: oracle-l@xxxxxxxxxxxxx <oracle-l@xxxxxxxxxxxxx>
Subject: Re: db file multiblock read count
There are only two points you have to consider -
a) does a non-default value have any effect on the actual activity at the time
of execution
b) does a non-default value have any effect on the cost of the query, and would
that make any critical plans change in an undesirable way.
Both points can be investigated with some very simple modelling, though the
"actual activity" testing may require a little though to cover variations like
parallel execution, direct path reads in general, possible effects on "small"
tables; and the "costing" testing needs to consider the effects of system
statistics (dbms_stats.xxx_system_stats) and/or the calibrate_io calls.
Once you've done the tests that are most relevent to your application and setup
you may still find that leaving everything to default is the safest strategy.
Regards
Jonathan Lewis
[https://ipmcdn.avast.com/images/icons/icon-envelope-tick-green-avg-v1.png]<https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.avg.com%2Femail-signature%3Futm_medium%3Demail%26utm_source%3Dlink%26utm_campaign%3Dsig-email%26utm_content%3Dwebmail&data=04%7C01%7C%7C73b8571cb084449dac1308d8bace89ce%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637464743531728542%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=AjoMMNhHKi6VSGgt1VDWvniOBbNllZLHRS9bDm6llDY%3D&reserved=0>
Virus-free.
www.avg.com<https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.avg.com%2Femail-signature%3Futm_medium%3Demail%26utm_source%3Dlink%26utm_campaign%3Dsig-email%26utm_content%3Dwebmail&data=04%7C01%7C%7C73b8571cb084449dac1308d8bace89ce%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637464743531728542%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=AjoMMNhHKi6VSGgt1VDWvniOBbNllZLHRS9bDm6llDY%3D&reserved=0>
On Fri, 15 Jan 2021 at 22:17, Moustafa Ahmed
<moustafa_dba@xxxxxxxxxxx<mailto:moustafa_dba@xxxxxxxxxxx>> wrote:
Hello
There are many opinions out there about the right value for
db file multiblock read count
And the more I look it seems like it started to be of less significance than 10
years ago!
Say a value of 1024 which is considerably high
On Exadata and a DW system
What would be the concerns with that?
