---
title: "oracle 10g 索引监控无效 奶奶的熊。"
author: "winzenghua"
date: "2009-07-28T17:30:00Z"
tags: []
slug: "oracle-10g"
draft: false
lastmod: "2009-07-28T17:30:00Z"
id: 987863
url: "https://java4gis.iteye.com/blog/987863"
---

昨天用脚本生成一堆索引 监控语句

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'