Re: Better cardinality estimate when dialing optimizer_features_enable back

  • From: Neil Kodner <nkodner@xxxxxxxxx>
  • To: Paul Drake <bdbafh@xxxxxxxxx>
  • Date: Wed, 25 Nov 2009 14:41:46 -0500

On Wed, Nov 25, 2009 at 2:36 PM, Paul Drake <bdbafh@xxxxxxxxx> wrote:

> Hello Neil.
>
> I'm not familiar with the hardware configuration referenced in this thread.
>
> If the SysAdmins have enabled read-ahead behavior (or adaptive
> read-ahead) in the RAID controllers or in the management software of
> the storage units, one would expect to see that the multiblock read
> time is low as the data has been pre-fetched into the (storage
> controller) cache.

Not entirely sure about this one.  We're using Veritas File Systems on our
Oracle Volumes if that helps. Solaris 10.

>


> Perhaps you can design a test that would cause scans to be executed
> against many different tables at the same time which might defeat the
> read-ahead behavior?
>
> I'm guessing that if the queue length is greater than a threshold
> value that the read-ahead is not going to be active. This of course
> would depend upon the vendor implementation.
>
> hth.
>
> Paul
>
>
> On Wed, Nov 25, 2009 at 2:12 PM, Neil Kodner <nkodner@xxxxxxxxx> wrote:
> > 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@xxxxxxxxxxxxxxxxxxxxx>
> > 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.completestreets.org/faq.html
> http://safety.fhwa.dot.gov/ped_bike/docs/pamanual.pdf
>

Other related posts: