RE: Optimizer question

Stefan,

The same bad estimate appears on 12.1.0.2 - I'm a little surprised that you got the "good" behaviour you did from 11.2.0.3 - the optimizer has been able to use the index NDV like a column group since at least 11.1.0.7.

I think I've written this one up somewhere - but can't find it. The problem is that when you have a column group that can be used Oracle seems to "forget" to take note of the num_nulls of the individual columns and doesn't factor them in to the calculation.


Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

________________________________________
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Stefan Koehler [contact_at_soocs.de]
Sent: 06 April 2016 09:00
To: Petr.Novak_at_trivadis.com; oracle-l
Subject: Re: Optimizer question

Hello Petr,
just rebuild a short model of your issue on 11.2.0.3.6 and 12.1.0.1 as i got no 11.2.0.4.

11.2.0.3.6
-------------------------------
Table Stats::
  Table:  T  Alias:  T
    #Rows: 100000  #Blks:  244  AvgRowLen:  3.00  ChainCnt:  0.00
Index Stats::
  Index: T_I  Col#: 1 2
    LVLS: 1  #LB: 210  #DK: 10  LB/K: 21.00  DB/K: 16.00  CLUF: 161.00     <<< The same
…
  Single Table Cardinality Estimation for T[T]
  Column (#1): A(
    AvgLen: 22 NDV: 0 Nulls: 100000 Density: 0.000000 Min: 0 Max: 0
  Column (#2): B(
    AvgLen: 3 NDV: 10 Nulls: 0 Density: 0.100000 Min: 1 Max: 10
  ColGroup (#1, Index) T_I
    Col#: 1 2    CorStregth: 0.00
  ColGroup Usage:: PredCnt: 2  Matches Full: #1  Partial:  Sel: 0.0000        <<< ColGroup #1 used and "correct" cardinality
  Table: T  Alias: T
    Card: Original: 100000.000000  Rounded: 1  Computed: 0.00  Non Adjusted: 0.00

12.1.0.1
-------------------------------
Table Stats::
  Table:  T  Alias:  T
  #Rows: 100000  #Blks:  244  AvgRowLen:  3.00  ChainCnt:  0.00
Index Stats::
  Index: T_I  Col#: 1 2
    LVLS: 1  #LB: 210  #DK: 10  LB/K: 21.00  DB/K: 16.00  CLUF: 161.00        <<< The same
…
  Single Table Cardinality Estimation for T[T]
  Column (#1): A(NUMBER)
    AvgLen: 22 NDV: 0 Nulls: 100000 Density: 0.000000
  Column (#2): B(NUMBER)
    AvgLen: 3 NDV: 10 Nulls: 0 Density: 0.100000 Min: 1.000000 Max: 10.000000
  ColGroup (#1, Index) T_I
    Col#: 1 2    CorStregth: 0.00
  ColGroup Usage:: PredCnt: 2  Matches Full: #1  Partial:  Sel: 0.1000            <<< ColGroup #1 used but "wrong" cardinality
  Table: T  Alias: T
    Card: Original: 100000.000000  Rounded: 10000  Computed: 10000.00  Non Adjusted: 10000.00


+10.2.0.4 (if i remember correctly) makes use of composite indexes for such estimates if possible. In addition column group stats and histograms on
the equality predicates that match the index columns may over-rule the index stats.


However based on my short re-model it looks like bug #20486828 which seems to affect >= 11.2.0.3.12, but not 12.1.0.2 (unfortunately i have no
12.1.0.2 on my mobile lab right now). This would also fit to my working 11.2.0.3.6 case.


12.1.0.1 with "_optimizer_extended_stats_usage_control = 254"
-------------------------------
Table Stats::
  Table:  T  Alias:  T
  #Rows: 100000  #Blks:  244  AvgRowLen:  3.00  ChainCnt:  0.00
Index Stats::
  Index: T_I  Col#: 1 2
    LVLS: 1  #LB: 210  #DK: 10  LB/K: 21.00  DB/K: 16.00  CLUF: 161.00
…
  Single Table Cardinality Estimation for T[T]
  Column (#1): A(NUMBER)
    AvgLen: 22 NDV: 0 Nulls: 100000 Density: 0.000000
  Column (#2): B(NUMBER)
    AvgLen: 3 NDV: 10 Nulls: 0 Density: 0.100000 Min: 1.000000 Max: 10.000000
  Table: T  Alias: T
    Card: Original: 100000.000000  Rounded: 1  Computed: 0.00  Non Adjusted: 0.00     <<< "Correct" cardinality again without col groups


Best Regards
Stefan Koehler

Freelance Oracle performance consultant and researcher
Homepage: http://www.soocs.de
Twitter: _at_OracleSK

> Petr Novak <Petr.Novak_at_trivadis.com> hat am 6. April 2016 um 05:36 geschrieben:
>
>  Hallo,
>
>  query on 11.2.0.4 DB
>
>  Table T has 1000000 rows, column A has all values Null, column B has 10 different values , no Nulls.
>
>  Index on T(A,B) is defined, num_distinct for index is 10.
>
>  select * from T where A=?                             , where ? is not Null uses column statistics, expects 1 row, makes index scan
>  select * from T where A=? and B=?                , both not Null uses index statistics, expects 100000 rows, makes table full scan.
>
>  Why for the second query optimizer switched from using column statistics to index statistics ? It is some bug ? How to get correct plan for the
> second query ?
>
>  Best Regards,
>  Petr
--
http://www.freelists.org/webpage/oracle-l


--