Maria confirms
“That is correct. Its best not to gather system stats”
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> On Behalf
Of Jared Still
Sent: Monday, February 1, 2021 11:26 AM
To: neil_chandler@xxxxxxxxxxx
Cc: jlewisoracle@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxx<mailto:neil_chandler@xxxxxxxxxxx>> 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@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
<oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>> on behalf
of Jonathan Lewis <jlewisoracle@xxxxxxxxx<mailto:jlewisoracle@xxxxxxxxx>>
Sent: 28 January 2021 11:08
To: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
<oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>>
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@xxxxxxxxx<mailto:Amir.Hameed@xxxxxxxxx>> 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$>
