Re: Better cardinality estimate when dialing optimizer_features_enable back

The 10053 with the altered session (ofe 10.1.0) is at http://www.neilkodner.com/10053_10_1_0.txt

On Tue, Nov 24, 2009 at 3:32 PM, Greg Rahn <greg_at_structureddata.org> wrote:

> Can you put up 10053 traces from
> select * from letter_bin, where prty_id=102985
> one with the default OFE, and one with 10.1 or 9.2?
>
>
> On Tue, Nov 24, 2009 at 11:33 AM, Neil Kodner <nkodner_at_gmail.com> wrote:
> > If I simply select * from letter_bin, where prty_id=102985 with all
> default
> > session parameters, I get cardinatlity of 12, which is the number of not
> > null values / NDV.
> > If I change OFE to 10.1.0 or 9.2.0, I get a correct value for
> cardinality.
> >
> > On Tue, Nov 24, 2009 at 2:28 PM, Greg Rahn <greg_at_structureddata.org>
> wrote:
> >>
> >> So then it appears the predicate ("LETTER_BIN"."PRTY_ID"=102985) via
> >> LETTER_BIN_PRTY_ID_I is row source with the issue. In the first query
> >> it looks to be off by 2 orders of magnitude, 1692 vs 14, Thats
> >> usually not so good in an NL plan.
> >>
> >> Are the stats on the table and index both using 100% sample?
> >> Is there a histogram on LETTER_BIN. PRTY_ID?
> >> If you switch OFE to 9.2 just for the test query, do you get a
> >> different cardinality estimate, or is it that OFE=9.2 just yields an
> >> overall better plan for the full query?
> >>
> >> On Tue, Nov 24, 2009 at 11:19 AM, Neil Kodner <nkodner_at_gmail.com>
> wrote:
> >> > For what its worth, if I just explain plan on select * from letter_bin
> >> > where
> >> > prty_id=12345, I get an incorrect cardinality estimate. For that
> >> > reason, I
> >> > have not tried isolating other fields.
> >>
> >> --
> >> Regards,
> >> Greg Rahn
> >> http://structureddata.org
> >
> >
>
>
>
> --
> Regards,
> Greg Rahn
> http://structureddata.org
>

--
http://www.freelists.org/webpage/oracle-l