Monitor index usage (Remove useless indexes)

Loading the editor... a system may produce a large number of indexes after long-term operation, maintenance, and version updates. Even the space occupied by indexes is much larger than the space occupied by data. Many indexes are useful for the system during initial design. However, after system upgrades, data table structure adjustments, and application changes, many indexes are gradually unavailable and become junk indexes. These indexes occupy a large amount of data space, increase the amount of system maintenance, and even reduce system performance. Therefore, DBAs should identify junk indexes based on system changes to reduce system weight.
After Oracle 9i, You can monitor the index to check whether the index is used in the system. Syntax:

Alter Index Monitoring usage;

To cancel monitoring, use the following statement:

Alter Index Nomonitoring usage;

After setting monitoring, You can query view v $ object_usage to check whether the index is used.

The following is a demo:

SQL> select * from V $ object_usage;

Index_name table_name monitoring used start_monitoring end_monitoring
----------------------------------------------------------------------------------------------------------------

SQL> alter index quest_template_idx monitoring usage;

Index altered

SQL> select count (*) from quest_template
2 Where minlevel> = 38
3 and maxlevel select * from V $ object_usage;

Index_name table_name monitoring used start_monitoring end_monitoring
----------------------------------------------------------------------------------------------------------------
Quest_template_idx quest_template Yes 05/22/2007 14:02:51

However, this method may have a problem: for a complex system, the number of indexes may be large, so we are skeptical about how to identify those indexes, what should be monitored? In other words, how can we reduce the monitoring scope? Here we will introduce several methods.

1. Use library cache data
In the library cache, the query plan for the midstream objects in the system is stored (not all, limited by the size of the library cache). Through the view v $ SQL _plan, We can query the data. Using this data, we can exclude the indexes that appear in the query plan:

Select a. object_owner, A. object_name
From v $ SQL _plan A, V $ sqlarea B
Where a. SQL _id = B. SQL _id
And a. object_type = 'index'
And B. last_load_time> ;

2. Use the statspack table

After statspack is created, a series of tables starting with stats $ are created to record snapshot statistics. The stats $ SQL _plan table records the query plan of the statements that exceed the threshold value of each snapshot. Therefore, we can exclude the index objects in this table from the monitoring scope:

Select a. object_owner, A. object_name

From stats $ SQL _plan A, stats $ SQL _plan_usage B

Where a. plan_hash_value = B. plan_hash_value

And a. object_type = 'index'

And B. last_active_time> ;

However, this table does not record data by default (snapshot level = 5). Only snapshot> = 6 records. In addition, this table does not exist in 8i.

3. Use AWR data

After 10 Gbit/s, Oracle has a more powerful performance analysis tool AWR than statspack, which also records statistics in the system for analysis. We can also analyze the indexes from which they are used.

Select B. object_owner, B. object_name

From dba_hist_snapshot A, dba_hist_ SQL _plan B, dba_hist_sqlstat C

Where a. snap_id = C. snap_id

And B. SQL _id = C. SQL _id

And B. object_type = 'index'

And a. startup_time> ;

The above method filters out most of the indexes that are certainly used, and then comprehensively applies them. It selects suspicious indexes for monitoring, finds and deletes useless indexes, and loses weight for the database.