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: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 6 Apr 2007 20:21:48 -0700
Message-ID: <1175916108.489812.87890@l77g2000hsb.googlegroups.com> 





On Apr 6, 10:31 am, "Kevin Kirkpatrick" <kvnkrkpt..._at_gmail.com> wrote:


> On Apr 4, 10:10 pm, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:

> > 1)  SYSDATE BETWEEN NVL(START_DATE_ACTIVE,SYSDATE) AND


> > NVL(END_DATE_ACTIVE,SYSDATE)


> > 2)  AND UPPER(KK_SMALL.HUGE_VAL) LIKE '%ALU%'


> > 3)  AND UPPER(KK_SMALL.HUGE_VAL) LIKE '%AL%'


> > 4)  AND KK_HUGE.HUGE_VAL = KK_SMALL.HUGE_VAL


> > 5)  AND KK_BIG.ID = KK_HUGE.ID;


>

> > Selectivity:

> > 1)    0.05 * 0.05

> > 2)  * 0.05

> > 3)  * 0.05

> > = 0.05^4 = 0.00000625

>

> Right - Everything is perfectly sensible up until this point.  The

> selectivity problem is mostly tied to the condition

> "SYSDATE BETWEEN NVL(START_DATE,SYSDATE) AND NVL(END_DATE,SYSDATE)"


>

> which can be expressed as

>

> "(START_DATE IS NULL OR START_DATE < SYSDATE) AND (END_DATE IS NULL OR

> END_DATE > SYSDATE)"



I have included this run below to see how it compares on an otherwise
idle system.



> to give identical results, but without confusing the CBO (which can

> use stats/histograms on the latter to give accurate selection

> estimates but, given the former condition, will ignore all stats/

> histograms and yield a selectivity of (0.05 * 0.05) for the

> condition).

>

> My objection is not how the CBO gets the excessive selectivity, but

> rather how it applies the number:

>

> > Expected number of rows = 0.00000625 * 500

>

> At this point, I think the CBO approach is flawed.  We all know no

> WHERE clause, no matter how small the starting cardinality and no

> matter how many conditionals, can ever return a fraction of a row.  I

> believe the logic of the CBO should be:

>

> Expected number of rows = ceil(0.00000625 * 500)

>

> That is my gripe - the CBO uses floating point values to represent

> cardinality estimates when, based on a proper understanding of the

> problem domain, it ought to be using discrete integral values of the

> most likely non-zero cardinality.  The latter approach would avoid

> bizarre decision making as exemplified in the above example.  In fact,

> the CBO developers seemed to know that fractional cardinality

> estimates are flawed - notice that on the explain plan displayed to

> the world, the value "1" is displayed even though behind the scenes

> CBO is using "0.00003"




I have an unrelated code sample that will cause the 10053 trace file
to show a cardinality of 0.5 for one of the tables in a SQL statement
that references three tables.  This apparently causes a number of
overflows and leads to an ORA-600 in 10.2.0.2 (not tested yet in
10.2.0.3).  I understand your argument and mostly agree with you.
However, what if Oracle is most of the time effectively using these
partial row calculations to correctly reduce the cardinality coming
out of 95% of the join operations where they occur (I am NOT stating
this as fact, just offering it for consideration).



> <snip>

>

> > It is still taking about 49 seconds to execute, so the MERGE JOIN

> > CARTESIAN was not as damaging as initially expected.

>

> Your hint replaced the MERGE JOIN cartesian product with a NESTED LOOP

> cartesian product (tomayto / tomahto).  Try this hint: /*+

> CARDINALITY(kk_small,1) */, which will force the CBO to use "1" as the

> expected rows after applying conditions to kk_small (as I believe it

> ought to do).  Also, to see the real damage, make kk_big 100,000 rows

> and kk_huge 1,000,000 rows.




The different join methods: nested loops, sort-merge, and hash do not
offer the same performance as the number of rows involved in the join
changes.  The sort-merge join can be very time consuming when two
large data sets are joined.  Samples are provided below.



Test runs of different methods:


Original SQL statement:


SELECT /*+ GATHER_PLAN_STATISTICS */



  *


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;




>From 10053 Trace:



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:  3.06  Resp: 3.06  Degree: 0

      Cost_io: 3.00  Cost_cpu: 236657
      Resp_io: 3.00  Resp_cpu: 236657
  Best:: AccessPath: TableScan
         Cost: 3.06  Degree: 1  Resp: 3.06  Card: 0.00  Bytes: 0





SELECT



  *


FROM



  TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));



| Id  | Operation                    | Name        | Starts | E-Rows |


A-Rows |  A-Time   | Buffers |  OMem |  1Mem | Used-Mem |



|   1 |  NESTED LOOPS                |             |      1 |      1
|    446 |00:00:50.09 |    4460K|       |       |          |
|   2 |   MERGE JOIN CARTESIAN       |             |      1 |     16
|   2230K|00:00:13.38 |      50 |       |       |          |
|*  3 |    TABLE ACCESS FULL         | KK_SMALL    |      1 |      1
|    446 |00:00:00.01 |      33 |       |       |          |
|   4 |    BUFFER SORT               |             |    446 |   5000
|   2230K|00:00:04.46 |      17 |   181K|   181K|  160K (0)|
|   5 |     TABLE ACCESS FULL        | KK_BIG      |      1 |   5000
|   5000 |00:00:00.01 |      17 |       |       |          |
|*  6 |   TABLE ACCESS BY INDEX ROWID| KK_HUGE     |   2230K|      1
|    446 |00:00:43.42 |    4460K|       |       |          |
|*  7 |    INDEX UNIQUE SCAN         | SYS_C007864 |   2230K|      1
|   2230K|00:00:20.61 |    2230K|       |       |          |





Predicate Information (identified by operation id):




   3 - filter((UPPER("KK_SMALL"."HUGE_VAL") LIKE '%ALU%' AND
UPPER("KK_SMALL"."HUGE_VAL") LIKE '%AL%' AND

              NVL("START_DATE_ACTIVE",SYSDATE@!)<=SYSDATE@! AND
NVL("END_DATE_ACTIVE",SYSDATE@!)>=SYSDATE@!))

   6 - filter("KK_HUGE"."HUGE_VAL"="KK_SMALL"."HUGE_VAL")
   7 - access("KK_BIG"."ID"="KK_HUGE"."ID")



The modified SQL statement, which allows Oracle to use histograms:
SELECT /*+ GATHER_PLAN_STATISTICS */



  *


FROM


  KK_SMALL,
  KK_BIG,
  KK_HUGE




WHERE



  (START_DATE_ACTIVE IS NULL



    OR START_DATE_ACTIVE<=SYSDATE)


  AND (END_DATE_ACTIVE IS NULL



    OR 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;




>From 10053 Trace:



SINGLE TABLE ACCESS PATH



  Column (#2): START_DATE_ACTIVE(DATE)


    AvgLen: 3.00 NDV: 2 Nulls: 391 Density: 0.5 Min: 2454194 Max:
2454197


  Column (#3): END_DATE_ACTIVE(DATE)


    AvgLen: 2.00 NDV: 2 Nulls: 446 Density: 0.5 Min: 2454195 Max:
2454198


  Using prorated density: 0.4908 of col #3 as selectivity of out-of-
range value pred


  Table: KK_SMALL  Alias: KK_SMALL


    Card: Original: 500  Rounded: 1  Computed: 1.18  Non Adjusted:
1.18


  Access Path: TableScan


    Cost:  3.06  Resp: 3.06  Degree: 0

      Cost_io: 3.00  Cost_cpu: 235774
      Resp_io: 3.00  Resp_cpu: 235774
  Best:: AccessPath: TableScan
         Cost: 3.06  Degree: 1  Resp: 3.06  Card: 1.18  Bytes: 0





SELECT



  *


FROM



  TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));



| Id  | Operation                    | Name        | Starts | E-Rows |


A-Rows |  A-Time   | Buffers |  OMem |  1Mem | Used-Mem |



|   1 |  NESTED LOOPS                |             |      1 |      1
|    446 |00:00:00.02 |    1111 |       |       |          |
|*  2 |   HASH JOIN                  |             |      1 |      1
|    446 |00:00:00.01 |     188 |   878K|   878K| 1131K (0)|
|*  3 |    TABLE ACCESS FULL         | KK_SMALL    |      1 |      1
|    446 |00:00:00.01 |       4 |       |       |          |
|   4 |    TABLE ACCESS FULL         | KK_HUGE     |      1 |  50000
|  50000 |00:00:00.10 |     184 |       |       |          |
|   5 |   TABLE ACCESS BY INDEX ROWID| KK_BIG      |    446 |      1
|    446 |00:00:00.01 |     923 |       |       |          |
|*  6 |    INDEX UNIQUE SCAN         | SYS_C007865 |    446 |      1
|    446 |00:00:00.01 |     477 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------





Predicate Information (identified by operation id):




   2 - access("KK_HUGE"."HUGE_VAL"="KK_SMALL"."HUGE_VAL")
   3 - filter((UPPER("KK_SMALL"."HUGE_VAL") LIKE '%ALU%' AND
UPPER("KK_SMALL"."HUGE_VAL") LIKE '%AL%' AND

              ("END_DATE_ACTIVE" IS NULL OR

"END_DATE_ACTIVE">=SYSDATE@!) AND ("START_DATE_ACTIVE" IS NULL OR

              "START_DATE_ACTIVE"<=SYSDATE@!)))
   6 - access("KK_BIG"."ID"="KK_HUGE"."ID")



Note that the execution plan in the above changed significantly - the
join order changed and the sort-merge join is now gone.  If we know
that works, let's see if we can fix the original SQL statement with
just a hint:


SELECT /*+ GATHER_PLAN_STATISTICS USE_HASH(KK_SMALL KK_HUGE) */

  *


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;




>From the 10053 Trace:



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:  3.06  Resp: 3.06  Degree: 0

      Cost_io: 3.00  Cost_cpu: 236657
      Resp_io: 3.00  Resp_cpu: 236657
  Best:: AccessPath: TableScan
         Cost: 3.06  Degree: 1  Resp: 3.06  Card: 0.00  Bytes: 0





SELECT



  *


FROM



  TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));



| Id  | Operation                    | Name        | Starts | E-Rows |


A-Rows |  A-Time   | Buffers |  OMem |  1Mem | Used-Mem |



|   1 |  NESTED LOOPS                |             |      1 |      1
|    446 |00:00:00.02 |    1111 |       |       |          |
|*  2 |   HASH JOIN                  |             |      1 |      1
|    446 |00:00:00.01 |     188 |   878K|   878K| 1133K (0)|
|*  3 |    TABLE ACCESS FULL         | KK_SMALL    |      1 |      1
|    446 |00:00:00.01 |       4 |       |       |          |
|   4 |    TABLE ACCESS FULL         | KK_HUGE     |      1 |  50000
|  50000 |00:00:00.10 |     184 |       |       |          |
|   5 |   TABLE ACCESS BY INDEX ROWID| KK_BIG      |    446 |      1
|    446 |00:00:00.01 |     923 |       |       |          |
|*  6 |    INDEX UNIQUE SCAN         | SYS_C007865 |    446 |      1
|    446 |00:00:00.01 |     477 |       |       |          |





Predicate Information (identified by operation id):





   2 - access("KK_HUGE"."HUGE_VAL"="KK_SMALL"."HUGE_VAL")
   3 - filter((UPPER("KK_SMALL"."HUGE_VAL") LIKE '%ALU%' AND
UPPER("KK_SMALL"."HUGE_VAL") LIKE '%AL%' AND

              NVL("START_DATE_ACTIVE",SYSDATE@!)<=SYSDATE@! AND
NVL("END_DATE_ACTIVE",SYSDATE@!)>=SYSDATE@!))

   6 - access("KK_BIG"."ID"="KK_HUGE"."ID")



The above completed very quickly when compared to the unhinted
version.



Let's try again to force a sort-merge join:
SELECT /*+ GATHER_PLAN_STATISTICS USE_MERGE(KK_SMALL KK_HUGE) */

  *


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;




>From the 10053 Trace:



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:  3.06  Resp: 3.06  Degree: 0

      Cost_io: 3.00  Cost_cpu: 236657
      Resp_io: 3.00  Resp_cpu: 236657
  Best:: AccessPath: TableScan
         Cost: 3.06  Degree: 1  Resp: 3.06  Card: 0.00  Bytes: 0





SELECT



  *


FROM



  TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));



|   1 |  MERGE JOIN          |          |      1 |      1 |    446 |
00:00:00.07|     175 |       |       |          |
|   2 |   SORT JOIN          |          |      1 |   5000 |   4991 |
00:00:00.04|     171 |   267K|   267K|  237K (0)|
|*  3 |    HASH JOIN         |          |      1 |   5000 |   5000 |
00:00:00.05|     171 |   870K|   870K| 1238K (0)|
|   4 |     TABLE ACCESS FULL| KK_BIG   |      1 |   5000 |   5000 |
00:00:00.01|      17 |       |       |          |
|   5 |     TABLE ACCESS FULL| KK_HUGE  |      1 |  50000 |  50000 |
00:00:00.10|     154 |       |       |          |
|*  6 |   SORT JOIN          |          |   4991 |      1 |    446 |
00:00:00.03|       4 | 15360 | 15360 |14336  (0)|
|*  7 |    TABLE ACCESS FULL | KK_SMALL |      1 |      1 |    446 |
00:00:00.01|       4 |       |       |          |





Predicate Information (identified by operation id):



   3 - access("KK_BIG"."ID"="KK_HUGE"."ID")
   6 - access("KK_HUGE"."HUGE_VAL"="KK_SMALL"."HUGE_VAL")
       filter("KK_HUGE"."HUGE_VAL"="KK_SMALL"."HUGE_VAL")
   7 - filter((UPPER("KK_SMALL"."HUGE_VAL") LIKE '%ALU%' AND
UPPER("KK_SMALL"."HUGE_VAL") LIKE '%AL%' AND
              NVL("START_DATE_ACTIVE",SYSDATE@!)<=SYSDATE@! AND


NVL("END_DATE_ACTIVE",SYSDATE@!)>=SYSDATE@!))


Oracle apparently did not join the tables in the order that I
specified - interesting.



And finally, the apparent quickest method of the night, which yielded
hash joins:


SELECT /*+ GATHER_PLAN_STATISTICS LEADING(KK_BIG KK_HUGE) */

  *


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;




>From the 10053 Trace:



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:  3.06  Resp: 3.06  Degree: 0

      Cost_io: 3.00  Cost_cpu: 236657
      Resp_io: 3.00  Resp_cpu: 236657
  Best:: AccessPath: TableScan
         Cost: 3.06  Degree: 1  Resp: 3.06  Card: 0.00  Bytes: 0





SELECT



  *


FROM



  TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));


| Id  | Operation           | Name     | Starts | E-Rows | A-Rows |
A-Time   | Buffers |  OMem |  1Mem | Used-Mem |



|*  1 |  HASH JOIN          |          |      1 |      1 |    446 |
00:00:00.01 |     205 |   878K|   878K| 1133K (0)|
|*  2 |   TABLE ACCESS FULL | KK_SMALL |      1 |      1 |    446 |
00:00:00.01 |       4 |       |       |          |
|*  3 |   HASH JOIN         |          |      1 |   5000 |   5000 |
00:00:00.05 |     201 |   870K|   870K| 1277K (0)|
|   4 |    TABLE ACCESS FULL| KK_BIG   |      1 |   5000 |   5000 |
00:00:00.01 |      17 |       |       |          |
|   5 |    TABLE ACCESS FULL| KK_HUGE  |      1 |  50000 |  50000 |
00:00:00.10 |     184 |       |       |          |





Predicate Information (identified by operation id):





   1 - access("KK_HUGE"."HUGE_VAL"="KK_SMALL"."HUGE_VAL")
   2 - filter((UPPER("KK_SMALL"."HUGE_VAL") LIKE '%ALU%' AND
UPPER("KK_SMALL"."HUGE_VAL") LIKE '%AL%' AND

              NVL("START_DATE_ACTIVE",SYSDATE@!)<=SYSDATE@! AND
NVL("END_DATE_ACTIVE",SYSDATE@!)>=SYSDATE@!))

   3 - access("KK_BIG"."ID"="KK_HUGE"."ID")



This behavior must cause significant headaches for developers who try
to write platform independent code.



Charles Hooper


PC Support Specialist


K&M Machine-Fabricating, Inc.
Received on Fri Apr 06 2007 - 22:21:48 CDT












Original text of this message













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