Identifies unused Indexes

Some Indexes do not need to be used for identifying unused indexes. However, when you modify a table, the indexes are automatically modified, reducing the database speed. Next we will make a simple experiment to monitor whether the index is used. First create a table, SQL> create Table t (id int, sex char (1), name char (10); table created. then input the data, SQL> begin 2 for I in 1 .. 100 3 loop 4 insert into t values (I, 'M', 'sun'); 5 end loop; 6 commit; 7 end; 8/PL/SQL procedure successfully completed. create an index for table t, SQL> create Index t_idx1 on t (id); index created. for more information, see SQL> select object_name, object_type from user_objects; OBJECT_NAME OBJECT_TYPE ----------------------------- ------------------ T_IDX1 indext table: Before monitoring indexes, we will first introduce a dynamic performance view. V $ OBJECT_USAGE displays statistics about index usage gathered from the database. you can use this view to monitor index usage. all indexes that have been used at least once can be monitoredand displayed in this view. V $ OBJECT_USAGE displays statistics on index usage in the database. This view can be used to monitor index usage. This view can monitor and display all indexes used last time. Column Datatype DescriptionINDEX_NAME VARCHAR2 (30) Index name in sys. obj $. name (index name) TABLE_NAMEa VARCHAR2 (30) Table name in sys. obj $. name (base table of the index) MONITORING VARCHAR2 (3) YES | NO (whether to monitor, yes YES | no) USED VARCHAR2 (3) YES | NO (whether to use, yes | no) START_MONITORING VARCHAR2 (19) Start monitoring time insys. object_stats.start_monitoring (index monitoring Start time) END_MONITORING VARCHAR2 (19) End monitoring time insys. object_stats.end_mon Itoring (the index monitoring end time is enabled and the use of monitoring indexes starts. SQL> alter Index t_idx1 monitoring usage; index altered. SQL> select * from v $ object_usage; INDEX_NAME TABLE_NAME mon use START_MONITORING END_MONITORING Starting monitoring ------ ------- begin ------------------- T_IDX1 t yes no 04/18/2013 00:23:06 on the above display MON Is yes indicates start monitoring, use is no indicates not used, it also uses the monitoring start time and end time. Here we can use the index to see the changes in the dynamic performance view. SQL> set autot on expSQL> select * from t where id = 88; id s name --------------------- 88 M sun Execution Plan hash value: 4055207394 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | ----------------------------- ----------------------------------------------------- | 0 | select statement | 1 | 28 | 2 (0) | 00:00:01 | 1 | table access by index rowid | T | 1 | 28 | 2 (0) | 00:00:01 | * 2 | index range scan | T_IDX1 | 1 | 1 (0) | 00:00:01 | identified Predicate Information (identified by operation id ):------------------------------ --------------------- 2-access ("ID" = 88) You can see from the execution plan that the index is used. SQL> set autot offSQL> select * from v $ object_usage; INDEX_NAME TABLE_NAME mon use START_MONITORING END_MONITORING begin certificate ------- ------ ------------------------------------------------- T_IDX1 t yes 04/18/2013 00:23:06 you can see that USE has changed to YES, indicating that you. Disable monitoring indexes. SQL> alter index t_idx1 nomonitoring usage; Index altered. let's take a look at the dynamic performance view. The SQL> select * from v $ object_usage; INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING has been created -------- ~~t_idx1 T NO YES 04/18/2013 00:23:06 can be seen at this time, MON is changed to NO, and the monitoring period is also marked.