Re: Question on gathering System Statistics

As you mention, Oracle has swung back and forth on this topic.

Last I heard ( 2 or 3 years ago) directly from Maria Colgan: do not gather system stats, earlier recommendations to do so were a mistake.

On Thu, Jan 28, 2021 at 03:44 Neil Chandler <neil_chandler_at_hotmail.com> wrote:

> 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 <oracle-l-bounce_at_freelists.org> on
> behalf of Jonathan Lewis <jlewisoracle_at_gmail.com>
> *Sent:* 28 January 2021 11:08
> *To:* oracle-l_at_freelists.org <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> 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
>
> --
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist Principal Consultant at Pythian
Oracle ACE Alumni
Pythian Blog http://www.pythian.com/blog/author/still/ Github: https://github.com/jkstill

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