Niall's Oracle Pages – Oracle Opinion since 2004

When unused – isn’t.

with 3 comments

So we recently reviewed a table with no fewer than 23 indexes on it. A combination of alter index… monitoring usage and reviewing DBMS_HIST_SQL_PLAN for plans that referenced the indexes found 8 indexes that were never used. As this table is also heavily updated we removed the indexes identified as unused. This was, fortunately, a QA system under change control. See if you can spot when the change was made.


Possibly Related Posts:

  • No Related Posts found

Written by Niall Litchfield

March 7th, 2013 at 2:44 pm

Posted in Uncategorized

3 Responses to 'When unused – isn’t.'

Subscribe to comments with RSS or TrackBack to 'When unused – isn’t.'.

  1. Read here —


    I said as much toward the bottom of the post. :)

  2. Thanks for dropping by David. This *was* a test system. Unfortunately v$object_usage is a bit unreliable, but the real mistake here was not remembering that DBA_HIST_SQL_PLAN records the plan for child cursors found in AWR so if your index makes the query really efficient, it probably won’t be captured in the workload repository until you drop the index when suddenly its much more likely to be a Top N SQL. :(

    Niall Litchfield

    7 Mar 13 at 4:37 pm

  3. So did you drop them or just make them invisible?

    Dominic Brooks

    7 Mar 13 at 4:44 pm

Leave a Reply

%d bloggers like this: