oracle索引使用监控

create table index_test as select * from dba_objects;
create index t_id on index_test(object_id) nologging;

create or replace procedure p_m as
i_name varchar2(20);
cursor c1 is select index_name from all_indexes where table_name=’INDEX_TEST’ and owner=’BAIXYU’;
begin
open c1;
loop
fetch c1 into i_name;
if c1%found then
execute immediate ‘alter index ‘||i_name||’ monitoring usage’;
else
exit;
end if;
end loop；
end p_m;

执行exec p_m;
通过查看下面的视图来查看索引的使用情况
select * from index_test where object_id=123;
alter index t_id monitoring usage;
select * from v$object_usage;