Re: composite index selectivity question

From: Denis <denis.sun_at_yahoo.com>
Date: Wed, 20 Oct 2010 08:40:56 -0700 (PDT)
Message-ID: <293229.95670.qm_at_web57204.mail.re3.yahoo.com>


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 <hkchital_at_singnet.com.sg>
To: denis.sun_at_yahoo.com; oracle-l_at_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


      
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Oct 20 2010 - 10:40:56 CDT

Original text of this message