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