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

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

--

http://www.freelists.org/webpage/oracle-l Received on Tue Feb 02 2021 - 15:23:28 CET