Application alterindexmonitoringusage; statement monitoring index usage

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 quickly locate these indexes so that they can be cleared

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 quickly locate these indexes so that they can be cleared

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 Table T
SQL> create table t (x int );

Table created.

-- 2. initialize a piece of data
SQL> 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 a10
SQL> col TABLE_NAME a10
SQL> col START_MONITORING for a20
SQL> col END_MONITORING for a20
SQL> 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 on
SQL> select * from t where x = 1;

X
----------
1

Execution 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 statement

Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
1 consistent gets
0 physical reads
0 redo size
508 bytes sent via SQL * Net to client
492 bytes encoded ed 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 off
SQL> select * from v $ object_usage;

INDEX_NAME TABLE_NAME monitorin used START_MONITORING END_MONITORING
---------------------------------------------------------------------------
I _T T 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;