RE: Question on gathering System Statistics

From: Hameed, Amir <Amir.Hameed_at_xerox.com>
Date: Fri, 29 Jan 2021 02:47:37 +0000
Message-ID: <BYAPR11MB34804FD18D32065331FE6691F4B99_at_BYAPR11MB3480.namprd11.prod.outlook.com>

Thanks Neil. Per MOS note E-Business Suite Applications Performance - Collecting Statistics in Oracle EBS 11i and R12 (Doc ID 368252.1), Oracle does suggest gathering System Statistics.

7) Should dbms_stats.gather_system_stats be used with E-Business 11i or R12? For E-Business Suite, for non-Exadata databases, it is recommend to gather system statistics with NOWORKLOAD option, as in: execute DBMS_STATS.GATHER_SYSTEM_STATS (gathering_mode => 'NOWORKLOAD');

Typically, this is only need to be done once after any major architectural changes made to the DB.

Thanks
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> On Behalf Of Neil Chandler Sent: Thursday, January 28, 2021 6:45 AM To: oracle-l_at_freelists.org; jlewisoracle_at_gmail.com Subject: Re: Question on gathering System Statistics

CAUTION: This email originated from outside the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe. Unsurprisingly, I agree with Jonathan.

The official Oracle line is (10 - gather system stats, 11 gather system stats, 12+ don't gather system stats - use the defaults***) They fundamentally affect the optimizer costings. If they aren't set, don't set them***. If they are set, understand what setting them has changed in terms of the optimizer. It can really affect the calculation between single block reads and multiblock reads, changing the balance between favouring selective index access versus full table or fast full index scans. From a stability perspective, you should leave them alone.

Neil Chandler

***there are exceptions to this, but my general rule is to leave as default in all releases of Oracle

---

From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>> on behalf of Jonathan Lewis <jlewisoracle_at_gmail.com<mailto:jlewisoracle_at_gmail.com>> Sent: 28 January 2021 11:08
To: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> <oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>> Subject: Re: Question on gathering System Statistics

A key question to ask first is whether you had system stats set/collected on the vmax250 and whether this was done in combination with setting the db_file_multiblock read count or leaving it to default, and had you done anything with the calibrate_io option? Ideally you probably want to believe that if you get the same plans with the Powermax you'll get the same, or better, performance. But if you do something that changes the optimizer's arithmetic you may get plan changes that result in random variations in performance.

If you don't know what you've done about system stats in the past there's a script at the end of this blog note that will report them for you: https://jonathanlewis.wordpress.com/2019/08/14/gather_system_stats/<https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fjonathanlewis.wordpress.com%2F2019%2F08%2F14%2Fgather_system_stats%2F&data=04%7C01%7C%7C7549f66e8e574167703808d8c37d08e0%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637474289095264596%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=cqbbmCDPiThkNr7rcbv3PoE6FAiRTM%2BD9f9a4DD1Mz4%3D&reserved=0>

Regards
Jonathan Lewis

On Tue, 26 Jan 2021 at 19:30, Hameed, Amir <Amir.Hameed_at_xerox.com<mailto:Amir.Hameed_at_xerox.com>> wrote:

Hi,

We are in the process of moving our applications from current storage array (VMAX250) to a new array (Powermax). From best practices standpoint, should we collect System Stats after moving to the new storage? If we should then is there a recommended way of gathering System Stats?

Thanks,

Amir

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

Received on Fri Jan 29 2021 - 03:47:37 CET