RE: db file multiblock read count

IMHO, Jonathan really says all that needs to be said right here.

Also consider the relative gains - how much gain do you really expect, and is that gain worth the effort around testing and then maintaining "non-default" values.

Clay Jackson

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> On Behalf Of Jonathan Lewis Sent: Sunday, January 17, 2021 1:59 AM
To: oracle-l_at_freelists.org
Subject: Re: db file multiblock read count

CAUTION: This email originated from outside of the organization. Do not follow guidance, click links, or open attachments unless you recognize the sender and know the content is safe.

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://nam12.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%7Cclay.jackson%40quest.com%7Cef8c88518ef04002582308d8bace8911%7C91c369b51c9e439c989c1867ec606603%7C0%7C0%7C637464743525671435%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=QTK%2BocYqpH9E3DbIP9iVfon1JKznOSbqjfHMMGgvUSA%3D&reserved=0> Virus-free. www.avg.com<https://nam12.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%7Cclay.jackson%40quest.com%7Cef8c88518ef04002582308d8bace8911%7C91c369b51c9e439c989c1867ec606603%7C0%7C0%7C637464743525681390%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=bvIIR6%2Fg2mTDYqtMWeydAxIzRJ0C53MwyA1SF0CCzmw%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