Re: Better cardinality estimate when dialing optimizer_features_enable back

  • From: Neil Kodner <nkodner@xxxxxxxxx>
  • To: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
  • Date: Tue, 24 Nov 2009 11:40:40 -0500

Now I'm thoroughly confused.  Since last night's analyze, I now get the same
(poor) cardinality estimate regardless of optimizer_features_enable.
 However, in my QA system, which was updated from production, I can
replicate the problem.

Here are my table/column/index stats in both production and QA.

The real issue behind all of this is queries against letter_bin by prty_id.
 They are taking too long and scanning too many blocks when I have the poor
cardinality estimate.  With the correct cardinality estimates, I get
subsecond response.  Otherwise, they take anywhere from 1 to 30 seconds.

Here's a sample 10046

and a 10053

And finally, my analyze command

On Mon, Nov 23, 2009 at 7:59 PM, Greg Rahn <greg@xxxxxxxxxxxxxxxxxx> wrote:

> I would second this.  Get a 10053 trace from both versions and look at
> the trace file in your favorite graphical diff tool.  Look for the
> cardinality/density calculation.  I would do this for the full query
> as well as your one table test case .
> What are the two execution plans of the full query?
> On Mon, Nov 23, 2009 at 3:55 PM, Stefan Knecht <knecht.stefan@xxxxxxxxx>
> wrote:
> > Just a quick thought...
> >
> > One thing you can try is to take a 10053 trace, and check for the values
> of
> > the various underscore (optimizer features), as well as bugfixes listed
> in
> > the tracefile, for both values for optimizer_features_enable. Any
> difference
> > you may spot may lead you to some more information.
> --
> Regards,
> Greg Rahn

Other related posts: