Re: Help me read my 10053 trace file

Life gets hard with partitioned tables and bitmap indexes, let alone combined - but I think the following MAY be valid:

1. You seem to be querying the data using the first column of a two-column bitmap index, and Oracle thinks that you will be accessing half the data, based on the indexed column. Note that the 134 cost is half of 268 leaf blocks for that index. Note also that the index selectivity (and table selectivity for that index) is 0.5
2. The computed cardinality may be irrelevant to this index - this is the total number of rows that Oracle expects to get - but this number is (I assume) affected by predicates which are on columns not in the index.

If the selectivity from the index to the table is 0.5, then I would expect Oracle to work out that the table block hits will be AT LEAST 0.5 * number of blocks in table (viz 7839/2), but the figure is usually higher, based on the clustering factor, rather than blocks in table.

However, the clustering_factor for bitmap indexes seems to have nothing to do with the clustering factor for b-tree indexes, I've found recently that it seems to match the number of distinct bitmap values. Nevertheless, there must be some data lurking somewhere which has made your total cost

    134 + 5089
rather than

    134 + 3920
But in either case, this is larger than the 1190 cost of tablescan.

In passing - the thing that really baffles me is the index 3905350 - with 5M data blocks per key - when there are only 5M rows in the table, and it's a composite index. The only thing I can guess is that this involves the partitioning key so something funny has happened to it.

Also, in the endless quest for understanding, do you have any idea why two of your indexes appear to have no columns ?

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA__________November 19/21 (Dallas)
____England______January 21/23


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html





charlie cs wrote in message ...


>I am trying to understand why my bitmap index was rejected.

>Here is the trace file from 10053 event

>

>***********************

>Table stats    Table: LIFE   Alias:  L

>  (Using composite stats)

>  TOTAL ::  CDN: 5485935  NBLKS:  7839  TABLE_SCAN_CST: 1190

AVG_ROW_LEN:


>182

>-- Index stats

>  INDEX#: 3906609  COL#: 6 16

>    USING COMPOSITE STATS

>    TOTAL ::  LVLS: 1   #LB: 268  #DK: 2  LB/K: 10  DB/K: 11  CLUF:

293


>  INDEX#: 3905350  COL#: 1 2 3 4 5

>    USING COMPOSITE STATS

>    TOTAL ::  LVLS: 2   #LB: 23411  #DK: 5485935  LB/K: 1  DB/K: 1

CLUF:


>950571

>  INDEX#: 3905296  COL#:

>    USING COMPOSITE STATS

>    TOTAL ::  LVLS: 1   #LB: 25  #DK: 100  LB/K: 1  DB/K: 1  CLUF:

800


>  INDEX#: 3905332  COL#:

>    USING COMPOSITE STATS

>    TOTAL ::  LVLS: 1   #LB: 25  #DK: 100  LB/K: 1  DB/K: 1  CLUF:

800


>***********************

>***************************************

>SINGLE TABLE ACCESS PATH

>Column:   METRO_ID  Col#: 5      Table: LIFE   Alias:  L

>    NDV: 15        NULLS: 0         DENS: 6.6667e-02 LO:  1  HI: 48

>Column: STATUS_DES  Col#: 16     Table: LIFE   Alias:  L

>    NDV: 1         NULLS: 0         DENS: 1.0000e+00

>Column:       RAMP  Col#: 6      Table: LIFE   Alias:  L

>    NDV: 2         NULLS: 0         DENS: 5.0000e-01

>  TABLE: LIFE     ORIG CDN: 5485935  CMPTD CDN: 182865

>  Access path: tsc  Resc:  1190  Resp:  1190

>  Access path: index (equal)

>      INDEX#: 3906609  TABLE: LIFE

>      CST: 134  IXSEL:  5.0000e-01  TBSEL:  5.0000e-01

>******** Bitmap access path rejected ********

>Cost: 5223  Selectivity: 0

>Not believed to be index-only.

>  BEST_CST: 1190.00  PATH: 2  Degree:  1

>***************************************

>

>why the bitmap index cost is so high?

>

>my understanding is, in addition of the index scan, which is 134, we

need to


>access some table blocks individually.  And the table access should

be


>(CMPTD CDN/ORIG CDN)*table blocks

>which is (182865/5485935)*7839 = 261.

>So the total cost for bitmap index should be 134+261=395.

>

>Could somebody tell me how oracle managed to get an outrageous cost

of 5223?


>

>Thanks

>

>

>

>