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, 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_C00867186Cardinality: 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
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
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" *****************************
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 ***************************************
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 ***************************************
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 ***************************************
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: 1164993Join 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
Cost_io: 533.00 Cost_cpu: 154256449 Resp_io: 533.00 Resp_cpu: 154256449 Access Path: index (UniqueScan)
NL Join: Cost: 23.90 Resp: 23.90 Degree: 1 Cost_io: 23.00 Cost_cpu: 1312056 Resp_io: 23.00 Resp_cpu: 1312056
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: 1312056Join Card: 0.00 = outer (15.63) * inner (50000.00) * sel (4.0002e-10)
<snip>
Number of join permutations tried: 6
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
+-----------------------------------+ | 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 | | ----------------------------------------------------- +-----------------------------------+
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