Re: Better cardinality estimate when dialing optimizer_features_enable back
Date: Wed, 25 Nov 2009 14:12:26 -0500
Message-ID: <ae2c46ba0911251112h1022526es8faa6f8fe906870_at_mail.gmail.com>
As far as things that might have changed statistics-wise, I'm not too sure. I can ask around, there are other DBAs in the org, but I dont think anyone would have changed the stats gathering methods. the gather_stats_job runs but it hasn't touched any of the tables in this specific schema.
One other thing that Randolf touched upon was the single block read time taking longer than multi-block. I calculated workload statistics again and received a similar response. Is this worth bringing up to the Sysadmins? I've since deleted the system stats and computed noworkload statistics instead.
On Wed, Nov 25, 2009 at 11:51 AM, Randolf Geist <info_at_sqltools-plusplus.org>wrote:
> > Well look what I found while poking around sys.wri$_optstat_histhead_
> > history
> >
> > http://bit.ly/75oNst (screenshot)
> >
> > Could this have been a function of sample size changing? Could
> > parallel have anything to do with it?
>
> Since the density itself was so far not used for the cardinality estimate
> calculation this is merely an indicator that "something" has changed - the
> old density is calculated based on the histogram generated. So a significant
> change in the density might indicate that the underlying histogram has
> changed.
>
> That was one of the questions I haven't raised yet - what could have
> changed, since I already had the impression that there was a time when the
> batch job performed better from your comments.
>
> You can try to restore the old statistics from the statistics history to
> see if there were significant differences in the histogram generated and the
> sample size used to gather the statistics along with the number of distinct
> values gathered. This way you could also check what NewDensity got
> calculated with the old statistics and what the estimates looked like for
> your example queries.
>
> The slowly increasing density suggests that the underlying data might
> slowly have changed to something now that might have changed the shape of
> the height-balanced histogram so that the density calculation was
> significantly affected.
>
> Regards,
> Randolf
>
> Oracle related stuff blog:
> http://oracle-randolf.blogspot.com/
>
> Co-author of the forthcoming "OakTable Expert Oracle Practices" book:
> http://www.apress.com/book/view/1430226684
>
> http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684
> ___________________________________________________________
> Preisknaller: WEB.DE DSL Flatrate für nur 16,99 Euro/mtl.!
> http://produkte.web.de/go/02/
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Nov 25 2009 - 13:12:26 CST