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: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 12 Dec 2006 22:33:30 -0000
Message-ID: <WP2dnYBjteuks-LYRVnyigA@bt.com>

<andrew.markiewicz_at_gmail.com> wrote in message news: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.html
>

Something'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 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 - 16:33:30 CST

Original text of this message

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