Path: news.f.de.plusline.net!news-fra1.dfn.de!newsfeed.hanau.net!news.tiscali.de!tiscali!newsfeed1.ip.tiscali.net!proxad.net!64.233.160.134.MISMATCH!postnews.google.com!p77g2000hsh.googlegroups.com!not-for-mail From: "Kevin Kirkpatrick" Newsgroups: comp.databases.oracle.misc Subject: Re: Number 1 gripe about CBO: 0 References: <1175697629.606670.305870@o5g2000hsb.googlegroups.com> NNTP-Posting-Host: 144.160.98.31 Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" X-Trace: posting.google.com 1175710091 22564 127.0.0.1 (4 Apr 2007 18:08:11 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Wed, 4 Apr 2007 18:08:11 +0000 (UTC) In-Reply-To: User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; SBC; .NET CLR 1.1.4322),gzip(gfe),gzip(gfe) Complaints-To: groups-abuse@google.com Injection-Info: p77g2000hsh.googlegroups.com; posting-host=144.160.98.31; posting-account=mW7hBA0AAABTqVYqprbLgSusB9AyVeEZ Xref: news.f.de.plusline.net comp.databases.oracle.misc:78653 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?