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

I have been following this thread with interest. And here is my 2 cents worth. May be worth less than that actually in this discussion. I have collected system stats on AIX, multiple linux systems, HPUX, etc from the time Oracle first suggested collecting them. I have yet to see them make any significant difference in performance. I am not saying it never happens, but I have yet to see them make any measurable difference in performance. I haven't seen a lack of system statistics cause things to run slower, and I haven't seen gathering system stats improve performance.

On Tue, Feb 2, 2021 at 8:24 AM Mark W. Farnham <mwf_at_rsiz.com> wrote:

> In an ideal world the CBO would be able to sample from a rolling snapshot
> of current system statistics collected lightly against the current
> background load without being a significant load themselves and plans would
> be adjusted dynamically for that plan execution and perhaps some sort of
> “within tolerance” could be checked on soft parses.
>
>
>
> For the reasons you mentioned in the thread there are logical reasons to
> collect the system statistics.
>
>
>
> BUT having seen a wide range of the result of the currently actually
> implemented CBO versus queries with canned defaults versus locally
> collected statistics I’d go with Maria.
>
>
>
> You may have specific cases where careful collection (or punching to
> specific values) tends to create better plans for that particular case.
>
> The GOAL is to get good plans, and to get good enough plans for most plans
> so that special attention is needed only for a small number of cases either
> because it falls through a crack in the CBO’s armor or because it is so
> important or sizeable that only precise attention down to the bare metal
> fastest possible is required.
>
>
>
> SO, FOR NOW, don’t collect the system statistics unless you can show in
> your lab for a particular case that it improves things net-net by a big
> enough margin to care.
>
>
>
> mwf
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Mladen Gogala
> *Sent:* Tuesday, February 02, 2021 1:58 AM
> *To:* oracle-l_at_freelists.org
> *Subject:* Re: [External] : Re: Question on gathering System Statistics
>
>
>
> Any explanation? This looks a bit counter-intuitive to me. Would it be
> possible to persuade to come here and explain the recommendation?
>
> Regards
>
> On 2/1/21 12:09 PM, Jeff Smith wrote:
>
> Maria confirms
>
> “That is correct. Its best not to gather system stats”
>
>
>
> --
>
> Mladen Gogala
>
> Database Consultant
>
> https://dbwhisperer.wordpress.com
>
>

-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

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