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.