Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: how to identify unused indexes
Scott Dudley <scott_at_telesoft.com> wrote:
>
>is there a way to identify indexes that aren't accessed for
>select/update? trying to identify superfluous indexes in a
database of
>some 300+ tables without having to go through hundreds of
thousands of
>lines of code.
>
>thanks.
>
>Microsoft gives you Windows but Unix gives you the whole house!
>
I do not believe there is any built-in way of doing this. One
thing you might try is writing code that will periodically
sample the buffer pool for what objects are there. You can use
the v$cache view which normally exists only on parallel server
installs, but the script catparr can be ran on non-ops installs
to create this view. By storing all indexes found in a work
table with a unique key on the owner, index_name you should be
able to have just one entry per index so you do not need to
generate much data. I would use dbms_job to run the task.
It is not perfect but this should allow you to identify most of your used objects so it should allow you to narrow your list of possible obsolete indexes way down.
Got questions? Get answers over the phone at Keen.com.
Up to 100 minutes free!
http://www.keen.com
Received on Fri Aug 11 2000 - 00:00:00 CDT