Use alter index ××monitoring usage; statement to monitor the index usage, altermonitoring
With the accumulation of time, in the absence of good planning, the database may have a large number of indexes that are not used for a long time. If you can quickly locate these indexes so that they can be cleared, they will be placed in the case. We can use the "alter index ××monitoring usage;" command to set the index to the monitored state, after a certain monitoring period, the unused indexes are displayed in the v $ object_usage view of the specific Schema. This process is displayed for reference.
Tip: You must be careful when adding and deleting indexes in the production database. You must perform a full test.
1. Prepare the environment
-- 1. create a table TSQL> create Table t (x int); table created. -- 2. initialize a data SQL statement> insert into t values (1); 1 row created. SQL> select * from t; X ---------- 1--3. create an index on the X field of table T. SQL> create Index I _t on t (x); index created.
2. Place the index I _T under monitoring
SQL> alter index I_T monitoring usage;Index altered.
3. view the recorded information in the v $ object_usage View
SQL> col INDEX_NAME for a10SQL> col TABLE_NAME a10SQL> col START_MONITORING for a20SQL> col END_MONITORING for a20SQL> select * from v$object_usage;INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING---------- ---------- ---------- --------- -------------------- -----------------I_T T YES NO 07/17/2010 22:27:13
The MONITORING field is "YES", indicating that the I _T is already under MONITORING. The USED field is "NO", indicating that the index has not been USED.
4. Simulated index used
SQL> set autot onSQL> select * from t where x = 1; X---------- 1Execution Plan----------------------------------------------------------Plan hash value: 2616361825-------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 ||* 1 | INDEX RANGE SCAN| I_T | 1 | 13 | 1 (0)| 00:00:01 |-------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("X"=1)Note----- - dynamic sampling used for this statementStatistics---------------------------------------------------------- 0 recursive calls 0 db block gets 1 consistent gets 0 physical reads 0 redo size 508 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
The execution plan shows that the query uses the index I _T.
5. view the record information in the v $ object_usage view again
SQL> set autot offSQL> select * from v$object_usage;INDEX_NAME TABLE_NAME MONITORIN USED START_MONITORING END_MONITORING---------- ---------- --------- --------- -------------------- -----------------I_T T YES YES 07/17/2010 22:27:13
The USED field changes to "YES", indicating that the I _T index has been USED during the monitoring period.
If the USED field remains in the "NO" state in a relatively scientific monitoring period, you can consider deleting this type of index.
6. Stop monitoring indexes and observe the changes in the v $ object_usage status.
SQL> alter index I_T nomonitoring usage;Index altered.sec@ora10g> select * from v$object_usage;INDEX_NAME TABLE_NAME MONITORIN USED START_MONITORING END_MONITORING---------- ---------- --------- --------- -------------------- -------------------I_T T NO YES 07/17/2010 22:27:13 07/17/2010 22:32:27
At this time, the MONITORIN field is "NO", indicating that the monitoring of index I _T has been stopped.
7. Enable index monitoring again to observe the status changes of v $ object_usage.
SQL> alter index I_T monitoring usage;Index altered.sec@ora10g> select * from v$object_usage;INDEX_NAME TABLE_NAME MONITORIN USED START_MONITORING END_MONITORING---------- ---------- --------- --------- -------------------- ------------------I_T T YES NO 07/17/2010 22:36:40
The MONITORIN field is "YES", indicating that the index I _T is under monitoring; the USED field is "NO", indicating that the index has not been USED during the period after monitoring is enabled again.
The process of stopping the index monitoring is equivalent to the process of resetting the index monitoring.
8. Generate monitoring statements for all indexes of the current user at a time.
You can use SQL to generate an SQL script.
Take the monitoring statement generated for all indexes under the SECOOLER user as an Example
SQL> select 'alter Index' | owner | '. '| index_name | 'monitoring usage;' as "Monitor Indices Script" from dba_indexes where owner in ('secoler'); Monitor Indices Script alter index SECOOLER. I _T monitoring usage ;...... Omitted ......
If you are familiar with PL/SQL, You can conveniently set indexes to monitored in batches.
SQL> conn secooler/secoolerSQL> begin 2 for rec in (select index_name from user_indexes) 3 LOOP 4 dbms_output.put_line (rec. index_name); 5 execute immediate 'alter Index' | rec. index_name | 'monitoring usage'; 6 end loop; 7 end; 8/I _T ...... Omit other index names ...... PL/SQL procedure successfully completed.
9. Summary
Generally, this method is rarely used in production databases (the premise is to do a good job of Planning) and is mostly used in Test Databases. Many indexes may be created in the test database for the purpose of testing various index combinations. Using this method, you can easily confirm the indexes that are not used.