Re: Very High BLKS_GETS_PER_ACCESS, Clustering Factor?

Hi,

Thanks to all who took the time to answer the question, especially to Dave Hau for providing the link to 'Asktom.oracle.com'. You are correct,
the number of DISTINCT_KEYS is fairly small (70 to 90 or so for a couple
of the indexes). Like somebody mentioned I did exaggerate in stating that 225000 for LF_ROWS is a very large number; I meant large in comparison with DEL_LF_ROWS.

Thanks,
DF

"Dave Hau" <davehau-no-spam-123_at_no-spam.netscape.net> wrote in message news:<pxxAa.569$Jj4.111_at_newssvr19.news.prodigy.com>...
> You left out the DISTINCT_KEYS column of the index_stats view, otherwise we
> could take a look at how selective your indexes are. Consider using bitmap
> indexes for the non-selective indexes you have, if your usage scenario does
> not involve a lot of concurrent modification to the tables, e.g. bitmap
> index would be a good idea for a data warehouse. This will bring down the
> BLKS_GETS_PER_ACCESS for your non-selective indexes.
>
> Have a look at this:
>
> http://asktom.oracle.com/pls/ask/f?p=4950:8:217283730323053051::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1277600641422,
>
> Cheers,
> Dave
>
>
>
>
>
> "SAP BASIS Consultant" <basis_consultant_at_hotmail.com> wrote in message
> news:dd2036f3.0305260737.524b6331_at_posting.google.com...
> > Hi,
> >
> > On a v8.1.5 (AIX) system, there are some indexes, about 40MB
> > or 50MB in size, for which INDEX_STATS (After analysis) shows
> > the following:
> >
> > PCT_USED: 90% (PCTFREE is 10%)
> > LF_ROWS: 225000 (Or some other very large number)
> > DEL_LF_ROWS: 0
> > HEIGHT: 3
> > BLKS_GETS_PER_ACCESS: 241 (542 in another and 5749 in another!).
> >
> > I rebuilt one of them, reanalyzed it, and the numbers are the same.
> >
> > Aside from BLKS_GETS_PER_ACCESS, the index seems in very good shape.
> >
> > However, BLKS_GETS_PER_ACCESS is very poor. Is this a case of
> > very poor clustering (In which I cannot do much aside from
> > reorganizing the table), or should I look into somehing else?
> >
> >
> >
> > Thanks in Advance,
> > DF
Received on Tue May 27 2003 - 05:44:56 CDT