Re: Optimizer question
Date: Wed, 6 Apr 2016 07:49:38 -0400
Message-ID: <CAAnDMS1UJXaquDwRJQoQw-kPr8BqWsek503Z3RnD4iOQpE06Xw_at_mail.gmail.com>
IIRC in 10.2 the fix was disabled by default and needed to be enabled, it should be 5868560.
Also from the bug text it seems the fix was never included in 11g due to CG being available there.
On Wed, Apr 6, 2016 at 6:13 AM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:
>
> 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
> >
> >
> >
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Apr 06 2016 - 13:49:38 CEST