FAQ
Hi, Listers,

I had a problem and appreicate you help me to understand:

Problem   : suboptimal execution plan using index SMC_IDX4 selected by Oracle
CBO
good index: SMC_MAP_CURRSTAT_NUK(MAP_ID, CURR_STATUS_ID, MAP_VERSION)
bad index : SMC_IDX4(MAP_ID, PREV_STATUS_ID, CURR_STATUS_ID )

Question (refer to 10053 below) : how ix_sel = 0.0017921 is calculated for the
index (SMC_MAP_CURRSTAT_NUK) access path ?
My understanding is
selectivity(MAP_ID,MAP_VERSION,CURR_STATUS_ID)
= selectivity(MAP_ID) * selectivity(MAP_VERSION) *
selectivity(CURR_STATUS_ID)
= 0.012821 * 1 * 0.023256
= 2.9817e-04
Oracle version: 10.2.0.4
SQL-1:
SELECT pieid
FROM SMC
WHERE MAP_ID = 91 AND
MAP_VERSION = 1 AND
CURR_STATUS_ID = 10;

===> from 10053 trace

Table Stats::
Table: SMC  Alias: SMC
#Rows: 299321835  #Blks:  9470379  AvgRowLen:  186.00
-----------------------------------------
BEGIN Single Table Cardinality Estimation
-----------------------------------------
Column (#8): MAP_ID(NUMBER)
AvgLen: 4.00 NDV: 78 Nulls: 0 Density: 0.012821 Min: 1 Max: 144
Column (#9): MAP_VERSION(NUMBER)
AvgLen: 3.00 NDV: 1 Nulls: 0 Density: 1 Min: 1 Max: 1
Column (#7): CURR_STATUS_ID(NUMBER)
AvgLen: 3.00 NDV: 43 Nulls: 0 Density: 0.023256 Min: 0 Max: 97
Table: SMC  Alias: SMC
Card: Original: 299321835  Rounded: 89243  Computed: 89243.24  Non Adjusted:
89243.24

Index: SMC_MAP_CURRSTAT_NUK  Col#: 8 7 9
LVLS: 3  #LB: 752377  #DK: 558  LB/K: 1348.00  DB/K: 204576.00  CLUF:
114153723.00
Index: SMC_IDX4  Col#: 8 6 7
LVLS: 3  #LB: 756804  #DK: 2060  LB/K: 367.00  DB/K: 60536.00  CLUF:
124705545.00

Access Path: index (AllEqRange)
Index: SMC_MAP_CURRSTAT_NUK
resc_io: 205929.00  resc_cpu: 1743632975
ix_sel: 0.0017921  ix_sel_with_filters: 0.0017921
Cost: 206118.49  Resp: 206118.49  Degree: 1

Access Path: index (RangeScan)
Index: SMC_IDX4
resc_io: 46888.00  resc_cpu: 1159030959
ix_sel: 0.012821  ix_sel_with_filters: 2.9815e-04
Cost: 47034.81  Resp: 47034.81  Degree: 1

SQL-2:
SELECT pieid
FROM SMC
WHERE MAP_ID = 91 AND
CURR_STATUS_ID = 10;
In this case, CBO is able to choose better index access path and selectivity
calculation is understandable.
Access Path: index (RangeScan)
Index: SMC_MAP_CURRSTAT_NUK
resc_io: 34264.00  resc_cpu: 288336758
ix_sel: 2.9815e-04  ix_sel_with_filters: 2.9815e-04
Cost: 34295.33  Resp: 34295.33  Degree: 1

Access Path: index (RangeScan)
Index: SMC_IDX4
resc_io: 46888.00  resc_cpu: 1152783475
ix_sel: 0.012821  ix_sel_with_filters: 2.9815e-04
Cost: 47034.13  Resp: 47034.13  Degree: 1

Thanks,

Yu (Denis) Sun
Oracle DBA

## Search Discussions

•  at Oct 20, 2010 at 2:30 pm ⇧
I bet that if you changed
SMC_MAP_CURRSTAT_NUK : --> (MAP_ID, CURR_STATUS_ID, MAP_VERSION)

to
SMC_MAP_CURRSTAT_NUK :--> ( MAP_VERSION,CURR_STATUS_ID, MAP_ID)

it would be a *better* index.

For the first query, Oracle isn't exactly doing the same sort of
operation on the two Indexes.
The AllEqRange on SMC_MAP_CURRSTAT_NUK has a much higher I/O cost than then
RangeScan on SMC_IDX4 and that is why Oracle chooses the latter index.

The expected cardinality is derived from *column* statistics.

Hemant K Chitale
At 09:22 PM Wednesday, Denis wrote:
Hi, Listers,

I had a problem and appreicate you help me to understand:

Problem : suboptimal execution plan using index SMC_IDX4 selected
by Oracle CBO
good index: SMC_MAP_CURRSTAT_NUK(MAP_ID, CURR_STATUS_ID, MAP_VERSION)
bad index : SMC_IDX4(MAP_ID, PREV_STATUS_ID, CURR_STATUS_ID )

Question (refer to 10053 below) : how ix_sel = 0.0017921 is
calculated for the index (SMC_MAP_CURRSTAT_NUK) access path ?
My understanding is
selectivity(MAP_ID,MAP_VERSION,CURR_STATUS_ID)
= selectivity(MAP_ID) * selectivity(MAP_VERSION) *
selectivity(CURR_STATUS_ID)
= 0.012821 * 1 * 0.023256
= 2.9817e-04
Oracle version: 10.2.0.4
SQL-1:
SELECT pieid
FROM SMC
WHERE MAP_ID = 91 AND
MAP_VERSION = 1 AND
CURR_STATUS_ID = 10;
===> from 10053 trace

Table Stats::
Table: SMC Alias: SMC
#Rows: 299321835 #Blks: 9470379 AvgRowLen: 186.00
-----------------------------------------
BEGIN Single Table Cardinality Estimation
-----------------------------------------
Column (#8): MAP_ID(NUMBER)
AvgLen: 4.00 NDV: 78 Nulls: 0 Density: 0.012821 Min: 1 Max: 144
Column (#9): MAP_VERSION(NUMBER)
AvgLen: 3.00 NDV: 1 Nulls: 0 Density: 1 Min: 1 Max: 1
Column (#7): CURR_STATUS_ID(NUMBER)
AvgLen: 3.00 NDV: 43 Nulls: 0 Density: 0.023256 Min: 0 Max: 97
Table: SMC Alias: SMC
Card: Original: 299321835 Rounded: 89243 Computed:

Index: SMC_MAP_CURRSTAT_NUK Col#: 8 7 9
LVLS: 3 #LB: 752377 #DK: 558 LB/K: 1348.00 DB/K:
204576.00 CLUF: 114153723.00
Index: SMC_IDX4 Col#: 8 6 7
LVLS: 3 #LB: 756804 #DK: 2060 LB/K: 367.00 DB/K:
60536.00 CLUF: 124705545.00
Access Path: index (AllEqRange)
Index: SMC_MAP_CURRSTAT_NUK
resc_io: 205929.00 resc_cpu: 1743632975
ix_sel: 0.0017921 ix_sel_with_filters: 0.0017921
Cost: 206118.49 Resp: 206118.49 Degree: 1

Access Path: index (RangeScan)
Index: SMC_IDX4
resc_io: 46888.00 resc_cpu: 1159030959
ix_sel: 0.012821 ix_sel_with_filters: 2.9815e-04
Cost: 47034.81 Resp: 47034.81 Degree: 1

SQL-2:
SELECT pieid
FROM SMC
WHERE MAP_ID = 91 AND
CURR_STATUS_ID = 10;
In this case, CBO is able to choose better index access path and
selectivity calculation is understandable.
Access Path: index (RangeScan)
Index: SMC_MAP_CURRSTAT_NUK
resc_io: 34264.00 resc_cpu: 288336758
ix_sel: 2.9815e-04 ix_sel_with_filters: 2.9815e-04
Cost: 34295.33 Resp: 34295.33 Degree: 1

Access Path: index (RangeScan)
Index: SMC_IDX4
resc_io: 46888.00 resc_cpu: 1152783475
ix_sel: 0.012821 ix_sel_with_filters: 2.9815e-04
Cost: 47034.13 Resp: 47034.13 Degree: 1
Thanks,

Yu (Denis) Sun
Oracle DBA
Hemant K Chitale

http://hemantoracledba.blogspot.com
http://hemantscribbles.blogspot.com
http://web.singnet.com.sg/~hkchital
•  at Oct 20, 2010 at 3:40 pm ⇧
Hemank,

Thanks for the suggestion about the fix. At this moment, I am more interested in
how the selectivity is calculated. Inspired by some google results, I think I
probably find the answer. Here is the stats from dba_indexes:
From DBA_INDEXES
~~~~~~~~~~~~~~~~~
.INDEX_NAME                   : SMC_MAP_CURRSTAT_NUK
.INDEX_TYPE                   : NORMAL
.LOGGING                      : NO
.BLEVEL                       : 3
.LEAF_BLOCKS                  : 752377
.DISTINCT_KEYS                : 558
..LAST_ANALYZED                : 10/19/2010 23:41:42
.DEGREE                       : 1
....

It seems the selectivity comes from : 1/DISTINCT_KEYS = 1/558=0.001792, this
matches perfectly with 10053 trc ix_sel :

Access Path: index (AllEqRange)
Index: SMC_MAP_CURRSTAT_NUK
resc_io: 205929.00  resc_cpu: 1743632975
ix_sel: 0.0017921  ix_sel_with_filters: 0.0017921
Cost: 206118.49  Resp: 206118.49  Degree: 1
Now the question is why or what controls Oracle CBO to choose index stats info
to calculate the composite index selectivity?

This db was upgraded from 9i not long time ago, I did not remember we had same
issue in 9i. Unfortunately it is not easy for me to find a 9i db and exp/imp
this 50GB table to test. Could it be some hidden paramter change that controls
the behavior?

________________________________
From: Hemant K Chitale
To: denis.sun_at_yahoo.com; oracle-l@freelists.org
Sent: Wed, October 20, 2010 10:30:53 AM
Subject: Re: composite index selectivity question

I bet that if you changed
SMC_MAP_CURRSTAT_NUK : -->  (MAP_ID, CURR_STATUS_ID, MAP_VERSION)
to
SMC_MAP_CURRSTAT_NUK :-->  ( MAP_VERSION,CURR_STATUS_ID, MAP_ID)
it would be a *better* index.

For the first query, Oracle isn't exactly doing the same sort of
operation on the two Indexes.
The AllEqRange on SMC_MAP_CURRSTAT_NUK  has a much higher I/O cost than then
RangeScan on SMC_IDX4 and that is why Oracle chooses the latter index.

The expected cardinality is derived from *column* statistics.

Hemant K Chitale
At 09:22 PM Wednesday, Denis wrote:
Hi, Listers,

I had a problem and appreicate you help me to understand:

Problem  : suboptimal execution plan using index SMC_IDX4 selected
by Oracle CBO
good index: SMC_MAP_CURRSTAT_NUK(MAP_ID, CURR_STATUS_ID, MAP_VERSION)
bad index : SMC_IDX4(MAP_ID, PREV_STATUS_ID, CURR_STATUS_ID )

Question (refer to 10053 below) : how ix_sel = 0.0017921 is
calculated for the index (SMC_MAP_CURRSTAT_NUK) access path ?
My understanding is
selectivity(MAP_ID,MAP_VERSION,CURR_STATUS_ID)
= selectivity(MAP_ID) * selectivity(MAP_VERSION) *
selectivity(CURR_STATUS_ID)
= 0.012821 * 1 * 0.023256
= 2.9817e-04
Oracle version: 10.2.0.4
SQL-1:
SELECT pieid
FROM SMC
WHERE MAP_ID = 91 AND
MAP_VERSION = 1 AND
CURR_STATUS_ID = 10;

===> from 10053 trace

Table Stats::
Table: SMC  Alias: SMC
#Rows: 299321835  #Blks:  9470379  AvgRowLen:  186.00
-----------------------------------------
BEGIN Single Table Cardinality Estimation
-----------------------------------------
Column (#8): MAP_ID(NUMBER)
AvgLen: 4.00 NDV: 78 Nulls: 0 Density: 0.012821 Min: 1 Max: 144
Column (#9): MAP_VERSION(NUMBER)
AvgLen: 3.00 NDV: 1 Nulls: 0 Density: 1 Min: 1 Max: 1
Column (#7): CURR_STATUS_ID(NUMBER)
AvgLen: 3.00 NDV: 43 Nulls: 0 Density: 0.023256 Min: 0 Max: 97
Table: SMC  Alias: SMC
Card: Original: 299321835  Rounded: 89243  Computed:

Index: SMC_MAP_CURRSTAT_NUK  Col#: 8 7 9
LVLS: 3  #LB: 752377  #DK: 558  LB/K: 1348.00  DB/K:
204576.00  CLUF: 114153723.00
Index: SMC_IDX4  Col#: 8 6 7
LVLS: 3  #LB: 756804  #DK: 2060  LB/K: 367.00  DB/K:
60536.00  CLUF: 124705545.00

Access Path: index (AllEqRange)
Index: SMC_MAP_CURRSTAT_NUK
resc_io: 205929.00  resc_cpu: 1743632975
ix_sel: 0.0017921  ix_sel_with_filters: 0.0017921
Cost: 206118.49  Resp: 206118.49  Degree: 1

Access Path: index (RangeScan)
Index: SMC_IDX4
resc_io: 46888.00  resc_cpu: 1159030959
ix_sel: 0.012821  ix_sel_with_filters: 2.9815e-04
Cost: 47034.81  Resp: 47034.81  Degree: 1

SQL-2:
SELECT pieid
FROM SMC
WHERE MAP_ID = 91 AND
CURR_STATUS_ID = 10;
In this case, CBO is able to choose better index access path and
selectivity calculation is understandable.
Access Path: index (RangeScan)
Index: SMC_MAP_CURRSTAT_NUK
resc_io: 34264.00  resc_cpu: 288336758
ix_sel: 2.9815e-04  ix_sel_with_filters: 2.9815e-04
Cost: 34295.33  Resp: 34295.33  Degree: 1

Access Path: index (RangeScan)
Index: SMC_IDX4
resc_io: 46888.00  resc_cpu: 1152783475
ix_sel: 0.012821  ix_sel_with_filters: 2.9815e-04
Cost: 47034.13  Resp: 47034.13  Degree: 1
Thanks,

Yu (Denis) Sun
Oracle DBA
Hemant K Chitale

http://hemantoracledba.blogspot.com
http://hemantscribbles.blogspot.com
http://web.singnet.com.sg/~hkchital

## Related Discussions

Discussion Overview
 group oracle-l categories oracle posted Oct 20, '10 at 1:22p active Oct 20, '10 at 3:40p posts 3 users 2 website oracle.com

### 2 users in discussion

Content

People

Support

Translate

site design / logo © 2022 Grokbase