Re: Auditing Index usage

Iain Knowles wrote:
>
> Hi
>
> Does anyone know if it is possible to audit index usage on 8i.
>
> I use a database, that has 6 main tables. The biggest table has approx
> 48million rows with approx 90 columns. There are other tables with between
> 18M and 30M rows. I have been asked to index every column in the six main
> tables. In my opinion less than 5% of the indexes would ever be used.
>
> Is it possible to create a record of index usage that would prove my point.
> eg date of index last use, frequency of use etc
>
> These indexes, as you can imagine are taking up an awful lot of space.
>
> cheers
>
> Iain

Once they are on, you can regularly query X$BH to look at the buffer cache. Link that to OBJ$ to determine if any index blocks are in the cache. If they are, then they have been used at least once.

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse