How to monitor indexes and clear monitoring information in oracle9i

oracle| Index
For DML operations, indexing is a performance burden for the database. If the index is not used effectively, its existence is worth new consideration. 1. Starting with Oracle9i, Oracle allows you to monitor the use of indexes:

Sql> Connect scott/tiger@connerconnected to oracle9i Enterprise Edition release 9.2.0.4.0 Connected as Scottsql> sel ECT index_name from User_indexes;index_name------------------------------pk_deptpk_emp start monitoring pk_dept index:sql> ALTER Index pk_dept monitoring Usage;index altered in this procedure, if the query uses an index, it will be recorded:sql> select * from dept where Deptno=10;deptno Dname LOC---------------------------------ACCOUNTING NEW York stop monitoring:sql> alter index pk_dept nomonitoring Usage;index al tered Query Index Usage, yes indicates that indexes are used during monitoring to:sql> SELECT * FROM v$object_usage;index_name table_name monitoring USED Start_ Monitoring end_monitoring-------------------------------------------------------------------------------------- -pk_dept DEPT NO YES 10/28/2004 10:55:19 10/28/2004 10:55:47sql>

2.oracle9i's Bug

Before 9205, if you accidentally monitor the Sys.i_objauth1 index and unfortunately do not stop it before you restart the database, your database will not start and no error message will be given.

The following simple statement can easily reproduce the problem:

' ALTER INDEX SYS. I_objauth1 monitoring USAGE '

If you have a good enough backup (severe warning, please do not test your production database), you can try:

[Oracle@jumper oradata]$ Sqlplus "/As SYSDBA"

Sql*plus:release 9.2.0.4.0-production on Sat Dec 4 10:09:30 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:oracle9i Enterprise Edition release 9.2.0.4.0-productionwith the partitioning optionjserver release 9.2.0.4 .0-production

Sql> ALTER index SYS. I_objauth1 monitoring usage;

Index altered.

sql> shutdown immediate;database closed. Database dismounted. ORACLE instance shut down. Sql> Startuporacle instance started.

Total System Global area 80811208 bytesfixed size 451784 bytesvariable size 37748736 bytesdatabase buffers 41943040 Edo buffers 667648 bytesdatabase mounted.



At this point, the database hangs, and there is no hint, in the Alert<sid>.log file, you can see:

[Oracle@jumper bdump]$ tail-f alert_conner.log completed:alter database Mountsat Dec 4 10:09:49 2004ALTER database opens At DEC 4 10:09:49 2004lgwr:primary database was in CLUSTER consistent Modethread 1 opened at log sequence 2 seq# mem# 0:/opt/oracle/oradata/conner/redo02.logsuccessful Open of Redo thread 1.Sat Dec 4 10:09:49 2004smon:enabl ing cache Recoverysat Dec 4 10:10:33 2004Restarting dead background process qmn0qmn0 started with pid=9



The database will then be parked here.

If you do not know this bug exists, you may be helpless.

Now all you can do is restore from backup, or upgrade to 9.2.0.5.

Oracle has already release this bug, you can refer to Metalink:note:2934068.8,oracle declaration in 9.2.0.5 (Server Patch Set) and 10g Production Base This bug was fixed in release.

[Oracle@jumper oradata]$ rm-rf conner[oracle@jumper oradata]$ cp-r connerbak/conner[oracle@jumper oradata]$ '/ As Sysdba '

Sql*plus:release 9.2.0.4.0-production on Sat Dec 4 10:19:07 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to a idle instance.

Sql> Startuporacle instance started.

Total System Global area 80811208 bytesfixed size 451784 bytesvariable size 37748736 bytesdatabase buffers 41943040 Edo buffers 667648 bytesdatabase mounted. Database opened. Sql>



3. In exceptional circumstances, you may need to clear the information in this v$object_usage view.

Oracle says that the last information is automatically overwritten the next time the index usage of the object is collected, and no cleanup is provided.

A little research.

V$object_usage is based on the following base table:

Create or Replace view V$object_usage (INDEX_NAME, TABLE_NAME, monitoring, used, start_monitoring, end_monitoring) Asselect 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_monitoringfrom sys.obj$ io, sys.obj$ t, sys.ind$ I, sys.object_usage ouwhere io.owner# = Userenv ( ' SchemaID ') and i.obj# = ou.obj# and io.obj# = ou.obj# and t.obj# = i.bo#/



Note that the V$object_usage key information comes from the Object_usage table. In addition, we can note that the query v$object_usage here is based on Userenv (' SchemaID '). So log in as different users, You cannot see the index monitoring information of other users, even DBAs, but you can get it from the Object_usage table.



Sql> SELECT * FROM V$object_usage;index_name table_name MON use start_monitoring end_ Monitoring----------------------------------------------------------------------------------------------------- ---pk_dept DEPT NO YES 10/28/2004 10:55:19 10/28/2004 10:55:47sql> SELECT * from Object_usage;select * from Object_usag e *error at line 1:ora-00942:table or view does not existsql> connect/as sysdbaconnected.sql>/obj# FLAGS START_M onitoring end_monitoring----------------------------------------------------------6288 1 10/28/2004 10:55:19 10/28/ 2004 10:55:47

In fact, we cleared the records of the Object_usage table and actually emptied the v$object_usage information.





Sql> Delete from object_usage;1 row deleted. Sql> commit; Commit complete. Sql> SELECT * from V$object_usage;no rows selected



This operation has no potential impact on the database, but use it with caution. For experimental purposes.





The author of this article: Eygle,oracle, the largest Oracle technology forum in China, Itpub.www.eygle.com is the author's personal site. You can pass guoqiang.gai@. gmail.com to contact the author. Welcome the technical discussion Exchange and the link exchange.

Original source:

Http://www.eygle.com/internal/how.to.monitor.index.and.how.to.clean.out.v$object_usage.htm