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@xxxxxxxx> 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@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Mladen Gogala
*Sent:* Tuesday, February 02, 2021 1:58 AM
*To:* oracle-l@xxxxxxxxxxxxx
*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
