RE: Optimizer question

Stefan,

I did say "at least" 11.1.0.7 - which means it may have been true earlier.
One of the slides I have when talking about the CBO includes a demonstration which finally ended up being labelled as 10.2.0.5 where the optimizer would use NDV for the number of rowids selected from the index, then fall back to the separate column stats for the number of rows that this would fetch from the table:

Execution Plan (10.2.0.5)                       Rows    Cost
|   0 | SELECT STATEMENT            |       |    10 |   168 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |    10 |   168 |
|*  2 |   INDEX RANGE SCAN          | T1_I1 |   200 |    25 |

Note - no extra predicates at operation 1, but the Rows drops.
It's possible that patches or back-ports appeared for this eventually.



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

________________________________________
From: Stefan Koehler [contact_at_soocs.de]
Sent: 06 April 2016 11:10
To: Jonathan Lewis; oracle-l
Subject: RE: Optimizer question

Hello Jonathan,
are you sure about 11.1.0.7? MOS ID #872406.1 (sub-point "Other examples") states 10.2.0.4 as well, but could not get it working on 10.2.0.5 by myself
right now (no col group stats are considered). Just found a 12.1.0.2 database and the issue is there as well as you mentioned. So the only case where
i could get it working was 11.2.0.3.6. By the way here is my model which worked with 11.2.0.3.6:

-----------------8<----------------------
create table t (a number, b number);

begin
for x in 1 .. 10
loop
  for y in 1 .. 10000
  loop
    insert into t values (NULL,x);
  end loop;
end loop;
commit;
end;
/

create index t_i on t(a,b);

exec dbms_stats.gather_table_stats(NULL,'T');

variable a1 number;
variable a2 number;
exec :a1 := 1;
exec :a2 := 3;
select * from t where a = :a1 and b = :a2;
-----------------8<----------------------

However IMHO the solution to Petr's issue would be extended stats (so that no index stats are considered), histograms on single column (if this makes
sense depends on how the data is scattered in column b) or just use the "_optimizer_extended_stats_usage_control" parameter like demonstrated :)

Best Regards
Stefan Koehler

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

> Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> hat am 6. April 2016 um 11:44 geschrieben:
>
> 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
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
--