Monday , April 22 2024

Monitoring Index Usage On Oracle


Indexes are usually not maintained in Oracle databases. But indexes need to be checked regularly. If an index is created and not checked, it may cause trouble. You may encounter an index that has reached a very large size in the future, spent serious db time, slowed down the processes in the table, but was not used in any query.

The use of indexes should be monitored. Unused indexes should be dropped.


When the index usage monitoring is turned on, we can find out whether the relevant index is used by the Cost Based Optimizer. This information is not sufficient to drop the index. For example, the use of primary key indexes will always be NO. Therefore, when it is dropped, it causes serious problems.

In summary, unused indexes can be dropped if they are not associated with the primary key or any sql profile.

Open Index Usage Monitoring:

Once the monitor is opened, you can watch it with v $ object_usage. In addition, if you create a view as below, you will have the opportunity to examine more in detail.

I would like to point out that the monitoring of its usage will bring extra load to the system. After you are done, you can close it as follows.

Close Index Usage Monitoring:


About dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *