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

On 4 Apr 2007 11:08:09 -0700, "Kevin Kirkpatrick" <kvnkrkptrck_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. 2 you probably don't have any proper histograms. The default is to calculate histograms with only 2 buckets. 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.

-- 
Sybrand Bakker
Senior Oracle DBA