Re: Help me read my 10053 trace file

Sorry, the query is like this:
WHERE ln.metro_id = i_metro_id AND

                                l.metro_id = :b1 ANd
                                eld.metro_id(+) = :b1 AND
            ln.status_desc = 'ACTIVE' AND
            ln.daset_id = ld.daset_id (+) AND
            ln.sect_id = ld.sect_id (+) AND
            ln.life_id = ld.life_id (+) AND
            ln.version_id = ld.version_id (+) AND
            ln.daset_id = l.daset_id AND
            ln.sect_id = l.sect_id AND
            ln.life_id = l.life_id AND
            ln.version_id = l.version_id AND
            l.status_desc = 'ACTIVE' AND
            l.ramp = 'Y' AND
            l.daset_id = eld.daset_id (+) AND
            l.sect_id = eld.sect_id (+) AND
            l.life_id = eld.life_id (+) AND
            l.version_id = eld.version_id (+) AND
            eld.life_id IS NULL

Thank you very much , Jonathan.

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:ar5p8g$ud$1$8300dec7_at_news.demon.co.uk...
>
> Life gets hard with partitioned tables and
> bitmap indexes, let alone combined - but
> I think the following MAY be valid:
>
> a) 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
>
> b) 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
> >
> >
> >
> >
>
>
Received on Sat Nov 16 2002 - 23:44:45 CST