Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Join selectivity is 0 causing bad cardinality estimates.

Re: Join selectivity is 0 causing bad cardinality estimates.

From: <andrew.markiewicz_at_gmail.com>
Date: 12 Dec 2006 14:55:03 -0800
Message-ID: <1165964103.310235.63270@l12g2000cwl.googlegroups.com>


I didn't think of that. Excellent.
I tried the script you gave me Jonathon but it still does not have values for hi and low.

  1 select table_name, column_name, low_value, high_value   2 from user_tab_columns
  3 where table_name = 'EQUIPMENT'
  4* and column_name ='VEND_MODEL_NUM'

TABLE_NAME                     COLUMN_NAME                    LOW_VALUE
 HIGH_VALUE
------------------------------ ------------------------------
---------- ----------
EQUIPMENT                      VEND_MODEL_NUM

I also checked the production system where there still is a histogram and it does not have values for them either.

  1 select table_name, column_name, low_value, high_value   2 from user_tab_columns
  3 where table_name = 'EQUIPMENT'
  4* and column_name ='VEND_MODEL_NUM' -> /

TABLE_NAME                     COLUMN_NAME                    LOW_VALUE
 HIGH_VALUE
------------------------------ ------------------------------
---------- ----------
EQUIPMENT                      VEND_MODEL_NUM


What would cause the lack of stats?

On Dec 12, 4:33 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> <andrew.markiew..._at_gmail.com> wrote in messagenews:1165962472.795212.260240_at_73g2000cwn.googlegroups.com...
>
>
>
> > Thanks for the replies.
> > Tried dropping the histogram but the selectivity is still 0.
> > Even 1/2 cardinality estimate would be better than 1. Not the best but
> > definitely better.
>
> > Relevant trace data follows:
> > ***********************
> > Table stats Table: EQUIPMENT Alias: E
> > TOTAL :: CDN: 7792 NBLKS: 52 AVG_ROW_LEN: 42
> > Column: VEND_MODEL Col#: 3 Table: EQUIPMENT Alias: E
> > NDV: 172 NULLS: 0 DENS: 5.8140e-03 LO: 0 HI: 0
> > NO HISTOGRAM: #BKT: 1 #VAL: 2
> > -- Index stats
> > INDEX NAME: I_EQUIPMENT_2 COL#: 3
> > TOTAL :: LVLS: 1 #LB: 28 #DK: 172 LB/K: 1 DB/K: 6 CLUF: 1123
> > INDEX NAME: I_EQUIPMENT_3 COL#: 4
> > TOTAL :: LVLS: 1 #LB: 15 #DK: 2208 LB/K: 1 DB/K: 1 CLUF:
> > 2539
> > INDEX NAME: I_EQUIPMENT_PK COL#: 1
> > TOTAL :: LVLS: 1 #LB: 26 #DK: 7792 LB/K: 1 DB/K: 1 CLUF: 51
> > INDEX NAME: I_EQUIPMENT_UK1 COL#: 2
> > TOTAL :: LVLS: 1 #LB: 19 #DK: 7792 LB/K: 1 DB/K: 1 CLUF:
> > 4404
> > _OPTIMIZER_PERCENT_PARALLEL = 0
>
> > OPTIMIZER PERCENT INDEX CACHING = 60
> > Access path: index (join index)
> > Index: I_EQUIPMENT_2
> > TABLE: EQUIPMENT
> > RSC_CPU: 0 RSC_IO: 0
> > IX_SEL: 0.0000e+00 TB_SEL: 5.8140e-03
> > Join: resc: 3 resp: 3
> > Join cardinality: 0 = outer (192) * inner (7792) * sel (0.0000e+00)
> > [flag=0]
> > Best NL cost: 4 resp: 3
>
> > On Dec 12, 4:08 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk>
> > wrote:
> >> <andrew.markiew..._at_gmail.com> wrote in
> >> messagenews: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 Lewishttp://jonathanlewis.wordpress.com
>
> >> Author: Cost Based Oracle:
> >> Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> >> The Co-operative Oracle Users'
> >> FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.htmlSomething's now gone wrong with your stats.
>
> The low and high values are now zero - so it
> is correct that you get a cardinality of zero,
> the values at one end of the join fall outside
> the range of the values at the other end.
>
> I think you need to run the following to get
> just the column stats with low/high:
>
> begin
> dbms_stats.gather_table_stats(
> ownname => user,
> tabname =>'equipment',
> cascade => false,
> estimate_percent => null,
> granularity => 'default',
> method_opt => 'for columns vend_model size 1'
> );
> end;
> /
>
> --
> Regards
>
> Jonathan Lewishttp://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html
Received on Tue Dec 12 2006 - 16:55:03 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US