Index Usage Tracking in Oracle Database 12c Release 2 (12.2)

Index usage tracking in Oracle 12.2 replaces the old index monitoring functionality of previous versions.

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 usage tracking allows unused indexes to be identified, helping to removing the risks associated with dropping useful indexes.

It is important to make sure that index usage tracking is performed over a representative time period. If you only check index usage during specific time frame you may incorrectly highlight indexes as being unused. The safest method is to use a tracking 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

Setup

The examples in this article require the following schema objects.

CONN test/test@pdb1

DROP TABLE temp1 PURGE;

CREATE TABLE temp1 (
  id           NUMBER,
  description  VARCHAR2(50),
  created_date  DATE,
  CONSTRAINT t1_pk PRIMARY KEY (id)
);

INSERT INTO temp1
SELECT level,
       'Description for ' || level,
       TRUNC(SYSDATE)
FROM   dual
CONNECT BY level <= 20;

CREATE INDEX temp1_description_idx ON temp1(description);
CREATE INDEX temp1_created_date_idx ON temp1(created_date);

-- Not needed for table segment. Picking up index stats.
EXEC DBMS_STATS.gather_table_stats(USER, 'temp1');

To Sample Or Not

Before we continue it is important to mention the impact of sampling on index usage tracking. The sampling approach is likely to notice the index usage in a normal running system, where indexes are repeatedly being accessed, but it can easily miss indexes used for one-off tests like those in this article.

ALTER SESSION SET "_iut_stat_collection_type"=ALL;
ALTER SESSION SET "_iut_stat_collection_type"=SAMPLED;

For the rest of this article I will have this parameter set to “ALL” for the session, so the results you see will be consistent. I would not suggest this on a live system unless you’ve tested and are happy with the overhead.

Also keep this in mind when you are trying to decide if an index is unused. Is it really unused, or has the index usage tracking just not noticed its use?

V$INDEX_USAGE_INFO

Index usage is tracked in memory, with top-level usage information visible using the V$INDEX_USAGE_INFO view.

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

SQL>
  • INDEX_STATS_ENABLED : Default 1 means index statistics are enabled. 0 means disabled.
  • INDEX_STATS_COLLECTION_TYPE : Default 1 means index statistics are sampled. 0 means all usage is tracked. Sampling means the data is less accurate, but there is less overhead.
  • ACTIVE_ELEM_COUNT : The number of active indexes since the last flush.
  • LAST_FLUSH_TIME : The last time the statistics were flushed to disk.

Every 15 minutes the index usage is flushed from memory to disk, the LAST_FLUSH_TIME is updated and the ACTIVE_ELEM_COUNT is set to 0. You can only see the object-level detail once a flush occurs, so you will need to keep an eye on the LAST_FLUSH_TIME value when you are monitoring index usage.

The following query checks the V$INDEX_USAGE_INFO view, performs some actions that use indexes and checks the view again.

-- Check statistics.
SET LINESIZE 120
COLUMN last_flush_time FORMAT A30

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 20-NOV-17 20.00.00.015

1 row selected.

SQL>


-- Perform some actions that use indexes.
SELECT * FROM temp1 WHERE id = 10;
SELECT * FROM temp1 WHERE description = 'Description for 10';
SELECT * FROM temp1 WHERE created_date = TRUNC(SYSDATE-1);
SELECT * FROM temp1 WHERE id = 11;
SELECT * FROM temp1 WHERE description = 'Description for 11';
SELECT * FROM temp1 WHERE created_date = TRUNC(SYSDATE);
SELECT * FROM temp1 WHERE id = 12;
SELECT * FROM temp1 WHERE description = 'Description for 12';
SELECT * FROM temp1 WHERE created_date = TRUNC(SYSDATE+1);


-- Check statistics.
SET LINESIZE 120
COLUMN last_flush_time FORMAT A30

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                 3 20-NOV-17 20.00.00.015

1 row selected.

SQL>

We can see all three indexes were active since the last flush.

DBA_INDEX_USAGE

The DBA_INDEX_USAGE view 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

SQL>

The columns are described here. In addition to the basic usage information there are columns representing usage histograms to give a little more information on the type of usage.

Remember, you will have to wait for a flush before this view will contain information.

The following query displays the index usage information.

SET LINESIZE 140
COLUMN owner FORMAT A30
COLUMN name  FORMAT A30

SELECT owner,
       name,
       total_access_count,
       total_exec_count,
       total_rows_returned,
       last_used
FROM   dba_index_usage
WHERE  owner = 'TEST'
ORDER BY 1, 2;

OWNER                          NAME                           TOTAL_ACCESS_COUNT TOTAL_EXEC_COUNT TOTAL_ROWS_RETURNED LAST_USED
------------------------------ ------------------------------ ------------------ ---------------- ------------------- ---------
TEST                           TEMP1_CREATED_DATE_IDX                             3                3                  20 20-NOV-17
TEST                           TEMP1_DESCRIPTION_IDX                              3                3                   3 20-NOV-17
TEST                           TEMP1_PK                                           3                3                   3 20-NOV-17

SQL>

The following query displays the access histogram for the indexes. Focusing on the date column index we can see two accesses in the BUCKET_0_ACCESS_COUNT bucket. No rows were returned and therefore it required no subsequent index accesses. There was one occurrence of an access that required between 11 and 100 access, when we queried the 20 rows for the current day.

SET LINESIZE 200

SELECT name,
       bucket_0_access_count,
       bucket_1_access_count,
       bucket_2_10_access_count,
       bucket_11_100_access_count,
       bucket_101_1000_access_count,
       bucket_1000_plus_access_count
FROM   dba_index_usage
WHERE  owner = 'TEST'
ORDER BY 1;

NAME                           BUCKET_0_ACCESS_COUNT BUCKET_1_ACCESS_COUNT BUCKET_2_10_ACCESS_COUNT BUCKET_11_100_ACCESS_COUNT BUCKET_101_1000_ACCESS_COUNT BUCKET_1000_PLUS_ACCESS_COUNT
------------------------------ --------------------- --------------------- ------------------------ -------------------------- ---------------------------- -----------------------------
TEMP1_CREATED_DATE_IDX                                2                     0                        0                          1                            0                             0
TEMP1_DESCRIPTION_IDX                                 0                     3                        0                          0                            0                             0
TEMP1_PK                                              0                     3                        0                          0                            0                             0

SQL>

The following query displays the rows histogram for the indexes. What we are expecting is the total number of rows returned in the TOTAL_ROWS_RETURNED column and the sum of the rows returned by queries in each row range bucket. Focusing on the date column index, we ran two queries returning no rows and a single query returning 20 rows, so we expect to see a value of 20 in the TOTAL_ROWS_RETURNED bucket and a value of 20 in the BUCKET_11_100_ROWS_RETURNED column.

SELECT name,
       total_rows_returned,
       bucket_2_10_rows_returned,
       bucket_11_100_rows_returned,
       bucket_101_1000_rows_returned,
       bucket_1000_plus_rows_returned
FROM   dba_index_usage
WHERE  owner = 'TEST'
ORDER BY 1;

NAME                           TOTAL_ROWS_RETURNED BUCKET_2_10_ROWS_RETURNED BUCKET_11_100_ROWS_RETURNED BUCKET_101_1000_ROWS_RETURNED BUCKET_1000_PLUS_ROWS_RETURNED
------------------------------ ------------------- ------------------------- --------------------------- ----------------------------- ------------------------------
TEMP1_CREATED_DATE_IDX                             20                         0                          20                             0                              0
TEMP1_DESCRIPTION_IDX                               3                         0                           0                             0                              0
TEMP1_PK                                            3                         0                           0                             0                              0

SQL>

At the time of writing the documentation on this histogram is not clear. I’ve confirmed with Oracle this is the expected behaviour and the documentation will be updated to reflect this, but in my opinion the histogram columns should record the number of occurrences of queries returning rows in that range, rather than the sum of the rows in that range. I would have expected BUCKET_11_100_ROWS_RETURNED=1 and TOTAL_ROWS_RETURNED=20, but that’s just my opinion.

Foreign Key Indexes

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

The fact that index usage tracking is on by default in Oracle 12.2 is a really neat addition. The default action of sampling index usage statistics means the are possibilities for inaccuracies. I literally never got index usage detected until I switched to the collection type of “ALL”.The indexes being marked as accessed during statistics gathering, and indexes supporting foreign key locking not being marked as used. Just keep in mind using the default sampling approach is not perfect, but switching to “ALL” may represent an significant overhead.

As mentioned previously, the index usage tracking 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.

As mentioned previously, use index usage tracking as a guide line, not an absolute. You must engage your brain before dropping indexes.

Leave a Reply

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