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 14:58:47 -0700
Message-ID: <1175723927.176051.244760@q75g2000hsh.googlegroups.com>


On Apr 4, 3:24 pm, sybra..._at_hccnet.nl wrote:
> On 4 Apr 2007 11:08:09 -0700, "Kevin Kirkpatrick"
>
>
>
>
>
> <kvnkrkpt..._at_gmail.com> wrote:
> >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.
>
> >-- 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.

Incorrect, the default is COMPUTE, so my stats were not estimates. However, if it makes it clearer to spell it out:

SQL> BEGIN
  2 DBMS_STATS.GATHER_TABLE_STATS
(OWNNAME=>'APPS',TABNAME=>'KK_HUGE',ESTIMATE_PERCENT=>100);   3 DBMS_STATS.GATHER_TABLE_STATS
(OWNNAME=>'APPS',TABNAME=>'KK_BIG',ESTIMATE_PERCENT=>100);   4 DBMS_STATS.GATHER_TABLE_STATS
(OWNNAME=>'APPS',TABNAME=>'KK_SMALL',ESTIMATE_PERCENT=>100);   5 END;
  6 /

PL/SQL procedure successfully completed.

Now, I EXPLAIN the same query, and sure enough, I get the exact same plan:

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

> 2 you probably don't have any proper histograms. The default is to
> calculate histograms with only 2 buckets.

Let's talk about the simple example above. As in our database, there aren't any really skewed columns, so what histogram definition am I missing?

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

Thanks. From the trace file



QUERY BLOCK TEXT

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

QUERY BLOCK SIGNATURE

qb name was generated
signature (optimizer): qb_name=SEL$1 nbfros=3 flg=0
  fro(0): flg=0 objn=2338915 hint_alias="KK_BIG"@"SEL$1"
  fro(1): flg=0 objn=2338913 hint_alias="KK_HUGE"@"SEL$1"
  fro(2): flg=0 objn=2338917 hint_alias="KK_SMALL"@"SEL$1"
*****************************

SYSTEM STATISTICS INFORMATION

  Using NOWORKLOAD Stats
  CPUSPEED: 121 millions instruction/sec   IOTFRSPEED: 4096 bytes per millisecond (default is 4096)   IOSEEKTIM: 10 milliseconds (default is 10)

BASE STATISTICAL INFORMATION

Table Stats::
  Table: KK_HUGE Alias: KK_HUGE
    #Rows: 50000 #Blks: 150 AvgRowLen: 16.00   Column (#2): HUGE_VAL(VARCHAR2)
    AvgLen: 12.00 NDV: 50000 Nulls: 0 Density: 2.0000e-05   Column (#1): ID(NUMBER)
    AvgLen: 5.00 NDV: 50000 Nulls: 0 Density: 2.0000e-05 Min: 1 Max: 50000
Index Stats::
  Index: SYS_C00867190 Col#: 1
    LVLS: 1 #LB: 104 #DK: 50000 LB/K: 1.00 DB/K: 1.00 CLUF: 150.00

Table Stats::
  Table: KK_BIG Alias: KK_BIG
    #Rows: 5000 #Blks: 14 AvgRowLen: 14.00   Column (#1): ID(NUMBER)
    AvgLen: 4.00 NDV: 5000 Nulls: 0 Density: 2.0000e-04 Min: 1 Max: 5000
Index Stats::
  Index: SYS_C00867191 Col#: 1
    LVLS: 1 #LB: 10 #DK: 5000 LB/K: 1.00 DB/K: 1.00 CLUF: 14.00

Table Stats::
  Table: KK_SMALL Alias: KK_SMALL
    #Rows: 500 #Blks: 2 AvgRowLen: 14.00   Column (#1): HUGE_VAL(VARCHAR2)
    AvgLen: 10.00 NDV: 500 Nulls: 0 Density: 0.002

SINGLE TABLE ACCESS PATH
  Table: KK_SMALL Alias: KK_SMALL
    Card: Original: 500 Rounded: 1 Computed: 0.00 Non Adjusted: 0.00
  Access Path: TableScan
    Cost: 2.15 Resp: 2.15 Degree: 0
      Cost_io: 2.00  Cost_cpu: 215293
      Resp_io: 2.00  Resp_cpu: 215293
  Best:: AccessPath: TableScan
         Cost: 2.15  Degree: 1  Resp: 2.15  Card: 0.00  Bytes: 0
***************************************

SINGLE TABLE ACCESS PATH
  Table: KK_BIG Alias: KK_BIG
    Card: Original: 5000 Rounded: 5000 Computed: 5000.00 Non Adjusted: 5000.00
  Access Path: TableScan
    Cost: 5.65 Resp: 5.65 Degree: 0
      Cost_io: 5.00  Cost_cpu: 949700
      Resp_io: 5.00  Resp_cpu: 949700
  Best:: AccessPath: TableScan
         Cost: 5.65  Degree: 1  Resp: 5.65  Card: 5000.00  Bytes: 0
***************************************

SINGLE TABLE ACCESS PATH
  Table: KK_HUGE Alias: KK_HUGE
    Card: Original: 50000 Rounded: 50000 Computed: 50000.00 Non Adjusted: 50000.00
  Access Path: TableScan
    Cost: 41.58 Resp: 41.58 Degree: 0
      Cost_io: 35.00  Cost_cpu: 9568216
      Resp_io: 35.00  Resp_cpu: 9568216
  Best:: AccessPath: TableScan
         Cost: 41.58  Degree: 1  Resp: 41.58  Card: 50000.00  Bytes: 0
***************************************

OPTIMIZER STATISTICS AND COMPUTATIONS

GENERAL PLANS

Considering cardinality-based initial join order. Permutations for Starting Table :0

Join order[1]: KK_SMALL[KK_SMALL]#0 KK_BIG[KK_BIG]#1 KK_HUGE[KK_HUGE]#2

Now joining: KK_BIG[KK_BIG]#1

NL Join
  Outer table: Card: 0.00 Cost: 2.15 Resp: 2.15 Degree: 1 Bytes: 14
  Inner table: KK_BIG Alias: KK_BIG
  Access Path: TableScan
    NL Join: Cost: 7.80 Resp: 7.80 Degree: 1
      Cost_io: 7.00  Cost_cpu: 1164993
      Resp_io: 7.00  Resp_cpu: 1164993
  Best NL cost: 7.80
          resc: 7.80 resc_io: 7.00 resc_cpu: 1164993
          resp: 7.80 resp_io: 7.00 resp_cpu: 1164993
Join Card: 15.63 = outer (0.00) * inner (5000.00) * sel (1)
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!!!!!!!!!!!!!!!!!!!!!!!!!  OOPS !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Join Card - Rounded: 16 Computed: 15.63
Best:: JoinMethod: NestedLoop

       Cost: 7.80 Degree: 1 Resp: 7.80 Card: 15.63 Bytes: 28



Now joining: KK_HUGE[KK_HUGE]#2

NL Join
  Outer table: Card: 15.63 Cost: 7.80 Resp: 7.80 Degree: 1 Bytes: 28
  Inner table: KK_HUGE Alias: KK_HUGE
  Access Path: TableScan
    NL Join: Cost: 639.13 Resp: 639.13 Degree: 1
      Cost_io: 533.00  Cost_cpu: 154256449
      Resp_io: 533.00  Resp_cpu: 154256449
  Access Path: index (UniqueScan)

    Index: SYS_C00867190
    resc_io: 1.00 resc_cpu: 9191
    ix_sel: 2.0000e-05 ix_sel_with_filters: 2.0000e-05
    NL Join: Cost: 23.90  Resp: 23.90  Degree: 1
      Cost_io: 23.00  Cost_cpu: 1312056
      Resp_io: 23.00  Resp_cpu: 1312056

  Access Path: index (AllEqUnique)
    Index: SYS_C00867190
    resc_io: 1.00 resc_cpu: 9191
    ix_sel: 2.0000e-05 ix_sel_with_filters: 2.0000e-05
    NL Join: Cost: 23.90  Resp: 23.90  Degree: 1
      Cost_io: 23.00  Cost_cpu: 1312056
      Resp_io: 23.00  Resp_cpu: 1312056
  Best NL cost: 23.90
          resc: 23.90 resc_io: 23.00 resc_cpu: 1312056
          resp: 23.90 resp_io: 23.00 resp_cpu: 1312056
Join Card: 0.00 = outer (15.63) * inner (50000.00) * sel (4.0002e-10)

<snip>

Number of join permutations tried: 6



(newjo-save) [2 1 0 ]
Final - All Rows Plan: Best join order: 1
  Cost: 23.9027  Degree: 1  Card: 1.0000  Bytes: 44
  Resc: 23.9027  Resc_io: 23.0000  Resc_cpu: 1312056
  Resp: 23.9027  Resp_io: 23.0000  Resc_cpu: 1312056
kkoipt: Query block SEL$1 (#0)

<snip>

Current SQL statement for this session:
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

Plan Table

+-----------------------------------+
| Id  | Operation                     | Name         | Rows  | Bytes |
Cost  | Time      |
-----------------------------------------------------
+-----------------------------------+
| 0   | SELECT STATEMENT              |              |       |
|    24 |           |
| 1   |  NESTED LOOPS                 |              |     1 |    44
|    24 |  00:00:01 |
| 2   |   MERGE JOIN CARTESIAN        |              |    16 |   448
|     8 |  00:00:01 |
| 3   |    TABLE ACCESS FULL          | KK_SMALL     |     1 |    14
|     2 |  00:00:01 |
| 4   |    BUFFER SORT                |              |  5000 |   68K
|     6 |  00:00:01 |
| 5   |     TABLE ACCESS FULL         | KK_BIG       |  5000 |   68K
|     6 |  00:00:01 |
| 6   |   TABLE ACCESS BY INDEX ROWID | KK_HUGE      |     1 |    16
|     1 |  00:00:01 |
| 7   |    INDEX UNIQUE SCAN          | SYS_C00867190|     1 |
|     0 |           |
-----------------------------------------------------
+-----------------------------------+

<snip>

Again - my major major gripe with the CBO is in the line right before the !OOPS!. The calculation

Join Card: 15.63 = outer (0.00) * inner (5000.00) * sel (1)

makes no sense - it is impossible for cardinality of "outer" to be a fraction less than 1. I know the CBO relies on probabilities - but the probability of the outer query having 0 < card < 1 is zero - it is impossible. The CBO ought to compute using "most likely cardinality" - on other words:

Join Card: 15.63 = max(outer (0.00),1) * max(inner (5000.00),1) * sel (1)

> --
> Sybrand Bakker
> Senior Oracle DBA- Hide quoted text -
>
> - Show quoted text -

Not trying to pick on you Sybrand, but given the sample database and the desired query, what steps should be taken to avoid the atrocious plan? Received on Wed Apr 04 2007 - 16:58:47 CDT

Original text of this message

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