Re: Very High BLKS_GETS_PER_ACCESS, Clustering Factor?

On 26 May 2003 08:37:02 -0700, basis_consultant_at_hotmail.com (SAP BASIS Consultant) wrote:

>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

Considering 225000 records as a very large number is definitely a joke.
The index in question must be a non-unique one, otherwise the blks_gets_per_access will always be the level of the index plus 1. If you compare the clustering factor and the blks_get_per_access for an unique index to each other you will see there is exactly *NO* relationship.
The value of 241 rather points to an index which has a high number of records per key, and shouldn't be considered too selective. You'd better determine the selectivity as number of distinct values / number of records

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Mon May 26 2003 - 12:05:12 CDT