Oracle10G监控索引的使用情况

 

        最近在检查数据库表空间的使用率时，发现索引空间增长的极快。而开发人员在创建索引时，可能并不在意这些索引是不是真的是必要的。因此我想通过一种方法来监控数据库中索引的使用情况，以便将那些用不到的索引，或者非常差的索引进行清理和优化，达到优化索引和数据库性能的目的。（没有的索引不仅浪费存储，而且会降低DML的性能，尤其是INSERT和UPDATE）

        从ORACLE9i开始，便可以监控索引的使用情况。命令如下：

启用：ALTER . MONITORING USAGE;

停用：ALTER. NOMONITORING USAGE;

        然后通过v$OBJECT_USAGE视图来收集“启用”至“停用”这段时间内索引的使用情况。V$OBJECT_USAGE的USED字段取值为"YES"和"NO"，表示索引被用到或没有用到，很遗憾这里无法得到索引的具体被利用次数。

        由于v$OBJECT_USAGE视图只能查看当前SCHEMA下的索引，对于DBA来说就有一定的局限。因此这里我新建了all视图，使其可以同时监控多个用户。

 

1. 创建全局的v$all_object_usage视图

create or replace view sys.v$all_object_usage
(owner, index_name, table_name, monitoring, used, start_monitoring, end_monitoring)
as
select u.name owner, io.name index_name, t.name table_name,
       decode(bitand(i.flags, 65536), 0, 'NO', 'YES') monitoring,
       decode(bitand(ou.flags, 1), 0, 'NO', 'YES') used,
       ou.start_monitoring start_monitoring,
       ou.end_monitoring   end_monitoring
 from sys.user$ u, 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#
  and u.user# = io.owner#

 

2. 为了方便，我授权所有用户都可以查询该视图

grant select on sys.v$all_object_usage to "PUBLIC";

create public synonym v$all_object_usage for sys.v$all_object_usage;

3. 启动监控所有索引的SHELL

--在这之前，你需要知道，你想要监控哪些用户？可以使用正规则=any(,...)，也可以使用反规则<>all(,...)

---------------------------------start_index_monitor.sh----------------------------------

#!/usr/bin/sh

. /home/kettle/.profile

sqlinfo=`/home/kettle/config/bam_getpasswd /home/kettle/config/db_login.ini`
echo $sqlinfo

sqlplus -s $sqlinfo <set heading off
set feed off
set pagesize 200
set linesize 100
spool start_index_monitor.sql
select 'ALTER INDEX '||owner||'.'||INDEX_NAME||' MONITORING USAGE;'
  from dba_indexes
 where wner=any('BAM')
   and index_type <> 'LOB';

spool off
exit
END_OF_LINE

sqlplus -s $sqlinfo <@./start_index_monitor.sql
exit
END_OF_LINE

 

4. 停止监控所有索引的SHELL

---------------------------------stop_index_monitor.sh----------------------------------

#!/usr/bin/sh

. /home/kettle/.profile

sqlinfo=`/home/kettle/config/bam_getpasswd /home/kettle/config/db_login.ini`
echo $sqlinfo

sqlplus -s $sqlinfo <set heading off
set feed off
set pagesize 200
set linesize 100
spool stop_index_monitor.sql
select 'ALTER INDEX '||owner||'.'||INDEX_NAME||' NOMONITORING USAGE;'
  from dba_indexes
 where wner = any('BAM')
   and index_type <> 'LOB';

spool off
exit
END_OF_LINE

sqlplus -s $sqlinfo <@./stop_index_monitor.sql
exit
END_OF_LINE

 

5. 产生监控报告的SHELL

---------------------------------Unused_index_rpt.sh----------------------------------

#!/usr/bin/sh

. /home/kettle/.profile

sqlinfo=`/home/kettle/config/bam_getpasswd /home/kettle/config/db_login.ini`

sqlplus -s $sqlinfo <set feed off
set pagesize 200
set linesize 100
ttitle center "Unused Indexes Report" skip 2
spool unused_index.rpt

select owner, index_name, table_name, used
  from v\$all_object_usage
 where used = 'NO';

spool off
exit
END_OF_LINE

6. 结果

        在执行了3之后，过一段时间（时间控制在让你的数据库能执行完所有的操作，这是个大概的时间，比如我这就让他执行了三天），再执行4，然后就可以执行5来输出报表了。打印的报表大概是这样的。

未命名.jpg

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10356975/viewspace-739342/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10356975/viewspace-739342/