 |
|
Oracle - Find unused Indexes
Oracle Tips by Burleson Consulting |
Also see my notes on
detecting duplicate Oracle index columns and
how to detect
duplicate
table rows. There are
3 ways to
write duplicate row detection queries.
Finding unused indexes in Oracle
10g
In Oracle10g we can easily see what indexes are
used, when they are used and the context where they are used. Here is a simple
AWR query to plot index usage:
col c1 heading ‘Object|Name’ format a30
col c2 heading ‘Operation’ format a15
col c3 heading ‘Option’ format a15
col c4 heading ‘Index|Usage|Count’ format 999,999
break on c1 skip 2
break on c2 skip 2
select
p.object_name c1,
p.operation c2,
p.options c3,
count(1) c4
from
dba_hist_sql_plan p,
dba_hist_sqlstat s
where
p.object_owner <> 'SYS'
and
p.operation like ‘%INDEX%’
and
p.sql_id = s.sql_id
group by
p.object_name,
p.operation,
p.options
order by
1,2,3;
Here is another script for tracking unused indexes and show the invocation
count of all indexes. Best of all, this script shows the columns
referenced for multi-column indexes:
col c1 heading 'Begin|Interval|time' format
a20
col c2 heading 'Search Columns' format 999,999
col c2 heading 'Invocation|Count' format a20
break on c1 skip 2
select
begin_interval_time c1,
count(*) c3
from
dba_hist_sqltext
natural join
dba_hist_snapshot
where
lower(sql_text) like lower('%cust_name_idx%')
Here is the output showing index usage:
Invocation Counts for cust_index
Begin
Interval
Invocation
time
Search Columns Count
-------------------- -------------- -----------
04-10-21 15
1 3
04-10-10 16
0 1
04-10-10 19
1 1
04-10-11 02
0 2
04-10-11 04
2 1
04-10-11 06
3 1
04-10-11 11
0 1
But you can still locate unused indexes in
Oracle 9i and Oracle 8i, but the mechanism for finding the unused indexes is not
as easy.
Finding unused indexes on
Oracle9i
One of the great features of
Oracle9i is the ability to easily locate and remove unused indexes.
When an index is not used by SQL queries with the cost-based
optimizer, the unused indexes waste space and cause INSERT
statements to run slower.
When you issue the alter index
<index_name> monitoring usage command, Oracle places an entry in
the v$object_usage view so you can see if the index is used.
This is just a bit-flag that is set to “1” when the index is
accessed by any SQL statement.
Here is a simple SQL*Plus script
to track all index usage in all Oracle schemas:
set pages 999;
set heading off;
spool run_monitor.sql
select
'alter index '||owner||'.'||index_name||' monitoring usage;'
from
dba_indexes
where
owner not in ('SYS','SYSTEM','PERFSTAT')
;
spool off;
@run_monitor
Next, we wait until a significant
amount of SQL has executed on our database, and then query the new
v$object_usage view:
select
index_name
mon,
used
from
v$object_usage;
Here we see that v$object_usage
has a single column called used, which will be set to YES or NO.
Sadly, this will not tell you how many times the index has been
used, but this tool is useful for investigating unused indexes.
INDEX_NAME MON USED
--------------- --- ----
CUSTOMER_LAST_NAME_IDX YES NO
Schema owners
and finding unused indexes
The problem is that the
v$object_usage view uses the username logged into database when
the alter index index_name monitoring usage; command is
issued.
The solution is to bypass the
v$object_usage view and query the underlying sys.object_usage
fixed table. This solution is from Oracle MetaLink:
select
io.name, t.name,
decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
u.start_monitoring,
ou.end_monitoring
from
sys.obj$ io,
sys.obj$ t,
sys.ind$ i,
sys.object_usage ou
where
io.owner# = userenv('SCHEMAID')
and
i.obj# = ou.obj#
and
io.obj# = ou.obj#
and
t.obj# = i.bo#;
This is another possible solution published by Oracle guru Rich
Jesse:
decode(bitand(i.flags, 65536), 0, 'no', 'yes') "monitoring",
decode(bitand(nvl(ou.flags,0), 1), 0, 'no', 'yes') "used",
ou.start_monitoring "start_monitoring",
ou.end_monitoring "end_monitoring"
sys.object_usage ou, sys.user$ = u
u.name not in ('sys','system')
Finding
unused indexes in Oracle8i
This same technique for finding
unused indexes can be done in Oracle8i, but it involves running
complex scripts to interrogate the Oracle 8i library cache and parse
for index names. Col. John Garmany has developed a complete method
to detect and remove un-used indexes in Oracle8 and Oracle8i
databases.
