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 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
@jloracle
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Stefan Koehler [contact@xxxxxxxx]
Sent: 06 April 2016 09:00
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 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: @OracleSK
Petr Novak <Petr.Novak@xxxxxxxxxxxx> 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
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
