Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: how to identify unused indexes

Re: how to identify unused indexes

From: Mark D Powell <mark.powellNOmaSPAM_at_eds.com.invalid>
Date: 2000/08/11
Message-ID: <1a710ddb.074bfaf3@usw-ex0102-015.remarq.com>#1/1

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US