| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Join selectivity is 0 causing bad cardinality estimates.
I changed the stats collection to use 'for all columns size 1' and
reran.
1 begin
2 dbms_stats.gather_table_stats(
3 ownname => user, 4 tabname =>'equipment', 5 cascade => false, 6 estimate_percent => null, 7 granularity => 'default', 8 method_opt => 'for all columns size 1'9 );
The high and low values are now present and the CBO uses the correct selectivity.
Column: VEND_MODEL Col#: 3 Table: EQUIPMENT Alias: E
NDV: 172 NULLS: 0 DENS: 5.8140e-03 LO: 3 HI: 193
NO HISTOGRAM: #BKT: 1 #VAL: 2
Join cardinality: 7794 = outer (192) * inner (7794) * sel (5.2083e-03)
[flag=0]
(Hurray!)
Upon inspecting the data dictionary I've noticed there are numerous other columns that we do not have low and high stats for that also have a NDV that are nonzero/non null. I would imagine these might suffer from the same problems. (In contrast, the majority of columns with a non null NDV do not have this issue.)
Our statistics are gathered weekly using a 15% estimate and method_opt
=> 'for all columns size auto'. I don't know if these parameters cause
any of these results.
I will investigate further if there are any known issues with stats
collection regarding this.
Thank you for your help and observations. They are invaluable. Andrew
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 - 18:00:14 CST
 |
 |