Oracle中监控索引的使用 （3）

  来源：赛迪网技术社区    作者：amaome

下面的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');

这个过程可能要耗费一定时间，建议在数据库不太繁忙的时候运行。