下面的PL/SQL块对数据库中的所有索引(SYS和SYSTEM拥有的索引除外)启用监控：
declare
l_sql varchar2(128);
begin
for rec in
(select ''alter index ''||owner.||''.''||index_name||'' monitoring usage'' mon
from dba_indexes
where owner not in (''SYS'', ''SYSTEM'')
and index_type=''NORMAL'') loop
l_sql:=rec.mon;
execute immediate l_sql;
end loop;
end;
下面我们来看一下Oracle 9i 这个新特性能不能识别在进行DML操作时外键列上索引的使用情况：
以9i中HR模式为例：
标准的dept和emp表是一个与外键关联的父子表的例子。这个例子主要想看一下，在父表上删除一个记录，会不会调用子表上外键上的索引。 首先监控HR模式下所有索引的使用，为了便于在主表上删除一条记录，不违反引用完整性约束。我们首先丢弃原有的约束，重新创建支持级联删除的约束.
alter table employees add constraint emp_dept_fk foreign
key (department_id) references departments on delete cascade;
alter table job_history drop constraint jhist_emp_fk;
alter table job_history add constraint jhist_emp_fk foreign
key(employee_id) references employees on delete cascade;
delete from departments where department_id=10; |
注意在此为了方便，我们删除部门id为10的记录。如果你删除其他的部门，可能你还要更改表job_history中相关的约束。
现在我们看看索引使用的情况：
select index_name, table_name, monitoring, used
from v$object_usage
where used=''YES''
INDEX_NAME TABLE_NAME MON USE
------------------------------ -------------------- --- ---
DEPT_ID_PK DEPARTMENTS YES YES
EMP_EMP_ID_PK EMPLOYEES YES YES
EMP_DEPT_FK EMPLOYEES YES YES |
很明显删除父表上的记录，也利用了子表中相关的索引。
v$object_usage 视图的一个异常之处是， 它只能显示属于连接用户的索引的信息。Oracle可能在将来会解决这个问题。如果您的数据库只显示连接用户的对象使用信息，下面的视图(必须被创建为SYS)可用于提供来自任何帐户的所有被监控的索引的信息：
create or replace view
V$ALL_OBJECT_USAGE(INDEX_NAME, TABLE_NAME, MONITORING, USED,
START_MONITORING, END_MONITORING) as
select io.name, t.name, decode(bitand(i.flags, 65536),0,''NO'',''YES''),
decode(bitand(ou.flags,1),0,''NO'',''YES''), ou.start_monitoring,
ou.end_monitoring
from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou
where i.obj#=ou.obj#
and io.obj#=ou.obj#
and t.obj#=i.bo#;
grant select on v$all_object_usage to public;
create public synonym v$all_object_usage for v$all_object_usage; |
3、最后我们简单的说一下，如何监控最近被使用的索引
下列查询将列出最近被访问的索引：
column owner format a20 trunc
column segment_name format a30 trunc
select distinct b.owner, b.segment_name
from x$bh a, dba_extents b
where b.file_id=a.dbafil
and a.dbablk between b.block_id and b.block_id+blocks-1
and segment_type=''INDEX''
and b.owner not in (''SYS'',''SYSTEM''); |
这个过程可能要耗费一定时间，建议在数据库不太繁忙的时候运行。
