OTech Magazine issue Spring 2015

Hey!  I'm in Print!  Well in the internet age kind of "print".  Check out the latest issue of the OTech Magazine. 


Click Here or on the image -->

My article is "Know your Histograms".  Lots of other great articles in here too.

Enjoy!

Which indexes are NOT being used? UPDATED!

UPDATE!  Ok I really shouldn't write posts when I'm sick... The query was a not correct.  Thanks to Mikhail Velikikh  for pointing this it out.

Correct query is:

SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME

 FROM DBA_INDEXES

 WHERE OWNER = 'OP' AND INDEX_TYPE != 'LOB'

 and (owner, index_name) not in (

 SELECT object_owner, OBJECT_NAME

 FROM DBA_HIST_SQL_PLAN

 WHERE OBJECT_OWNER = 'OP' AND OPERATION = 'INDEX')

/

 

Have a great day!  

There is a lot of chatter about indexes and folks are realizing that more indexes isn’t better and too many can be bad.  The impact on DML is real.  The more indexes the more maintenance there is for all INSERTS and DELETES and maybe the UPDATES too (just depends on what you update of course).   Also more indexes can even cause the optimizer to slow down as it considers indexes that it ends up not using for selects.

I had a post a while back to show which indexes are being used (http://ricramblings.blogspot.com/2012/11/is-this-index-being-used.html).  Which certainly is nice to know but what about a system with lots of indexes out there, which ones aren’t being used?  It’s likely faster to zero in on those unused ones and figure out if you should keep it or not.  

The index monitoring is an option and it might give you an idea of which ones aren’t, however it’s not a great tool really and unfortunately the results aren’t reliable. (If you’d like, check out Richard Foote’s blog (https://richardfoote.wordpress.com/) and search for “Index Monitoring”, he has several posts talking about its limitations.)  Really what you want to know is “Which indexes are not being used in my SQL?”

For this the best approach is to use DBA_HIST_SQL_PLAN.  However you must be warned that this view is part of the Oracle Diagnostic Pack.  If you’re not licensed for this you cannot use this view.  Most large shops are, so this shouldn’t be an issue.  Assuming you have the Diagnostic Pack, here is a simple query that will tell you which indexes haven’t been used in SQL for a given schema for as long as you have history in the view:

SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME FROM DBA_INDEXES

WHERE OWNER = 'OP' AND INDEX_TYPE != 'LOB'

MINUS

SELECT OBJECT_NAME, NULL, NULL FROM DBA_HIST_SQL_PLAN

WHERE OBJECT_OWNER = 'OP' AND OPERATION = 'INDEX'

That’s it.  I’ve excluded LOB indexes since they are mostly used internally and dropping those would be a very bad thing, but otherwise this will show you all the indexes that haven’t been used in a plan that is in the plan history. 

If you don’t have the Diagnostic Pack, you could change this to:

SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME FROM DBA_INDEXES

WHERE OWNER = 'OP' AND INDEX_TYPE != 'LOB'

MINUS

SELECT OBJECT_NAME, NULL, NULL FROM V$SQL_PLAN

WHERE OBJECT_OWNER = 'OP' AND OPERATION = 'INDEX'

The only issue here is that this only shows plans that are currently in the shared pool, so you might have used an index in some query that has aged out for example.  But even this should give you a decent list of indexes worth investigation.  

Once you find some indexes that appear to not be used, now what?  Well if you’re a cowboy you can just drop ‘em and see what happens!  Of course that might not really be a great idea, so this is a case where making the index INVISIBLE for a while might be good, then after a few weeks (or months) drop them.