Path: text.usenetserver.com!out03a.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!l12g2000cwl.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 16:00:14 -0800 Organization: http://groups.google.com Lines: 157 Message-ID: <1165968014.486737.296780@l12g2000cwl.googlegroups.com> References: <1165960064.046563.211800@n67g2000cwd.googlegroups.com> <1165962472.795212.260240@73g2000cwn.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 1165968022 5095 127.0.0.1 (13 Dec 2006 00:00:22 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Wed, 13 Dec 2006 00:00:22 +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: l12g2000cwl.googlegroups.com; posting-host=64.73.76.107; posting-account=AetYPw0AAACCwIYdPo1MoOwoknFchesW Xref: usenetserver.com comp.databases.oracle.server:419082 X-Received-Date: Tue, 12 Dec 2006 19:00:22 EST (text.usenetserver.com) 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 ); 10* end; The high and low values are now present and the CBO uses the correct selectivity. *********************** Table stats Table: EQUIPMENT Alias: E TOTAL :: CDN: 7794 NBLKS: 52 AVG_ROW_LEN: 42 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] Best NL cost: 4 resp: 3 (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" wrote: > wrote in messagenews:1165962472.795212.260240@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" > > 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.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