Re: rebuild indexes

Hi David,

No it is not a bug, it's not even a feature, it's the way it works. Blks_gets_per_access is basically an implicit measure for the selectivity of the index (and the index_histogram table that still gets filled is a far better measure) as it roughly reflects the number of records per key. It doesn't change when you rebuild your index as you already have noticed. What I try to is look at
the height of an index, usually 3, and if it is 4 and it has been 3 you probably need to rebuild it.
the number of blocks allocated in relation with the btree_space (total size of the btree) and the used_space. The pct_used is nothing more than used_space/btree_space. For me, when I see the pct_used decrease (and of course the index segment could have been overallocated from the start), this means the overhead in the btree increases. Personally, I have never completely understood the index statistics in user/dba_indexes, and I know monitoring pct_used helps. In ordinary cases (blocksize 2048) the maximum number of bytes used per block is 1889 and the max pct_used 89 percent.

Hth,

Sybrand Bakker, Oracle DBA

David Spaisman <david.spaisman_at_compaq.com> wrote in message news:7lislb$c88$1_at_mailint03.im.hou.compaq.com...
> Hello:
>
> I have been told that the two parameters -- blks_gets_per_access and
> pct_ued -- were key indicators on whether or not an index should be
rebuilt.
>
> My environment for this testing is Oracle 8.0.4.00 on NT 4.0 sp4.
>
> In a test environment, I have rebuild indexes that exceed
> blks_gets_per_access(> 5) .
>
> I would like to clarify my understanding of how this parameter(and
Pct_used)
> from index_stats work.
>
> If I rebuild the index or drop and recreate the table(and then the index)
> this parameter --blks_gets_per_access-- does not change. I thought that if
> the table was rebuilt the parameter would be initialized. That does not
> appear to be the case. In a specifc case, the blk_gets_per_access is 2269
> and it didn't change regardless of whether I rebuilt the index or
> dropped/recreated the table and index. Restarting the datbase/instance
> ddin't change the parameter value as well.
>
> Is this the way this parameter works or did I do something wrong? Is this
> abug possbily?
>
> Thanks.
>
> DAvid Spaisman
>
>
Received on Fri Jul 02 1999 - 13:47:09 CDT