Help me read my 10053 trace file

From: charlie cs <cs3526_at_ureach.com>
Date: Sat, 16 Nov 2002 04:30:32 GMT
Message-ID: <IRjB9.2017$aB1.1538@nwrddc02.gnilink.net>

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 Received on Fri Nov 15 2002 - 22:30:32 CST