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@xxxxxxxxx> 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@xxxxxxxxxxxxxxxxxx> 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
@jloracle
________________________________________
From: Stefan Koehler [contact@xxxxxxxx]
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: @OracleSK
Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx> hat am 6. April 2016 um11:44 geschrieben:
you got the "good" behaviour you did from 11.2.0.3 - the optimizer has been
Stefan,
The same bad estimate appears on 12.1.0.2 - I'm a little surprised that
able to use the index NDV like a column group since at least 11.1.0.7.problem is that when you have a column group that can be used Oracle seems
I think I've written this one up somewhere - but can't find it. The
to
"forget" to take note of the num_nulls of the individual columns anddoesn't factor them in to the calculation.
behalf of Stefan Koehler [contact@xxxxxxxx]
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on
Sent: 06 April 2016 09:00i got no 11.2.0.4.
To: Petr.Novak@xxxxxxxxxxxx; 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
same
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
…ColGroup #1 used and "correct" cardinality
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 <<<
Table: T Alias: T0.00
Card: Original: 100000.000000 Rounded: 1 Computed: 0.00 Non Adjusted:
same
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
…10.000000
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:
ColGroup (#1, Index) T_IColGroup #1 used but "wrong" cardinality
Col#: 1 2 CorStregth: 0.00
ColGroup Usage:: PredCnt: 2 Matches Full: #1 Partial: Sel: 0.1000 <<<
Table: T Alias: TAdjusted: 10000.00
Card: Original: 100000.000000 Rounded: 10000 Computed: 10000.00 Non
such estimates if possible. In addition column group stats and histograms on
+10.2.0.4 (if i remember correctly) makes use of composite indexes for
the equality predicates that match the index columns may over-rule theindex stats.
seems to affect >= 11.2.0.3.12, but not 12.1.0.2 (unfortunately i have no
However based on my short re-model it looks like bug #20486828 which
12.1.0.2 on my mobile lab right now). This would also fit to my working11.2.0.3.6 case.
10.000000
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:
Table: T Alias: T0.00 <<< "Correct" cardinality again without col groups
Card: Original: 100000.000000 Rounded: 1 Computed: 0.00 Non Adjusted:
geschrieben:
Best Regards
Stefan Koehler
Freelance Oracle performance consultant and researcher
Homepage: http://www.soocs.de
Twitter: @OracleSK
Petr Novak <Petr.Novak@xxxxxxxxxxxx> hat am 6. April 2016 um 05:36
10 different values , no Nulls.
Hallo,
query on 11.2.0.4 DB
Table T has 1000000 rows, column A has all values Null, column B has
statistics, expects 1 row, makes index scan
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 100000 rows, makes table full scan.select * from T where A=? and B=? , both not Null uses index
statistics to index statistics ? It is some bug ? How to get correct plan
Why for the second query optimizer switched from using column
for the
--second query ?--
Best Regards,
Petr
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
//www.freelists.org/webpage/oracle-l