Re: Question on gathering System Statistics

Mladen,

In addition to being very smart, Maria is also privy to a lot of insider information that cannot be shared with us mere mortals.

My assumption was that there are some known-to-oracle reasons for what she said.

Jared

On Mon, Feb 1, 2021 at 17:45 Mladen Gogala <gogala.mladen_at_gmail.com> wrote:

> 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>
> 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
>
>
> --
> Mladen Gogala
> Database Consultanthttps://dbwhisperer.wordpress.com
>
> --
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