Index Monitoring in Oracle Database

 

Index Monitoring

 In Oracle 12.2 this functionality has been replaced by Index Usage Tracking.

The database maintains all indexes defined against a table regardless of their usage. Index maintenance can cause significant amounts of CPU and I/O usage, which can be detrimental to performance in a write-intensive system. With this in mind, it makes sense to identify and remove any indexes that are not being used as they are a pointless drain on resources. Index monitoring allows unused indexes to be identified accurately, removing the risks associated with dropping useful indexes.

It is important to make sure that index monitoring is performed over a representative time period. If you only monitor indexes during specific time frames you may incorrectly highlight indexes as being unused. The safest method is to use a monitoring period which spans the whole lifecycle of your application, including any OLTP and batch operations. In a Decision Support System (DSS) this may involve a period of weeks or months, while OLTP systems typically have shorter cycles.

• ALTER INDEX
• Useful Scripts
• Foreign Key Indexes
• Limitations

Related articles.

• Index Usage Tracking (DBA_INDEX_USAGE, V$INDEX_USAGE_INFO) in Oracle Database 12c Release 2 (12.2)

ALTER INDEX

Index monitoring is started and stopped using the ALTER INDEX syntax shown below.

ALTER INDEX my_index_i MONITORING USAGE;
ALTER INDEX my_index_i NOMONITORING USAGE;

If you are using a version prior to Oracle 12.1, information about the index usage can be displayed using the V$OBJECT_USAGE view.

SELECT index_name,
       table_name,
       monitoring,
       used,
       start_monitoring,
       end_monitoring
FROM   v$object_usage
WHERE  index_name = 'MY_INDEX_I'
ORDER BY index_name;

The V$OBJECT_USAGE view does not contain an OWNER column so you must to log on as the object owner to see the usage data.

From Oracle 12.1 onward the V$OBJECT_USAGE view has been deprecated in favour of the {DBA|USER]}_OBJECT_USAGE views. The structure is the same, but the DBA_OBJECT_USAGE view includes an OWNER column.

SELECT index_name,
       table_name,
       monitoring,
       used,
       start_monitoring,
       end_monitoring
FROM   dba_object_usage
WHERE  index_name = 'MY_INDEX_I'
AND    owner = 'TEST'
ORDER BY index_name;

Useful Scripts

The following scripts may come in handy when setting up and reviewing index monitoring:

• index_monitoring_on.sql
• index_monitoring_off.sql
• index_monitoring_status.sql
• index_usage.sql

Foreign Key Indexes

To quote the Oracle Database Concepts manual:

"You should almost always index foreign keys. The only exception is when the matching unique or primary key is never updated or deleted."

When a foreign key is unindexed, DML on the parent primary key results in a share row exclusive table lock (or share-subexclusive table lock, SSX) on the child table, preventing DML from other transactions against the child table. If the DML affects several rows in the parent table, the lock on the child table is obtained and released immediately for each row in turn. Despite the speed of the lock-release process, this can cause significant amounts of contention on the child table during periods of heavy update/delete activity on the parent table.

When a foreign key is indexed, DML on the parent primary key results in a row share table lock (or subshare table lock, SS) on the child table. This type of lock prevents other transactions from issuing whole table locks on the child table, but does not block DML on either the parent or the child table. Only the rows relating to the parent primary key are locked in the child table.

This issue is mitigated somewhat in later releases, but it is still worth considering, and above all testing.

Limitations

Index monitoring is only a guideline and does not list all indexes used by the system. Prior to Oracle 12.2, index monitoring is based on the indexes selected for the execution plan during the parsing phase of optimisation. Once the statement runs, other indexes for recursive SQL, like constraint checking, will also be referenced, but may not be included in the object usage views. Use index monitoring as a guide line, not an absolute.

In Oracle 12.2 the mechanism behind index monitoring has been revamped, changing it from parse time to runtime, so the chances of an index that is used not making it into the object usage views is a lot lower.

As mentioned previously, the monitoring is only as good as the sample period you observe. Imagine you are checking usage for 11 months, decide to drop some indexes, then the following month you run some yearly reports which rely on those indexes. Once again, use index monitoring as a guide line, not an absolute.

https://blog.zeddba.com/2018/07/17/index-monitoring-in-oracle-database/