Use alter index & #215; & #215; & #215; monitoring usage; statement to monitor whether an index is used or not. altermonitoring

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.