Re: how to identify unused indexes

That can give you a slight edge, but there is a problem with that approach - dirty blocks can become clean very quickly - in about 3 seconds - and the leaf blocks of useless indexes are the blocks which are likely to become clean fastest.

One of my 'maybe' tests also very simple - check the plan for:

    select * from table where useless_index = constant;

if it's a tablescan, then the index is probably never used, but the trouble is that there are still special cases like:

    select count(*) from table
    where useless_index = constant;

where it might be used.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Connor McDonald wrote in message <39968175.27A8_at_yahoo.com>...


>Jonathan Lewis wrote:

>>

>> 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

>> >

>

>I haven't done any exhaustive analysis but I remember using the flags in

>x$bh to determine whether an index blocks were dirty or not - it wasn't

>perfect but it gave more "confidence" in determining whether they got

>there from a read or a write.

>

>CHeers

>--

>===========================================

>Connor McDonald

>http://www.oracledba.co.uk

>

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