Re: Optimizer question
Date: Wed, 6 Apr 2016 16:35:44 +0300
Message-ID: <CACGsLC+SVxr_tuCf4Ly0HOsVaW9W_Qx=YM3GC9hZqedoAkWsJw_at_mail.gmail.com>
Wow you can quote bug numbers by memory! :-) I think it's #5040753 and I even found an old OTN thread about it <https://community.oracle.com/thread/881863> 5868560 is another bug fix on top of 5040753 as I understand
On Wed, Apr 6, 2016 at 2:49 PM, Mauro Pagano <mauro.pagano_at_gmail.com> wrote:
> 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
>>
>>
>>
>
-- Regards Timur Akhmadeev -- http://www.freelists.org/webpage/oracle-lReceived on Wed Apr 06 2016 - 15:35:44 CEST