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:
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.
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.
Hello Ric,
ReplyDeleteYour 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.
Excellent! Good catch. Will update this post. I should know better then to write a post when I'm sick. :-) Thanks much.
ReplyDelete