Index Usage – 1

In 12.2 Oracle introduced Index Usage Tracking to replace the previous option for “alter index xxx monitoring usage”. A recent post on the Oracle database discussion forum prompted me to look for articles about this “new” feature and what people had to say about it. There didn’t seem to be much information online – just a handful of articles starting with Tim Hall a few years ago and ending with Maria Colgan a few months ago – so I thought I’d update my notes a little and publish them.

Unfortunately, by the time I’d written the first 6 pages it was starting to feel like very heavy going, so I decided to rewrite it as a mini-series. In part one I’ll just give you some descriptions and explanations that are missing from the manuals; in part two I’ll do a bit of a fairly shallow dive to talk about what’s happening behind the scenes and how you can do some experiments; in part three I’ll describe some of the experiments and show the results that justify the descriptions I’ve given here in part one.

History

In the bad old days you could enable “monitoring” on an index to see if it was being used. The command to do this was:

alter index {index name} monitoring usage;

After executing this statement you would wait for a bit then check the view dba_object_usage:

SQL> desc dba_object_usage
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 OWNER                         NOT NULL VARCHAR2(128)
 INDEX_NAME                    NOT NULL VARCHAR2(128)
 TABLE_NAME                    NOT NULL VARCHAR2(128)
 MONITORING                             VARCHAR2(3)
 USED                                   VARCHAR2(3)
 START_MONITORING                       VARCHAR2(19)
 END_MONITORING                         VARCHAR2(19)

SQL> select * from dba_object_usage;

OWNER           INDEX_NAME           TABLE_NAME                MON USE START_MONITORING    END_MONITORING
--------------- -------------------- ------------------------- --- --- ------------------- -------------------
TEST_USER       T2_I1                T2                        YES YES 03/12/2024 15:31:35

1 row selected.

As you can see, this didn’t give you much information – just “yes it has been used” or “no it hasn’t been used” since the moment you started monitoring it; and that’s almost totally useless as an aid to measuring or understanding the effectiveness of the index.

Apart from the almost complete absence of information, there were collateral issues: I think that, initially, gathering stats, index rebuilds and using explain plan would flag an index as used; at the opposite extreme indexes that were actually used to avoid foreign key locking problems were not flagged as used.

And now for something completely different

The promise of Index Usage Tracking is clearly visible in the description of the view you use to report the details captured:

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

Though the columns are not very well described in the reference manuals you can see very clearly that there’s a lot more detail than just “yes/no” here. The columns clearly carry information about “how many times” and “how much data”, breaking the numbers down across a small range-based histogram. Here’s an example of output (using Tom Kyte’s print_table() routine to turn columns to rows):

SQL> execute print_table('select * from dba_index_usage where name = ''T1_I1''')
OBJECT_ID                      : 206312
NAME                           : T1_I1
OWNER                          : TEST_USER
TOTAL_ACCESS_COUNT             : 889
TOTAL_EXEC_COUNT               : 45
TOTAL_ROWS_RETURNED            : 17850
BUCKET_0_ACCESS_COUNT          : 0
BUCKET_1_ACCESS_COUNT          : 0
BUCKET_2_10_ACCESS_COUNT       : 0
BUCKET_2_10_ROWS_RETURNED      : 0
BUCKET_11_100_ACCESS_COUNT     : 878
BUCKET_11_100_ROWS_RETURNED    : 13200
BUCKET_101_1000_ACCESS_COUNT   : 9
BUCKET_101_1000_ROWS_RETURNED  : 1650
BUCKET_1000_PLUS_ACCESS_COUNT  : 2
BUCKET_1000_PLUS_ROWS_RETURNED : 3000
LAST_USED                      : 11-mar-2024 20:26:26

The order of the columns is just a little odd (in my opinion) so I’ve switched two of them around in my descriptions below:

• Total_exec_count: is the total number of executions that have been captured for SQL statements using this index.
• Total_access_count: is the total number of scans of this index that have been observed. If you think of a nested loop join you will appreciate that a single execution of an SQL statement could result in many accesses of an index – viz: an index range scan into the inner (second) table may happen many times, once for each row acquired from the outer (first) table.
• Total_rows_returned: carries a little trap in the word rows, and in the word returned. In this context “rows” means “index entries”, and “returned” means “passed to the parent operation”. (To be confirmed / clarified)
• Bucket_0_access_count: how many index accesses found no rows and there’s no bucket_0_row_count needed because it would always be 0).
• Bucket_1_access_count: how many index accesses found just one row (and there’s no bucket_1_row_count because that would always match the access count).
• Bucket_M_N_access_count: how many index accesses found between M and N rows.
• Bucket_M_N_row_count: sum of rows across all the index accesses that returned between M and N rows.
• Last_used: date and time of the last flush that updated this row of the table/view.

The most important omission in the descriptions given in the manuals is the difference between total_exec_count and total_access_count. (It was a comment on Maria Colgan’s blog note asking about the difference that persuaded me that I really had to write this note.) If you don’t know what an “access” is supposed to be you can’t really know how to interpret the rest of the numbers.

Take another look at the sample output above, it shows 45 executions and 889 accesses – I happen to know (because I did the test) that most of the work I’ve done in this interval has been reporting a two-table join that uses a nested loop from t2 into t1 using an index range scan on index t1_i1 to access table t1. I know my data well enough to know that every time I run my query it’s going to find about 20 rows in t2, and that for every row I find in t2 there will be roughly 15 rows that I will access in t1 through the index.

Give or take a little extra activity round the edges that blur the numbers I can see that the numbers make sense:

• 45 executions x 20 rows from t2 = 900 index range scans through t1_i1
• 878 index ranges scans x 15 rows per scan = 13,170

The numbers are in the right ball-park to meet my expectations. But we do have 11 more accesses reported – 9 of them reported an average of 1,650/9 = 183 rows, 2 of them reported an average of 3,000/2 = 1500 rows. Again, I know what I did, so I can explain why those numbers have appeared, but in real life you may have to do a little work to find a reasonable explanation (Spoilers: be suspicious about gathering index stats)

It’s possible, for example, that there are a few rows in the t2 table that have far more than the 15 row average in t1 and the larger numbers are just some examples from the nested loop query that happened to hit a couple of these outliers in t2. (It’s worth highlighting, as a follow-up to this suggestion, that a single execution could end up reporting accesses and row counts in multiple buckets.)

In fact the 9 “medium sized” access were the result of single table queries using a “between” clause that ranged through 10 to 15 values of t1 (returning 150 to 225 rows each), and the two “large” accesses were the result of two index-only queries where I forced an index full scan and an index fast full scan that discarded half the rows of an index holding 3,000 entries.

As I said, I’ll be presenting a few examples in part 3, but a guideline that may be helpful when considering the executions, accesses, and rowcounts is this: if you’re familiar with the SQL Monitor report then you’ll know that each call to dbms_sql_monitor.report_sql_monitor() reports one execution – then the Starts column for any index operation will (probably) be the total access count, and the Rows (Actual) column will (probably) be the total rows returned. As noted above, though, any one execution may end up splitting the total Starts and Rows (Actual) across multiple buckets.

Some questions to investigate

I hope this has given you enough information to get you interested in Index Usage Tracking, and some idea of what you’re looking at when you start using the view. There are, however, some technical details you will need to know if you want to do some testing before taking any major steps in production. There are also some questions that ought to be addressed before jumping to conclusions about what the numbers mean, so I thought I’d list several questions that came to mind when I first read about the feature:

• Does a call to dbms_stats.gather_index_stats result in an update to the index usage stats, and does it matter?
• Does a call to explain plan result in an update to the index usage stats, and does it matter.
• Do referential integrity checks result in the parent or child indexes being reported in the usage stats. What if there is a parent delete with “on delete cascade” on the child.
• Do inserts, updates, deletes or merges produce any unexpected results (e.g. double / quadruple counting); what if they’re PL/SQL forall bulk processing, what if (e.g.) you update or delete through a join view.
• Does an index skip scan count as a single access, or does Oracle count each skip as a separate access (I’d hope it would be one access).
• If you have an index range scan with a filter predicate applied to each index entry after the access predicate is the “rows returned” the number of index entries examined (accessed), or the number that survive the filter. (I would like it to be the number examined because that’s the real measure of the work done in the index but the name suggests it counts the survivors.)
• Does an index fast full scan get reported correctly.
• Are IOTs accounted differently from ordinary B-tree indexes
• For bitmap indexes what is a “row” and what does the tracking information look like?
• If you have an “Inlist Iterator” operation does this get summed into one access, or is it an access per iteration (which is what I would expect). And how is the logic applied with partitioned table iteration.
• Does a UNION or UNION ALL operation count multiple accesses (I would expect so), and what happens with things like nvl_or_expansion with “conditional” branches.
• Does a “connect by pump” through an index produce any unexpected results
• Can index usage tracking tell us anything about Domain indexes
• Are there any types of indexes that are not tracked (sys indexes, for example)

If you can think of any other questions where “something different” might happen, feel free to add them as comments.

Summary

Index Usage Tracking (and the supporting view dba_index_usage) can give you a good insight into how Oracle is using your indexes. This note explains the meaning of data reported in the view and a couple of ideas about how you may need to interpret the numbers for a single index.

In the next two articles we’ll look at some of the technical aspects of the feature (including how to enable and test it), and the results captured from different patterns of query execution, concluding (possibly in a 4th article) in suggestions of how to use the feature in a production system.

Footnote

At the start of this note I said it had been prompted by a question on one of the Oracle forums. The thread was about identifying indexes that could be dropped and the question was basically: “Is the old index monitoring obsolete?” The answer is “Yes, definitely, and it has been for years.”