oracle 10g 索引监控

相关引用
http://www.eygle.com/archives/2004/12/monitor_index_usage.html


一 环境
Rhel5U4_x86_64 + 10.2.0.4

二
在网上找的如何使用索引 (原地址没记.不由感叹,天下文章一大抄.大家都copy而不去管到底能不能执行出结果.)

索引监控
select   'alter index '||owner||'.'||index_name||' monitoring usage;' from dba_indexes where owner in (select username from dba_users where account_status ='OPEN') and owner not in ('SYS','SYSTEM','PERFSTAT','MGMT_VIEW','MONITOR','SYSMAN','DBSNMP');


运行一段时间然后运行

select   'alter index '||owner||'.'||index_name||' nomonitoring usage;' from dba_indexes where owner in (select username from dba_users where account_status ='OPEN') and owner not in ('SYS','SYSTEM','PERFSTAT','MGMT_VIEW','MONITOR','SYSMAN','DBSNMP');

查询视图
V$OBJECT_USAGE


整合脚本
set pages 2000;
set heading off;
spool index_monitor.sql
select   'alter index '||owner||'.'||index_name||' monitoring usage;' from dba_indexes where owner in (select username from dba_users where account_status ='OPEN') and owner not in ('SYS','SYSTEM','PERFSTAT','MGMT_VIEW','MONITOR','SYSMAN','DBSNMP');
spool off;
@index_monitor


按照上面做的以后,以sys用户登录系统查询v$object_usage视图.无结果返回.不由怀疑这种方法的可行性.于是又一顿神goo. 找到了此文档最前面贴出的链接地址.

下面是 v$object_usage 创建命令(我不会从数据库里面找出ddl语句.是用pldeveloper搜出的)

create or replace view v$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 io.owner# = userenv('SCHEMAID')
  and i.obj# = ou.obj#
  and io.obj# = ou.obj#
  and t.obj# = i.bo#;
comment on column V$OBJECT_USAGE.INDEX_NAME is 'Name of the index';
comment on column V$OBJECT_USAGE.TABLE_NAME is 'Name of the table upon which the index was build';
comment on column V$OBJECT_USAGE.MONITORING is 'Whether the monitoring feature is on';
comment on column V$OBJECT_USAGE.USED is 'Whether the index has been accessed';
comment on column V$OBJECT_USAGE.START_MONITORING is 'When the monitoring feature is turned on';
comment on column V$OBJECT_USAGE.END_MONITORING is 'When the monitoring feature is turned off';


注意到v$object_usage关键信息来源于OBJECT_USAGE表.
另外我们可以注意一下,此处v$object_usage的查询基于userenv('SCHEMAID')建立.
所以以不同用户登录,是无法看到其他用户的索引监视信息的,即使这个用户是dba.

那如果我想以sys来查询其他用户的监控结果怎么办呢? 根据 v$object_usage创建命令知道 只要能够查询 object_usage并且知道 schemaid就可以了.

Schemaid就是 dba_users 表中的 user_id 列.

查询语句可以这样写: (仅做参考)
select 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.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 io.owner#  in (select user_id from dba_users where username =’DRUG’);

这里写下,分享并自已留用.

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