昨天用脚本生成一堆索引 监控语句
select'alter index '||index_name||' monitoring usage;'
from user_indexes;
alter index IX_BASE_PROUSER_USER monitoring usage;
alter index IX_BASE_PROUSER_LOWEX monitoring usage;
alter index IX_BASE_PROUSER_SOURCE monitoring usage;
alter index IX_BASESUCCPROUSE_ENDDATE monitoring usage;
alter index IX_BASE_PROUSER_YEARMONTH monitoring usage;
alter index IX_BASE_PROUSER_YEARWEEK monitoring usage;
alter index IX_BASE_PROUSER_PROID monitoring usage;
alter index IX_VIP_TRADEDETAIL_NAMEADDTIME monitoring usage;
然后在pl/sql developer 的 command 窗口执行成功
今天凌晨有15个过程在跑。上班后用SYS账号查看
select * from V$OBJECT_USAGE
结果没没有记录
为此专门写个查询去使用 索引 IX_VIP_TRADEDETAIL_NAMEADDTIME。可后果依旧没有记录。
最后从网上扣了这段代码过来
select object_owner, object_name, options, count(*)
from v$sql_plan
where operation='INDEX'
and object_owner='BI'
group by object_owner, object_name, operation, options
order by count(*) desc;
原因:是用SYS账号是看不到BI账号的索引监控情况的
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#
注意到v$object_usage关键信息来源于OBJECT_USAGE表.
另外我们可以注意一下,此处v$object_usage的查询基于userenv('SCHEMAID')建立.
所以以不同用户登录,你是无法看到其他用户的索引监视信息的,即使是dba,但是可以从object_usage表中得到.
在9205之前,如果你不慎监控了SYS.I_OBJAUTH1索引,并且不幸在重起数据库之前没有停止它,那么你的数据库将会无法启动,并且
不会给出任何错误信息。
以下这条简单的语句可以轻易再现这个问题:
'ALTER INDEX SYS.I_OBJAUTH1 MONITORING USAGE'