Re: Nested loop cost looks too high on 19c
- From: Jonathan Lewis <jlewisoracle@xxxxxxxxx>
- To: Oracle L <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 2 Jun 2022 11:37:27 +0100
And so I am thinking, how safe is it to just update the num_distinct at
the global level for that column to the same value as that of 11.2 system?
Or will it be overridden when next partition gather happens.
Without knowing what has happened in the past, and exactly what calls to
gather stats are taking place (including batch jobs and overnight jobs),
and what preferences have been set, it's impossible to say why you've got a
table where the global num_distinct is so far off the value that you would
expect given the information you'vs shown so far. If you don't know why
you're in the current position there's no way of knowing if, or when,
Oracle would revert to that position if you tried to solve the problem by
using an explicit call to dbms_stats.set column_stats - at worst it
probably won't cause any harm in the short term, and the value might
survive for a while before suddenly reverting (e.g. after a couple of weeks
because the table stats have gone stale).
You can't really get any information from 11.2 about what's happening, but
19c has a couple of report options in dbms_stats to show you all the stats
collection operations, and autostat job collection details, so you could
review a little bit of the past to see if that tells you how the calls are
made. See:
https://jonathanlewis.wordpress.com/2018/09/10/stats-time-2/
Regards
Jonathan Lewis
On Wed, 1 Jun 2022 at 09:59, Pap <oracle.developer35@xxxxxxxxx> wrote:
Yes Jonathan, as I had just replied couple of minutes ago it's a 24/7 data
load table and stats gather happens manually throughout the scheduled job.
But the test system is the one which has been restored from production
only. But I doubt that stats gather job is really running there as because
data load is not happening in that system 24/7 as it's in production. And
so ideally statistics should not change/differ untill someone has manually
gathered and 19c algo took effect on the num_distinct value. And so I am
thinking, how safe is it to just update the num_distinct at the global
level for that column to the same value as that of 11.2 system? Or will it
be overridden when next partition gather happens.
On Wed, 1 Jun 2022, 12:39 pm Jonathan Lewis, <jlewisoracle@xxxxxxxxx>
wrote:
Sorry,
I forget you'd already said granularity=>'PARTITION'
Does that mean you have a procedure that populates a partition then
gathers stats (with a trickle of change thereafter)?
Regards
Jonathan Lewis
On Tue, 31 May 2022 at 23:47, Jonathan Lewis <jlewisoracle@xxxxxxxxx>
wrote:
One other thing to check on gathering stats - what was the setting for
the granularity paramter?
Regards
Jonathan Lewis
Other related posts:
