DBA Hub

📋Steps in this guide1/6

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

Index usage tracking in Oracle 12.2 replaces the index monitoring functionality of previous versions. Use it to help identify unused indexes.

oracle 12cconfigurationintermediate
by OracleDba
12 views
1

Setup

The examples in this article require the following schema objects.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CONN test/test@pdb1

DROP TABLE t1 PURGE;

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

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

CREATE INDEX t1_description_idx ON t1(description);
CREATE INDEX t1_created_date_idx ON t1(created_date);

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

To Sample Or Not

Before we continue it is important to mention the impact of sampling on index usage tracking. By default index usage is sampled, rather than tracking all index usage. When I first wrote this article I was having trouble getting the index usage tracking views to notice my tests. 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. I Googled and found a post by Franck Pachot who mentioned the following switch to control the statistics collection type. 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?

Code/Command (click line numbers to comment):

1
2
ALTER SESSION SET "_iut_stat_collection_type"=ALL;
ALTER SESSION SET "_iut_stat_collection_type"=SAMPLED;
3

V$INDEX_USAGE_INFO

Index usage is tracked in memory, with top-level usage information visible using the view. The columns of the view are described in full here , but here are some that standout. - : Default 1 means index statistics are enabled. 0 means disabled. - : 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. - : The number of active indexes since the last flush. - : The last time the statistics were flushed to disk. Every 15 minutes the index usage is flushed from memory to disk, the is updated and the 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 value when you are monitoring index usage. The following query checks the view, performs some actions that use indexes and checks the view again. We can see all three indexes were active since the last flush.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
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>

-- 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
15-AUG-17 20.00.00.015

1 row selected.

SQL>


-- Perform some actions that use indexes.
SELECT * FROM t1 WHERE id = 10;
SELECT * FROM t1 WHERE description = 'Description for 10';
SELECT * FROM t1 WHERE created_date = TRUNC(SYSDATE-1);
SELECT * FROM t1 WHERE id = 11;
SELECT * FROM t1 WHERE description = 'Description for 11';
SELECT * FROM t1 WHERE created_date = TRUNC(SYSDATE);
SELECT * FROM t1 WHERE id = 12;
SELECT * FROM t1 WHERE description = 'Description for 12';
SELECT * FROM t1 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
15-AUG-17 20.00.00.015

1 row selected.

SQL>
4

DBA_INDEX_USAGE

The view displays object-level index usage once it has been flushed to disk. 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. The following query displays the access histogram for the indexes. Focusing on the date column index we can see two accesses in the 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. The following query displays the rows histogram for the indexes. What we are expecting is the total number of rows returned in the 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 bucket and a value of 20 in the column. 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 and , but that's just my opinion.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
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>

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                           T1_CREATED_DATE_IDX                             3                3                  20 15-AUG-17
TEST                           T1_DESCRIPTION_IDX                              3                3                   3 15-AUG-17
TEST                           T1_PK                                           3                3                   3 15-AUG-17

SQL>

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
------------------------------ --------------------- --------------------- ------------------------ -------------------------- ---------------------------- -----------------------------
T1_CREATED_DATE_IDX                                2                     0                        0                          1                            0
0
T1_DESCRIPTION_IDX                                 0                     3                        0                          0                            0                             0
T1_PK                                              0                     3                        0                          0                            0                             0

SQL>

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
------------------------------ ------------------- ------------------------- --------------------------- ----------------------------- ------------------------------
T1_CREATED_DATE_IDX                             20                         0                          20                             0
0
T1_DESCRIPTION_IDX                               3                         0                           0                             0                              0
T1_PK                                            3                         0                           0                             0                              0

SQL>
5

Foreign Key Indexes

To quote the Oracle Database Concepts manual. > "As a rule, foreign keys should be indexed. 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. The index usage tracking mechanism doesn't detect indexes used in this scenario, as demonstrated by Franck Pachot .
6

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". Franck Pachot demonstrated 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. For more information see: Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!