Re: Optimizer question
Date: Wed, 6 Apr 2016 09:44:16 -0400
Message-ID: <CAAnDMS1Sz-n7xHJm3t6cmUgf4s1OxX6KWR2sq73V8TPxP+_YLw_at_mail.gmail.com>
And I think you are right ;-)
On my defense my memory (and my MOS search skills) works very bad before coffee!!!
On Wed, Apr 6, 2016 at 9:35 AM, Timur Akhmadeev <timur.akhmadeev_at_gmail.com> wrote:
> 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:44:16 CEST