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

I concur. The only time I have noticed a big difference is on an Exadata platform that is used for OLTP vs DW. On a DW you collect system stats using the “EXADATA” option on and on an Exadata used primarily for OLTP, it is known to just delete system stats. Having system stats collected with “EXADATA” on an OLTP, will cause smartscans to be favored and that can cause issues for a very busy OLTP system.

---

Thanks,


Shane Borden
sborden76_at_yahoo.com




> On Feb 2, 2021, at 10:48 AM, Andrew Kerber <andrew.kerber_at_gmail.com> wrote:

> 

> 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 <mailto: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> [mailto: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 <mailto: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 <https://dbwhisperer.wordpress.com/>

> 

> -- 

> Andrew W. Kerber

> 

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



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