Existing Index Needed or Not needed

/* 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:*/

Connect as sys user and use this query to check how many times each index is used for a schema...


Method 1 : ( Oracle 10g )
select p.object_owner owners, p.object_name Obj_Name, p.operation Operation, p.options Options, count(1) Idx_Usg_Cnt
from dba_hist_sql_plan p,dba_hist_sqlstat s
where p.object_owner = 'Schema_Name' and p.operation like '%INDEX%' and p.sql_id = s.sql_id
group by p.object_owner,p.object_name,p.operation,p.options
order by 1,2,3

To list all the indexes in a schema use the following query.

select t.owner,t.table_name,t.index_name,t.last_analyzed from dba_indexes t
where t.owner = 'schema_name'



/* -- Here is another script for tracking unused indexes and showing the invocation count(How manytimes and when they are called/Used) of all indexes.
-- Best of all, this script shows the columns referenced for multi-column indexes: */

select to_char(sn.begin_interval_time,'yy-mm-dd hh24') Begin_Int_Time, p.search_columns Search_Col, count(*) Invocation_Cnt
from dba_hist_snapshot sn, dba_hist_sql_plan p, dba_hist_sqlstat st
where st.sql_id = p.sql_id and sn.snap_id = st.snap_id and lower(object_name) like /*lower('%&idxname%')*/ lower ('Idx_Name')
group by begin_interval_time,search_columns

With these queries we can find out the unused indexes and drop them off.


Method 2 : (Oracle 9i)
Connect to a user and then
alter index index_Name monitoring usage

then use the query below to check whether the index is used

select index_name mon,used from v$object_usage;

Here is a simple SQL*Plus script to track all index usage in a Oracle schemas:

set pages 999;
set heading off;
spool Idx_monitor.sql

select 'alter index '||owner||'.'||index_name||' monitoring usage;'
from dba_indexes
where owner = 'Schema_name';

spool off;

@Idx_monitor.sql

Once idx_monitor.sql completes, you can check for the Index usage by

select index_name mon,used from v$object_usage;