RE: Optimizer question

From: Stefan Koehler <contact_at_soocs.de>
Date: Wed, 6 Apr 2016 12:10:38 +0200 (CEST)
Message-ID: <1319852707.422512.1459937438550.JavaMail.open-xchange_at_app04.ox.hosteurope.de>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Apr 06 2016 - 12:10:38 CEST