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

  • Hemant K Chitale 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:
    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
    Hemant K Chitale

    http://hemantoracledba.blogspot.com
    http://hemantscribbles.blogspot.com
    http://web.singnet.com.sg/~hkchital
  • Denis 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:
    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
    Hemant K Chitale

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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedOct 20, '10 at 1:22p
activeOct 20, '10 at 3:40p
posts3
users2
websiteoracle.com

2 users in discussion

Denis: 2 posts Hemant K Chitale: 1 post

People

Translate

site design / logo © 2022 Grokbase