Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Number 1 gripe about CBO: 0 <Cardinality< 1 (?????)
On Apr 4, 10:44 am, sybra..._at_hccnet.nl wrote:
> On 4 Apr 2007 07:40:29 -0700, "Kevin Kirkpatrick"
>
>
>
>
>
> <kvnkrkpt..._at_gmail.com> 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)
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_ACTIVE10 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;
PL/SQL procedure successfully completed.
> Also did you run the statement with event 10053 enabled?
How can I tell?
Received on Wed Apr 04 2007 - 13:08:09 CDT