Path: text.usenetserver.com!out04a.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!73g2000cwn.googlegroups.com!not-for-mail From: andrew.markiewicz@gmail.com Newsgroups: comp.databases.oracle.server Subject: Re: Join selectivity is 0 causing bad cardinality estimates. Date: 12 Dec 2006 14:27:52 -0800 Organization: http://groups.google.com Lines: 73 Message-ID: <1165962472.795212.260240@73g2000cwn.googlegroups.com> References: <1165960064.046563.211800@n67g2000cwd.googlegroups.com> NNTP-Posting-Host: 64.73.76.107 Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" X-Trace: posting.google.com 1165962478 8303 127.0.0.1 (12 Dec 2006 22:27:58 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Tue, 12 Dec 2006 22:27:58 +0000 (UTC) In-Reply-To: User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.0.8) Gecko/20061025 Firefox/1.5.0.8,gzip(gfe),gzip(gfe) Complaints-To: groups-abuse@google.com Injection-Info: 73g2000cwn.googlegroups.com; posting-host=64.73.76.107; posting-account=AetYPw0AAACCwIYdPo1MoOwoknFchesW Xref: usenetserver.com comp.databases.oracle.server:419072 X-Received-Date: Tue, 12 Dec 2006 17:27:58 EST (text.usenetserver.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" wrote: > wrote in messagenews:1165960064.046563.211800@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