Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Number 1 gripe about CBO: 0 <Cardinality< 1 (?????)

Re: Number 1 gripe about CBO: 0 <Cardinality< 1 (?????)

From: Kevin Kirkpatrick <kvnkrkptrck_at_gmail.com>
Date: 4 Apr 2007 11:08:09 -0700
Message-ID: <1175710089.606107.22730@p77g2000hsh.googlegroups.com>


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)
  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.

> Also did you run the statement with event 10053 enabled?
How can I tell? Received on Wed Apr 04 2007 - 13:08:09 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US