Re: [External] : Re: Question on gathering System Statistics

As I recall,

Maria indicated.

1 it is incredibly difficult to capture representative stats.

You probably have different workloads overnight, at the weekend or even once a year, it is difficult to capture anything representative.

Not an issue here with no workload, but worth mentioning.

2 a huge number or Oracle performance problems that the support desk handled were related to poor system statistics.

3 apart from major releases, there is no regression testing of non standard system statistics.

Ie. If you use non standard statistics, you are far more likely to encounter hidden bugs and fringe cases in the optimiser that Oracle hadn't foreseen or experienced.

Care should be taken. Before any change I would encourage taking baseline plan statistics as a reference.

But I'd also encourage you to be very careful about gathering system statistics full stop.

D.

On Mon, 1 Feb 2021, 17:09 Jeff Smith, <jeff.d.smith_at_oracle.com> wrote:

> Maria confirms
>
> “That is correct. Its best not to gather system stats”
>
>
>
> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> *On
> Behalf Of *Jared Still
> *Sent:* Monday, February 1, 2021 11:26 AM
> *To:* neil_chandler_at_hotmail.com
> *Cc:* jlewisoracle_at_gmail.com; oracle-l_at_freelists.org
> *Subject:* [External] : 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://urldefense.com/v3/__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__;JSUlJSUlJSUlJSUlJSUlJSUlJSUl!!GqivPVa7Brio!Iqc6WUoJQ12JaOSi84RxpVRSZIfmS7DlzMb9TGK0B6cQ1gWWRUbEeOnVk79-ihkoSrI$>
>
>
>
>
>
> 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/
> <https://urldefense.com/v3/__http:/www.pythian.com/blog/author/still/__;!!GqivPVa7Brio!Iqc6WUoJQ12JaOSi84RxpVRSZIfmS7DlzMb9TGK0B6cQ1gWWRUbEeOnVk79-5idzCnw$>
>
> Github: https://github.com/jkstill
> <https://urldefense.com/v3/__https:/github.com/jkstill__;!!GqivPVa7Brio!Iqc6WUoJQ12JaOSi84RxpVRSZIfmS7DlzMb9TGK0B6cQ1gWWRUbEeOnVk79-pi5IxPI$>
>
>

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