The drawback to this is that an index will be 'used'
if you insert a row into a table. In fact, given the
general nature of indexes, you could find that a
redundant index has more blocks pulled into the cache
than a useful index if you insert a batch of records.
--
Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
Mark D Powell wrote in message
<1a710ddb.074bfaf3_at_usw-ex0102-015.remarq.com>...
>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.
>
>
>
>
>
>-- Mark D. Powell -- The only advice that counts is the advice that
>you follow so follow your own advice. --
>
>-----------------------------------------------------------
>
>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
