But, I didn't see the first table showing any histograms in the 10053
which is why I suggested to histogram all tables involved with all
cols, to see what would happen. I was about to post this when Jonathan
noted this and said to drop the hist stats.
andrew.markiewicz_at_gmail.com wrote:
> We also have histograms calculated using a similar method as you have,
> FOR ALL COLUMNS SIZE AUTO.
>
>
> On Dec 12, 4:16 pm, "EscVector" <J..._at_webthere.com> wrote:
> > Jonathan,
> > Would it make sense to histogram the other table as I suggested, even
> > if there is little skew, possibly removing the halved cardinality
> > estimate?
> >
> > Jonathan Lewis wrote:
> > > <andrew.markiew..._at_gmail.com> wrote in message
> > >news:1165960064.046563.211800_at_n67g2000cwd.googlegroups.com...
> > > > Hello.
> > > > I have a tuning issue which has brought me a situation that is
> > > > confusing. I have reduced the issue down to a simple join which should
> > > > return all rows in the larger table (7792 rows). But the CBO join
> > > > cardinality estimate is 1 for this join. I created a 10053 trace and
> > > > found the join cardinality calculation uses a selectivity of zero
> > > > causing the CBO to estimate 1 row. What would cause this?
> >
> > > > There are no implicit conversion issues I can find. There are no
> > > > histogram lookups since there are not even any filtering predicates on
> > > > the sql....
> >
> > > You have a histogram on one end of the join.
> >
> > > Column: VEND_MODEL Col#: 3 Table: EQUIPMENT Alias: E
> > > NDV: 172 NULLS: 0 DENS: 3.3694e-03
> > > HEIGHT BALANCED HISTOGRAM: #BKT: 200 #VAL: 65
> >
> > > Wolfgang Breitling and Alberto Dell'Era were exchanging email
> > > with me a little while ago about an issue where the optimizer would
> > > halve the join cardinality estimate in circumstances similar to yours.
> > > Possibly you've been caught by the same "anomaly".
> >
> > > Try dropping the histogram temporarily to see if the cardinality changes.
> >
> > > --
> > > Regards
> >
> > > Jonathan Lewis
> > >http://jonathanlewis.wordpress.com
> >
> > > Author: Cost Based Oracle: Fundamentals
> > >http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
> >
> > > The Co-operative Oracle Users' FAQ
> > >http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Received on Tue Dec 12 2006 - 22:05:47 CST
