Re: Question on gathering System Statistics

Maria Colgan is usually very smart but this particular recommendation doesn't make much sense. There are situations in which it doesn't make sense to gather system statistics, but in general I do want the optimizer to know the cost single block read, multiple block reads and the optimal multi-block read count. Without knowing the actual values of SREADTIM, MREADTIM and MBRC it is not possible to calculate realistic price for a plan. Without system statistics, the instance will use built in values, regardless of whether it is running on a slow PC or an ultra fast latest Power 10 AIX box with XTremIO SAN. It stands to reason that the latter box will be able to tolerate a good bit more of IO operations than the PC and that the plan which would absolutely kill the PC would be considered acceptable on the AIX box.

Jared, a long. long time ago, when you and me, among others, were starting this mailing list on kbs,net, there was only one optimizer. The basic philosophy was: if there is an index on the column - use it. That was the rule optimizer, aka RBO. That worked fine until DSS  (decision support systems) started to gain prominence. For that, you needed trends and aggregations across ever larger data sets and index scans were not the best solution. There is a whole set of solutions to that problem, from Vertica to specialized DW boxes like Exadata, Greenplum and Netezza. One of the solutions was CBO.

The role of CBO was to precisely calculate the duration of the query and decide when NOT to use an index. If you want to precisely calculate the duration of the query on the particular system, you need IO characteristics of the system, in particular SREADTIM, MREADTIM and MBRC and that can only be achieved by gathering the system statistics. CBO allows you to do what the most of the people are doing: run a mixed mode OLTP and reporting DB on the same instance. It is supposed to be smart enough to know the difference between reporting queries and OLTP queries. Without system statistics it can't be.

On 2/1/21 11:25 AM, Jared Still wrote:
> 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
> <mailto: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
> <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
>
> --
> 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/
> <http://www.pythian.com/blog/author/still/>
> Github: https://github.com/jkstill <https://github.com/jkstill>
>
>

-- 
Mladen Gogala
Database Consultant
https://dbwhisperer.wordpress.com


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