Re: System stats

Generally speaking, yes, I would always opt to get the Oracle optimizer as much information as possible. System statistics are used by the optimizer and thus can help in producing more optimal plans. This of course also means that if you suddenly start gathering them on a busy production server, plans may optimize differently.

Regarding the maintenance, I would agree that it's a low maintenance activity - but I wouldn't necessarily say to leave it out there forever. If your storage changes, or the workload on said storage changes that affects your throughput in a significant enough manner (you suddenly share storage with other systems, or share the same SAN, etc), you may want to update the system stats as well to reflect that fact. Similarly I would consider updating them, if you change platforms, migrate to a bigger server, etc. Things like that. I would also include it as part of database upgrades, as the algorithms may change between versions, though I couldn't give you a specific example when that last happened (but someone else on this list may well know that).

For example, on a highly volatile environment such as the cloud, where you never know where your database ends up running and how many others may share the same hardware, being more aggressive with your system statistics can help maintain a more stable database, particularly if you automate it and track the history of it.

On Mon, Mar 25, 2019 at 11:57 AM Cee Pee <carlospena999_at_gmail.com> wrote:

> List,
>
> I was reading up on system stats and came across this link:
>
> https://blogs.oracle.com/optimizer/should-you-gather-system-statistics
>
> Here are some of the things the author says:
>
> 1. "if you are at a decision point and you need to choose whether to
> gather them or not, then in most cases you should use the defaults and *not
> *gather system statistics."
>
> Doesnt setting systems help a lot these days esp with faster IO devices.
> Do the listers collect system stats in your environments, test. prod, etc?
>
> 2. "there is at least *some *management or procedural overhead required
> to maintain them"
>
> 'Maintaining' stats? I thought once we set the system stats we leave it
> out there forever without touching it?
>
> Thanks all,
> CP
>

-- 
//
zztat - The Next-Gen Oracle Performance Monitoring and Reaction Framework!
Visit us at zztat.net | _at_zztat_oracle | fb.me/zztat | zztat.net/blog/

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