Re: index never used

Hi,

monitoring indexes can be done with 9i. If you still want to use version 8, you can query the sql-statements from v$sql and afterwards let them explained by explain plan command.

Be careful, when dropping indexes:
Access plans can change with every compute statistics command! And if there are ad hoc queries, you won't know, which indexed may be used in the future.

Best regards,
Andreas

vslabs_at_onwe.co.za (Billy Verreynne) wrote in message news:<3c610ad3.2351706059_at_news.saix.net>...
> franz.mueller_at_orbis.de (franz mueller) wrote:
>
> >I have a DB, that has a huge amount of indices. I am would bet that
> >80% of them have been added by people not knowing what they are doing,
> >and therefore are never used. How can I monitor those indices. I use
> >8.x
>
> Franz, users havd no place adding indexes, or designing tables, in the
> first place. I would even say that 80% of programmers are clueless in
> this regard - they simply view it as a fast access path. The more
> paths, the merrier.
>
> [rant]
> I am not sure how to fix this problem. I sit with a similar problem. I
> have ranted and raved about it. I tried to explain in long e-mails. I
> offered my assistance. A spontanous round of indeference meets each
> attempt.
>
> Having a table where the total indexes space used for that table is
> _larger_ than the table itself (and this is a billing system table
> mind you - not a data warehouse or DSS table). Where the set of 5 or
> so indexes, index the _same_ set columns in different sequences and
> combinations... but still, it is "their" code, "their" database and
> "their" application. Although they just live to point the finger to
> the DBA and Oracle when it comes to performance issues.
>
> One thing that is always missed when people post here about what
> qualifications a DBA should have, what a DBA's job description is..
> the number one thing they forget.. Dealing with political and office
> bullshit when it comes to DBA duties.
> [/rant]
Received on Wed Feb 06 2002 - 08:19:26 CST