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; /