Re: Very High BLKS_GETS_PER_ACCESS, Clustering Factor?

From: Dave Hau <davehau-no-spam-123_at_no-spam.netscape.net>
Date: Mon, 26 May 2003 23:35:49 GMT
Message-ID: <pxxAa.569$Jj4.111@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 Mon May 26 2003 - 18:35:49 CDT