Friday, March 20, 2015

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.

2 comments:

  1. Hello Ric,

    Your current query could incorrectly lists table indexes as not been used.
    I slightly modified your query. Please, see modified query below:
    SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME
    FROM DBA_INDEXES
    WHERE OWNER = 'TC' AND INDEX_TYPE != 'LOB'
    and (owner, index_name) not in (
    SELECT object_owner, OBJECT_NAME
    FROM DBA_HIST_SQL_PLAN
    WHERE OBJECT_OWNER = 'TC' AND OPERATION = 'INDEX')
    /

    As a further improvement, I suggest to exclude dbms_stats cursors from analysis.
    Because index can be used when we are gather a statistics on index.
    Same true for the automatic maintenance task.

    I provided a simple test case below that shows that your current query incorrectly lists indexes that was used as not been used.

    SQL> grant create session,create table to tc identified by tc;
    Grant succeeded.
    SQL> create table tc.t(x int, y int, constraint t_pk primary key(x));
    Table created.
    SQL> create index tc.t_y_i on tc.t(y);
    Index created.
    SQL> select /*+ index(t)*/* from tc.t where x = 1;
    no rows selected
    SQL> col sql_id old_v sql_id
    SQL> select sql_id, sql_text from v$sqlarea where sql_text='select /*+ index(t)*/* from tc.t where x = 1';
    SQL_ID SQL_TEXT
    --------------------------------------- --------------------------------------------------
    anhq776dd69r3 select /*+ index(t)*/* from tc.t where x = 1
    SQL> doc
    DOC> The output of the query below shows that index T_PK was been used.
    DOC>#
    SQL> select * from table(dbms_xplan.display_cursor( '&sql_id.'));
    PLAN_TABLE_OUTPUT
    SQL_ID anhq776dd69r3, child number 0
    select /*+ index(t)*/* from tc.t where x = 1
    Plan hash value: 277961208
    -------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
    -------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | | | 1 (100)|
    | 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 26 | 1 (0)|
    |* 2 | INDEX UNIQUE SCAN | T_PK | 1 | | 1 (0)|
    -------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    2 - access("X"=1)
    23 rows selected.
    SQL> exec dbms_workload_repository.add_colored_sql( '&sql_id.')
    SQL> exec dbms_workload_repository.create_snapshot
    SQL> doc
    DOC> The output of the query below shows that T table indexes was not been used.
    DOC> This's does not mean that they was not been used.
    DOC>#
    SQL> SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME FROM DBA_INDEXES
    2 WHERE OWNER = 'TC' AND INDEX_TYPE != 'LOB'
    3 MINUS
    4 SELECT OBJECT_NAME, NULL, NULL FROM DBA_HIST_SQL_PLAN
    5 WHERE OBJECT_OWNER = 'TC' AND OPERATION = 'INDEX';

    INDEX_NAME INDEX_TYPE TABLE_NAME
    ----------- ----------- -----------
    T_PK NORMAL T
    T_Y_I NORMAL T
    SQL> doc
    DOC> Actually, T_PK index was been used. T_Y_I index was not been used.
    DOC>#
    SQL> SELECT OBJECT_NAME, NULL, NULL
    2 FROM DBA_HIST_SQL_PLAN
    3 WHERE OBJECT_OWNER = 'TC' AND OPERATION = 'INDEX';
    OBJECT_NAME N N
    ------------------------------ - -
    T_PK
    SQL> doc
    DOC> The output of the query below shows that T_Y_I index was not been used.
    DOC> That's correct.
    DOC>#
    SQL> SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME
    2 FROM DBA_INDEXES
    3 WHERE OWNER = 'TC' AND INDEX_TYPE != 'LOB'
    4 and (owner, index_name) not in (
    5 SELECT object_owner, OBJECT_NAME
    6 FROM DBA_HIST_SQL_PLAN
    7 WHERE OBJECT_OWNER = 'TC' AND OPERATION = 'INDEX')
    8 /
    INDEX_NAME INDEX_TYPE TABLE_NAME
    ----------- ----------- -----------
    T_Y_I NORMAL T

    Best regards,
    Mikhail.

    ReplyDelete
  2. Excellent! Good catch. Will update this post. I should know better then to write a post when I'm sick. :-) Thanks much.

    ReplyDelete