Index Monitoring in Oracle 12.2

APPLIES TO:

Oracle Database – Enterprise Edition – Version 12.2.0.1 and later
Information in this document applies to any platform.

GOAL

This document explains new features of Index Monitoring in Oracle 12.2.

SOLUTION

There is a big improvement in Index Monitoring in Oracle 12.2.

In previous releases, we could monitor index usage as follows.

1. Enable MONITORING for the index.

SQL> alter index <Index_Name> monitoring usage;

2. See if the index has been used by querying v$object_usage.

Reference :

Document 136642.1 Identifying Unused Indexes with the ALTER INDEX MONITORING USAGE Command

This old feature has some limitations such as not knowing out how many times the index has been used.

In 12.2, index monitoring is enabled by default and can track index usage more accurately.

Relevant parameters
~~~~~~~~~~~~~~~~~~~
The underscore parameter “_iut_stat_collection_type” controls index usage monitoring type.
It has two values SAMPLED and ALL. It is SAMPLED by default.
It should be ALL to get the most accurate result. But, it can cause some overhead.
So, it is recommended to set ALL only during the monitoring period.

ALTER SYSTEM SET “_iut_stat_collection_type” = ALL;

Relevant views
~~~~~~~~~~~~~~
V$INDEX_USAGE_INFO keeps track of index usage since the last flush. A flush occurs every 15 minutes. After each flush, ACTIVE_ELEM_COUNT is reset to 0 and LAST_FLUSH_TIME is updated to the current time.

INDEX_STATS_COLLECTION_TYPE=1 if _iut_stat_collection_type = SAMPLED.
INDEX_STATS_COLLECTION_TYPE=0 if _iut_stat_collection_type = ALL.

SQL> DESC v$index_usage_info
Name Null? Type
—————————————– ——– —————————-
INDEX_STATS_ENABLED NUMBER
INDEX_STATS_COLLECTION_TYPE NUMBER
ACTIVE_ELEM_COUNT NUMBER
ALLOC_ELEM_COUNT NUMBER
MAX_ELEM_COUNT NUMBER
FLUSH_COUNT NUMBER
TOTAL_FLUSH_DURATION NUMBER
LAST_FLUSH_TIME TIMESTAMP(3)
STATUS_MSG VARCHAR2(256)
CON_ID NUMBER

DBA_INDEX_USAGE displays object-level index usage once it has been flushed to disk.

SQL> DESC dba_index_usage
Name Null? Type
—————————————– ——– —————————-
OBJECT_ID NOT NULL NUMBER
NAME NOT NULL VARCHAR2(128)
OWNER NOT NULL VARCHAR2(128)
TOTAL_ACCESS_COUNT NUMBER
TOTAL_EXEC_COUNT NUMBER
TOTAL_ROWS_RETURNED NUMBER
BUCKET_0_ACCESS_COUNT NUMBER
BUCKET_1_ACCESS_COUNT NUMBER
BUCKET_2_10_ACCESS_COUNT NUMBER
BUCKET_2_10_ROWS_RETURNED NUMBER
BUCKET_11_100_ACCESS_COUNT NUMBER
BUCKET_11_100_ROWS_RETURNED NUMBER
BUCKET_101_1000_ACCESS_COUNT NUMBER
BUCKET_101_1000_ROWS_RETURNED NUMBER
BUCKET_1000_PLUS_ACCESS_COUNT NUMBER
BUCKET_1000_PLUS_ROWS_RETURNED NUMBER
LAST_USED DATE

Demo in HR demo schema
~~~~~~~~~~~~~~~~~~~~~~

SQL> conn hr/hr

SQL> alter session set nls_date_format = ‘DD-MON-RR hh24:mi:ss’;

INDEX_STATS_ENABLED INDEX_STATS_COLLECTION_TYPE ACTIVE_ELEM_COUNT LAST_FLUSH_TIME
——————- ————————— —————– ———————————–
1 1 0 29-AUG-17 12.16.32.572 PM

SQL> alter session set “_iut_stat_collection_type” = all;

SQL> select INDEX_STATS_ENABLED, INDEX_STATS_COLLECTION_TYPE, ACTIVE_ELEM_COUNT, LAST_FLUSH_TIME from v$index_usage_info;

INDEX_STATS_ENABLED INDEX_STATS_COLLECTION_TYPE ACTIVE_ELEM_COUNT LAST_FLUSH_TIME
——————- ————————— —————– ———————————–
1 0 0 29-AUG-17 12.16.32.572 PM