I too agree with most previous replies.
At a certain point in time in the past, setting db_file_multiblock_read_count
to 0 would make it oracle controlled and dynamic. However since it’s not
documented what it takes into account and how it responds to it, I never used
this setting, dynamic settings are unpredicatable especially if the whole
mechanism is undocumented. I just read the db_file_multiblock_read_count
parameter description in the oracle 19 documentation, and any mentioning of it
being dynamic is gone, rather it says that the default value is ‘maximum I/O
size that can be performed efficiently’ that is a rather clumsy and
non-descriptive formulation.
Setting the value higher makes you give Oracle the ability to potentially read
a large chunk in one go, which is useful if all the layers underneath Oracle do
support the size of the IO, and the optimal path for your execution is
performing full scans. The larger you set the value for
db_file_multiblock_read_count, the cheaper choosing a full segment scan becomes
for the optimizer. It’s up to you to determine a setting that is optimal. My
takeway would be to have db_file_multiblock_read_count at 128 (8k) and work
with that. l don’t have confidence in ‘leave it and let oracle deal with it’,
like some suggested, because Oracle doesn’t know how you system looks like.
If doing large IOs is optimal for whatever you do with that database, and it
makes sense to spend time tuning it (the choice whether to tune something
should be a cost based analysis too ;-) there are a couple of specifics that I
found that are good to know. I didn’t check them for the very latest versions
(12.2+), that’s up to you.
- There is no maximum IO size in linux to my knowledge. There used to be one,
but that was a long time ago.
- That doesn’t mean filesystems, storage drivers and storage hardware do not
have limits (obviously).
- A buffered multiblock IO (administered under the wait event db file scattered
read) cannot go beyond 1M (128/8k, 64/16k), even if you set DMRC higher.
- A direct path multiblock IO (administered under the wait event direct path
read) can go as high as 4096 (DMRC; 32MB@8k).
— ASM will not perform a larger IO than the AU size set for the diskgroup,
because that is the potential maximum size of adjacent blocks. (which is 4MB by
default on exadata).
— I found multiple IO mechanisms in Oracle that have 1MB as maximum IO size (on
linux), one of them is the database writer, to which oracle sometimes refers to
as ‘SSTIOMAX’.
— This is not a parameter, and nowhere to be found in the oracle database,
however some trace messages mention it. So maybe a constant defined in a header
file. (one of these functions is kcbb_current_space()).
Please mind this doesn’t mean I say you should set it this way. Actually,
probably not.
Like anything, there is a ‘goldilocks zone’ for settings where it performs
best, setting it too high will just make it really inefficient, just as setting
it too low will make it inefficient.
So set DMRC to 128 (when at 8k blocksize) and if you are doing
datawarehousing/OLAP on exadata set the special exadata mode for system
statistics to further prefer full segment scans, and it’s probably tuned well
enough for its use.
Frits Hoogland
http://fritshoogland.wordpress.com ;<http://fritshoogland.wordpress.com/>
frits.hoogland@xxxxxxxxx <mailto:frits.hoogland@xxxxxxxxx>
Mobile: +31 6 14180860
On 17 Jan 2021, at 12:36, Neil Chandler <neil_chandler@xxxxxxxxxxx> 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.
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://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>
<x-msg://21/#x_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
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?
