Re: db file multiblock read count

From: Neil Chandler <neil_chandler_at_hotmail.com>
Date: Sun, 17 Jan 2021 11:36:49 +0000
Message-ID: <DB7PR10MB20907E6B94D206F3EDF85DF385A50_at_DB7PR10MB2090.EURPRD10.PROD.OUTLOOK.COM>

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_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Jonathan Lewis <jlewisoracle_at_gmail.com> Sent: 17 January 2021 09:58
To: oracle-l_at_freelists.org <oracle-l_at_freelists.org> Subject: Re: db file multiblock read count

There are only two points you have to consider -

1. does a non-default value have any effect on the actual activity at the time of execution
2. 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_at_hotmail.com<mailto:moustafa_dba_at_hotmail.com>> 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?

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

Received on Sun Jan 17 2021 - 12:36:49 CET