Path: news.f.de.plusline.net!news-fra1.dfn.de!newsfeed.hanau.net!news.tiscali.de!newsfeed.freenet.de!feeder.news-service.com!newsgate.cistron.nl!transit.news.xs4all.nl!xs4all!textnews.nntp.hccnet.nl!textreader.nntp.hccnet.nl!not-for-mail From: sybrandb@hccnet.nl Newsgroups: comp.databases.oracle.misc Subject: Re: Number 1 gripe about CBO: 0 References: <1175697629.606670.305870@o5g2000hsb.googlegroups.com> <1175710089.606107.22730@p77g2000hsh.googlegroups.com> X-Newsreader: Forte Agent 4.2/32.1118 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Lines: 152 Organization: Hobby Computer Club News Network NNTP-Posting-Date: 04 Apr 2007 20:24:44 GMT NNTP-Posting-Host: 62.251.33.224 X-Trace: 1175718284 textreader.nntp.hccnet.nl 738 62.251.33.224:1985 X-Complaints-To: abuse@hccnet.nl Xref: news.f.de.plusline.net comp.databases.oracle.misc:78657 On 4 Apr 2007 11:08:09 -0700, "Kevin Kirkpatrick" wrote: >On Apr 4, 10:44 am, sybra...@hccnet.nl wrote: >> On 4 Apr 2007 07:40:29 -0700, "Kevin Kirkpatrick" >> >> >> >> >> >> wrote: >> >Clipped from a subtree of the execution plan (Oracle 10g R2) >> >> >8 MERGE JOIN CARTESIAN Cardinality: 2,442 >> > 5 TABLE ACCESS BY INDEX ROWID SCHEMA1.T1 Cardinality: 1 >> > 4 INDEX RANGE SCAN SCHEMA1.T1_I1 Cardinality: 1 >> > 7 BUFFER SORT Cardinality: 2,323,987 >> > 6 TABLE ACCESS FULL SCHEMA1.T2 Cardinality: 2,323,987 >> >> >Since when does 2.3 Million times "1" equal 2,442??? Yes, I know - >> >behind the scenes, the CBO has decided that the filter criteria of #4 >> >and #5 results in a cardinality of: >> >> >Cardinality #5 = 20,000 x .05 x .01 x .01 x.01 = 0.0001 rows >> >> >My beef is with the fact that the CBO does not immediately round this >> >intermediate value up to "1" before applying it to future >> >calculations. CARDINALITIES CAN NEVER EQUAL 0.0001 ROWS. They are >> >either 1 or more. So how can it make sense to EVER evaluate a >> >execution plan on the premise that one of intermediate steps will >> >yield 0.0001 rows, a logical impossibility? >> >> >The above snippet is a plan in a query that's been running in our >> >production system for 12 hours now. step #4 / #5 actually yields 4 >> >rows, not .0001 rows; so the CBO is doing a series of index nested >> >loops using > 10 million rows, not 2,400 rows. >> >The point is not that the stats on T1 and conditional criteria on T1 >> >yielded a lower-than-actual estimate; the point is that no matter how >> >low this estimate was, the CBO should have known that a cartesian join >> >of T1 and T2 would yield at least the cardinality of T2. >> >> The point is that you provide insufficient information by excluding >> the sql statement and part of the execution plan, >> so no one can judge whether your value judgements are correct. >> >> -- >> Sybrand Bakker >> Senior Oracle DBA- Hide quoted text - >> >> - Show quoted text - > >Connected to: >Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit >Production >With the Partitioning and Data Mining options > >SQL> >SQL> DROP TABLE KK_HUGE PURGE; > >Table dropped. > >SQL> >SQL> DROP TABLE KK_BIG PURGE; > >Table dropped. > >SQL> >SQL> DROP TABLE KK_SMALL PURGE; > >Table dropped. > >SQL> >SQL> create table kk_huge (ID PRIMARY KEY, HUGE_VAL) > 2 as select level id, LEVEL || ' VALUE' HUGE_VAL > 3 from DUAL CONNECT BY LEVEL < 50001; > >Table created. > >SQL> >SQL> create table kk_big (ID PRIMARY KEY, BIG_VAL) > 2 as select level id, LEVEL || ' VALUE' BIG_VAL > 3 from DUAL CONNECT BY LEVEL < 5001; > >Table created. > >SQL> >SQL> >SQL> create table kk_SMALL (HUGE_VAL, START_DATE_ACTIVE, >END_DATE_ACTIVE) > 2 as > 3 select LEVEL || ' VALUE' HUGE_VAL, > 4 CASE WHEN LEVEL > 490 THEN SYSDATE + 1 > 5 WHEN LEVEL < 100 THEN SYSDATE - 2 > 6 ELSE NULL END START_DATE_ACTIVE, > 7 CASE WHEN LEVEL > 495 THEN SYSDATE + 2 > 8 WHEN LEVEL < 50 THEN SYSDATE - 1 > 9 ELSE NULL END END_DATE_ACTIVE > 10 from DUAL CONNECT BY LEVEL < 501; > >Table created. > >SQL> >SQL> BEGIN > 2 DBMS_STATS.GATHER_TABLE_STATS ('APPS','KK_HUGE'); > 3 DBMS_STATS.GATHER_TABLE_STATS ('APPS','KK_BIG'); > 4 DBMS_STATS.GATHER_TABLE_STATS ('APPS','KK_SMALL'); > 5 END; > 6 / > >PL/SQL procedure successfully completed. > >-- NOW EXPLAIN THIS QUERY: >SELECT * FROM KK_SMALL, KK_BIG, KK_HUGE >WHERE SYSDATE BETWEEN NVL(START_DATE_ACTIVE,SYSDATE) AND >NVL(END_DATE_ACTIVE,SYSDATE) >AND UPPER(KK_SMALL.HUGE_VAL) LIKE '%ALU%' >AND UPPER(KK_SMALL.HUGE_VAL) LIKE '%AL%' >AND KK_HUGE.HUGE_VAL = KK_SMALL.HUGE_VAL >AND KK_BIG.ID = KK_HUGE.ID; > >-- Here is the plan I get on Oracle 10gR2: >SELECT STATEMENT Cardinality: 1 > 7 NESTED LOOPS Cardinality: 1 > 4 MERGE JOIN CARTESIAN Cardinality: 16 > 1 TABLE ACCESS FULL TABLE APPS.KK_SMALL Cardinality: 1 > 3 BUFFER SORT Cardinality: 5,000 > 2 TABLE ACCESS FULL TABLE APPS.KK_BIG Cardinality: >5,000 > 6 TABLE ACCESS BY INDEX ROWID TABLE APPS.KK_HUGE Cardinality: >1 > 5 INDEX UNIQUE SCAN INDEX (UNIQUE) APPS.SYS_C00867186 >Cardinality: 1 > > >> Also did you run the statement with event 10053 enabled? >How can I tell? 3 remarks 1 IIRC the default for gather table stats is to estimate statistics. Cardinality calculations are based upon those estimation. If you estimate statistics, cardinality can be wrong. 2 you probably don't have any proper histograms. The default is to calculate histograms with only 2 buckets. 3 alter session set events='10053 trace name context forever, level 1' prior to the statement will result in a trace file on the server. The trace file contains a complete breakdown of CBO's reasoning, including cardinality calculations. -- Sybrand Bakker Senior Oracle DBA