Clear useless indexes in Oracle to improve DML Performance

DML performance is low. One of the most serious causes is the existence of useless indexes. All SQL insert, update, and delete operations will become slower when they need to modify a large number of indexes when each row of data is changed.
Many Oracle administrators assign an index to a column of WHERE statements in an SQL query. Although this method can make SQL run faster, the function-based Oracle index makes it possible for database administrators to allocate excessive indexes on the rows of data tables. Excessively allocating indexes seriously affects the performance of key Oracle data tables.
Before Oracle9i appeared, there was no way to determine which indexes were not used for SQL queries. Let's take a look at what methods Oracle9i provides for you to locate these indexes and delete them.
The process is quite simple. Oracle9i has a tool that allows you to monitor the use of indexes using the alter index Command. Then you can search for unused indexes and delete them from the database.
The following is a script that enables the monitoring of all indexes in a system:
Set pages 999;
Set heading off;
Spoolrun_monitor. SQL
Select
'Alter Index' | owner | '.' | index_name | 'monitoring usage ;'
From
Dba_indexes
Where
Owner not in ('sys ', 'system', 'perfstat ')
;
Spool off;
@ Run_monitor
 
Wait for a while until enough SQL statements are run on the database, and then you can query the new V $ OBJECT_USAGE view.
Select
Index_name,
Table_name,
Mon,
Used
From
V $ object_usage;
In V $ OBJECT_USAGE, a column is called USED and its value is YES or NO. Unfortunately, it won't tell you how many times Oracle has used this index, but this tool is useful for finding out unused indexes.
Note:
Set heading off -- disable the title of the result line
Spool c: \ dropTable. SQL
Select 'drop table' | table_name | ';' from user_tables;
Spool off

In this way, you can simply edit c: \ dropTable. SQL to obtain a batch SQL statement for deleting the table of the current user.