For DML operation, the index is a performance burden for the database. If the index is not used, its existence is worthwhile. 1. Starting from Oracle9i, Oracle allows you to monitor the index:
SQL> Connect Scott / Tiger @ Conner
Connected to Oracle9i Enterprise Edition Release 9.2.0.4.0
Connected as Scott
SQL> SELECT INDEX_NAME from User_indexes;
Index_name
------------------------------
PK_DEPT
PK_EMP
Start monitoring PK_DEPT index:
SQL> ALTER INDEX PK_DEPT MONITORING USAGE;
Index altered
In this process, if the query uses the index, it will be recorded:
SQL> SELECT * from dept where deptno = 10;
DEPTNO DNAME LOC
-------------------------------
10 Accounting New York
Stop monitoring:
SQL> ALTER INDEX PK_DEPT NOMONITORING USAGE;
Index altered
Query index usage, yes indicates that the index is used in the monitoring process:
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:47
SQL>
2. Oracle9i Bug
Before 9205, if you accidentally monitor the sys.i_objauth1 index, and unfortunately did not stop it before restarting the database, then your database will not start and will not give any error messages.
The following simple statements can easily reproduce this question:
'Alter index sys.i_objauth1 Monitoring Usage'
If you have a good backup (serious warning, please don't test your production database), you can try it:
[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 bytesRedo Buffers 667648 bytesDatabase mounted.
At this point, the database hangs, and there will be no prompts, in the ALERT
[Oracle @ jumper bdump] $ tail -f alert_conner.log Completed: ALTER DATABASE MOUNTSat Dec 4 10:09:49 2004ALTER DATABASE OPENSat Dec 4 10:09:49 2004LGWR: Primary database is in CLUSTER CONSISTENT modeThread 1 opened at log sequence 54 Current log # 2 SEQ # 54 MEM # 0: /opt/oracle/oradata/conner/redo02.logsuccessful Open of redo thread 1.sat Dec 4 10:09:49 2004smon: Enabling Cache RecoverySat Dec 4 10:10:33 2004Restarting DEAD Background Process QMN0QMN0 Started with PID = 9
Then the database will stop here.
If you don't know this bug, you may fight.
Now what you can do is to recover from backup, or upgrade to 9.2.0.5.
Oracle already released this bug, you can refer to Metalink: NOTE: 2934068.8, Oracle declaration in 9.2.0.5 (Server Patch Set) and 10G Production Base Release in Fixed this bug.
[Oracle @ Jumper ORADATA] $ RM -RF Conner [Oracle @ Jumper ORADATA] $ CP -R Connerbak / Conner [Oracle @ Jumper ORADATA] $ SQLPLUS '/ 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 an iDLE Instance.
SQL> StartupoPoracle Instance Started.
Total System Global Area 80811208 Bytesfixed Size 451784 Bytesvariable Size 37748736 Bytesdatabase Buffers 41943040 BYTESREDO BUFFERS 667648 BYtesDatabase mounted.Database openmed.sql>
3. In special cases, you may need to clear the information in this V $ Object_USAGE view.
Oracle's statement is that the last information is automatically overwritten when the index usage of the object will be collected, and the clear means is not provided.
I have studied it slightly.
v $ object_usage is established based on the following 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 outhere objer # = useerenv ('schemaid') And i.obj # = Ou.obj # and o.obj # = Ou.obj # and t.obj # = I.bo # /
Note that the V $ Object_USAGE key information is derived from the object_usage table. In addition, we can pay attention to the V $ Object_USAGE query is based on Userenv ('SchemAID'). So you can log in with different users, you can't see other users' index monitoring Information, even DBA, but can be obtained 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:47
SQL> SELECT * from Object_USAGE;
SELECT * from Object_USAGE
*
Error At Line 1:
ORA-00942: Table or view does not exist
SQL> Connect / as sysdba
Connected.
SQL> /
Obj # flags start_monitoring end_monitoring
---------- --------------------------- ----------- ------------
6288 1 10/28/2004 10:55:19 10/28/2004 10:55:47
In fact, we clear the record of the Object_usage table, which is actually cleaned the information of V $ Object_USAGE.
SQL> delete from object_usage;
1 row deleded.
SQL> commit;
COMMIT COMPLETE.
SQL> SELECT * FROM V $ Object_USAGE;
No rows selected
This operation does not have a potential impact on the database, but please use it carefully as an experimental purpose.
Author: eygle, Oracle technology followers, Oracle technical forum itpub.www.eygle.com from China is the biggest author's personal site you may contact the author by Guoqiang.Gai@gmail.com welcome to explore technical exchanges and links. Exchange. Original article:
Http://www.eygle.com/internal/how.to.monitor.index.nd.how.to.clean.out.vm (ket)
