Re: Better cardinality estimate when dialing optimizer_features_enable back

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.
http://spreadsheets.google.com/ccc?key=0ArUTHdqN0J--dGtCLUZ1ZmhXMkpNUUpRVjZmcE9QLXc&hl=en

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
http://www.neilkodner.com/10046_trace.txt

and a 10053
http://www.neilkodner.com/10053_trace.txt

And finally, my analyze command
DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>USER,CASCADE=>TRUE,method_opt=>'FOR
ALL INDEXED COLUMNS SIZE AUTO',DEGREE=>4);









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
> http://structureddata.org
>

Other related posts: