Index usage

To see what indexes you can remove from a database, you first need to know which ones are typically in use. Scanning the buffer cache (X$BH) is a good starting point to see which indexes are in the cache - but its important to take into account that index blocks need to be present in the buffer cache just to keep the indexes updated, thus their presence in the buffer cache is not a firm guarantee of their necessity.

A next step is to see if any sql's use those indexes. The procedure below does this by issuing an explain plan against every sql presently in the cache. It then extracts any rows representing index access from the PLAN_TABLE entries created and stores then in the INDEX_LIST table below. When run at regular intervals, it also updates a detection count for each index, representing that it was found at least once in this run.

create table index_list
 ( OBJECT_OWNER    VARCHAR2(30),
   OBJECT_NAME     VARCHAR2(30),
   DETECTION_COUNT NUMBER(10)
);


create or replace 
procedure index_access_list is
 cursor c_sql is
   select s.parsing_user_id, st.address||st.hash_value sqlhash, st.sql_text
   from sys.v_$sql s, sys.v_$sqltext st
   where st.address = s.address
   and st.hash_value = s.hash_value
   and st.command_type in (2,3,6,7)
   and s.parsing_user_id > 0
   order by st.address, st.hash_value, st.piece;
  prev_sql varchar2(80);
  prev_uid number := 0;
  entire_sql varchar2(32767);
  err_cnt number := 0;
  tot_cnt number;
  v_user varchar2(30);
begin
delete from plan_table;
commit;
for i in c_sql loop
  if prev_uid != i.parsing_user_id then 
    select name into v_user
    from sys.user$
    where user# = i.parsing_user_id; 
    execute immediate 'alter session set current_schema = '||v_user;
  end if;
  tot_cnt := c_sql%rowcount;
  if prev_sql != i.sqlhash then
    begin
      execute immediate 
        'explain plan set statement_id = '''||i.sqlhash||''' '||
        ' into plan_table'||
        ' for '||entire_sql;
    exception when others then err_cnt := err_cnt + 1;
    end;
    entire_sql := i.sql_text;
  else
    entire_sql := entire_sql || i.sql_text;
  end if;
  prev_sql := i.sqlhash;
end loop;
dbms_output.put_line('Statements processed: '||tot_cnt);
dbms_output.put_line('Statements failed:    '||err_cnt);
update index_list 
set detection_count = detection_count + 1
where (object_owner, object_name) in  
 ( select object_owner, object_name
   from plan_table
   where operation = 'INDEX' );
insert into index_list
select distinct object_owner, object_name, 1
from ( 
  select object_owner, object_name
  from plan_table
  where operation = 'INDEX'
  minus
  select object_owner, object_name
  from index_list );
commit;
select username into v_user
from sys.v_$session
where audsid = userenv('SESSIONID'); 
execute immediate 'alter session set current_schema = '||v_user;
end;
/