The longer an application has been successfully run, the more likely you are to have indexes that are no longer used or beneficial. Removing these indexes not only saves space but can also improve the performance of any DML operations.
But knowing which indexes can be safely dropped can be tricky.
In Oracle Database 12c, things got a little easier with the introduction of a new view called DBA_INDEX_USAGE.
The DBA_INDEX_USAGE view displays cumulative index usage statistics, but unlike previous releases, it’s not just a binary value (YES or NO). This new view shows how often an index was used, who accessed it, and how effective it was via histogram based on the number of rows returned. The index access information is captured in memory and periodically flushed to disk every 15 minutes. You can query the last_flush_Time in v$INDEX_USAGE_INFO to determine when it was updated.
As always, it is easier to understand the benefits of a dictionary view by looking at an example. In my example below, I join the DBA_INDEX_USAGE view to USER_INDEXES via an outer join to ensure I capture information on indexes that are never used because these indexes won’t appear in DBA_INDEX_USAGE at all.
SELECT i.index_name, u.total_access_count tot_access, u.total_exec_count exec_cnt, u.bucket_0_access_count B0, u.bucket_1_access_count B1, u.bucket_2_10_access_count B2_10, u.bucket_11_100_access_count B11_100, u.bucket_101_1000_access_count B101_1K, u.bucket_1000_plus_access_count B1K, u.last_used FROM DBA_INDEX_USAGE u RIGHT JOIN DBA_INDEXES i ON i.index_name = u.name WHERE i.owner='MARIA' ORDER BY u.total_access_count;
The output of this query should look something like this.
INDEX_NAME TOT_ACCESS EXEC_CNT B0 B1 B2_10 B11_100 B101_1K B1K LAST_USED ---------------- ---------- ---------- ------- ------- ------- -------- ---------- ---------- ----------- PROD_CUST_SALES 1 1 0 0 0 0 0 1 06-APR-23 INDX_LOC 2 2 0 1 1 0 0 0 12-APR-23 INDX_DEPTNO 19 19 18 1 0 0 0 0 26-APR-23 PROD_SUP_INDX 27 27 25 0 0 0 2 0 26-APR-23 EMPNO_PK_IND 82 82 48 32 0 2 0 0 26-APR-23 CHAN_SOLD PROD_SUB_IDX
As you can see in the output above, for each index, we get the total number of accesses (TOT_ACCESS), the execution count (EXEC_CNT), the last date the index was used (LAST_USED), and a histogram of how many rows were returned across several buckets.
Let’s look at the EMPNO_PK INDEX index (the third row from the bottom). This index returned 0 rows 48 times out of 82 total accesses and a single row 32 times. So, it looks like this index is commonly used and valuable.
However, if we look at PROD_CUST_SALES (the first row), it was accessed only once and returned over 1,000 rows. Is that a helpful index?
Maybe/maybe not. It may be beneficial if this index access replaces a full table scan of an extremely wide table. If, on the other hand, it’s only marginally cheaper than a full table scan, it may not be worth the overhead of maintaining the index.
But perhaps the most exciting part of the query output above is that we have two indexes (CHAN_SOLD, PROD_SUB_IDX) that have never been accessed. These are good candidates to be dropped.
But dropping an index can be risky. What can you do to ensure you don’t get fired for dropping the wrong index?
Rather than dropping the index immediately, marking the index invisible is far easier. The optimizer will be unaware of the index in the future and won’t select it as part of any execution plan. However, the index will continue to be maintained.
ALTER INDEX prod_sub_idx INVISIBLE;
If no one complains about their query performance digressing after some time, you can safely drop the index. If, on the other hand, someone does complain, you can alter the index visible again in a matter of seconds.
ALTER INDEX prod_sub_idx VISIBLE;
You can also create a new index as invisible. The optimizer is unaware of the new index until you can verify it improves performance by setting the parameter OPTIMIZER_USE_INVISBLE_INDEXES within a single session. This will allow the optimizer to consider the new index only for SQL statements issued within your session. All other sessions will continue to use the existing execution plans.
-- New indexes can be marked invisible until you have an opportunity to prove they improve performance CREATE INDEX my_idx ON t(x, object_id) INVISIBLE; -- Test newly created invisible indexes by setting OPTIMIZER_USE_INVISBLE_INDEXES to TRUE ALTER SESSION SET optimizer_use_invisible_indexes = TRUE;
There is only one day to go until
Plus, our very own Juan Loaiza will take to the main stage to deliver the database and developer keynote (