Oracle Database 12.2 - How to track index usage

    Jul 25, 2017 2:08:00 PM by Deiby Gomez

    Introduction

    Several articles have been written about how to track the usage of indexes and there are several scripts to determine which indexes are being used after monitoring for a while. In previous versions to 12cR2 of Oracle Database there is the clause “ALTER INDEX (…) MONITORING USAGE” that can be used for this. However Oracle 12.2 introduced two new views that automatically monitor index usage:

    V$INDEX_USAGE_INFO: V$INDEX_USAGE_INFO keeps track of index usage since the last flush. A flush occurs every 15 minutes. After each flush, ACTIVE_ELEM_COUNT is reset to 0 and LAST_FLUSH_TIME is updated to the current time.

    DBA_INDEX_USAGE: DBA_INDEX_USAGE displays cumulative statistics for each index.

    With these two new views, Oracle automatically tracks the usage of indexes. There are several columns in the dba_index_usage that can be used to find out how many accesses the indexes have received, how many rows have returned, and, even better, there are buckets to create histograms for accesses and rows returned. The most recent time that the index was used is also recorded.  

    In the following example, I will create a table with three columns, with one index in every column. Then I will run some queries against the table in order to use the indexes, and we will confirm that indeed Oracle 12.2 tracks the usage.

     

    Creating the table

    SQL> create table dgomez.table1 (id number, val1 varchar2(20), val2 varchar2(20));
    Table created.

     

    Creating an Index in each column

    SQL> create index dgomez.idx_id on dgomez.table1(id);
    Index created.
     
    SQL> create index dgomez.idx_val1 on dgomez.table1(val1);
    Index created.
     
    SQL> create index dgomez.idx_val2 on dgomez.table1(val2);
    Index created.

     

    Perform some INSERTs in the table

    While the INSERTs sentences also impact the index (index entries must be created in the b-tree), this doesn’t count as “access”.

    SQL> insert into dgomez.table1 values (1,'a','b');
    SQL> insert into dgomez.table1 values (2,'b','c');
    SQL> insert into dgomez.table1 values (3,'c','d');
    SQL> insert into dgomez.table1 values (4,'d','e');
    SQL> insert into dgomez.table1 values (5,'e','f');
    SQL> insert into dgomez.table1 values (6,'f','g');
    SQL> insert into dgomez.table1 values (7,'g','h');
    SQL> insert into dgomez.table1 values (8,'h','i');
    SQL> insert into dgomez.table1 values (9,'i','j');
    SQL> insert into dgomez.table1 values (10,'j','k');
    SQL> insert into dgomez.table1 values (11,'k','l');
    SQL> commit;

     

    Executing some queries

    I will execute some queries. I have enabled autotrace to confirm that the query is using the index. This counts as an “access”. Also pay attention to how many rows each query has returned, since this count is also monitored by Oracle. At the end, we will list how many accesses and how many rows each index has returned and we will confirm whether the data displayed is correct.

     

    Using the index IDX_ID:

    SQL> select id from dgomez.table1 where id>1;

    10 rows selected.

    ---------------------------------------------------------------------------
    | Id | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    | 0  | SELECT STATEMENT |        | 10    | 130   | 1 (0)      | 00:00:01 |
    |* 1 | INDEX RANGE SCAN | IDX_ID | 10    | 130   | 1 (0)      | 00:00:01 |
    ---------------------------------------------------------------------------

    SQL> select id from dgomez.table1 where id>0;

    11 rows selected.

    ---------------------------------------------------------------------------
    | Id | Operation        | Name   | Rows | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    | 0  | SELECT STATEMENT |        | 11   | 143   | 1 (0)      | 00:00:01 |
    |* 1 | INDEX RANGE SCAN | IDX_ID | 11   | 143   | 1 (0)      | 00:00:01 |
    ---------------------------------------------------------------------------

     

    Using the index IDX_VAL1:

    SQL> select val1 from dgomez.table1 where val1 !='a';

    10 rows selected.

    -----------------------------------------------------------------------------
    | Id | Operation        | Name     | Rows | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------
    | 0  | SELECT STATEMENT |          | 10   | 120   | 1 (0)      | 00:00:01 |
    |* 1 | INDEX FULL SCAN  | IDX_VAL1 | 10   | 120   | 1 (0)      | 00:00:01 |
    -----------------------------------------------------------------------------

    SQL> select val1 from dgomez.table1 where val1 !='z';

    11 rows selected.

    -----------------------------------------------------------------------------
    | Id | Operation        | Name     | Rows | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------
    | 0  | SELECT STATEMENT |          | 11   | 132   | 1 (0)      | 00:00:01 |
    |* 1 | INDEX FULL SCAN  | IDX_VAL1 | 11   | 132   | 1 (0)      | 00:00:01 |
    -----------------------------------------------------------------------------

     

    Using the index IDX_VAL2:

    SQL> select val2 from dgomez.table1 where val2 !='b';

    10 rows selected.

    -----------------------------------------------------------------------------
    | Id | Operation        | Name     | Rows | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------
    | 0  | SELECT STATEMENT |          | 10   | 120   | 1 (0)      | 00:00:01 |
    |* 1 | INDEX FULL SCAN  | IDX_VAL2 | 10   | 120   | 1 (0)      | 00:00:01 |
    -----------------------------------------------------------------------------

    SQL> select val2 from dgomez.table1 where val2 !='z';

    11 rows selected.

    -----------------------------------------------------------------------------
    | Id | Operation        | Name     | Rows | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------
    | 0  | SELECT STATEMENT |          | 11   | 132   | 1 (0)      | 00:00:01 |
    |* 1 | INDEX FULL SCAN  | IDX_VAL2 | 11   | 132   | 1 (0)      | 00:00:01 |
    -----------------------------------------------------------------------------

     

    Confirming the information captured

    Now let’s take a look into the information captured by Oracle. In the previous part of this demo I executed each query two times in order to use every index twice. The first query always returned 10 rows for every index, and the second query returned 11 rows for every index; this means in total the index has returned 21 rows. Now let’s confirm these values:

    SQL>select name, total_access_count, total_exec_count, total_rows_returned, last_used from DBA_INDEX_USAGE where owner='DGOMEZ';

    NAME      TOTAL_ACCESS_COUNT TOTAL_EXEC_COUNT TOTAL_ROWS_RETURNED LAST_USED
    --------- ------------------ ---------------- ------------------- ---------------------
    IDX_ID                     2                2                  21   07-16-2017 18:58:43
    IDX_VAL1                   2                2                  21   07-16-2017 18:58:43
    IDX_VAL2                   2                2                  21   07-16-2017 18:58:43

    Fortunately the information about every query I executed was captured, but it seems not all the SELECTs are captured, as Frank Pachot explains in this article. I also saw that if the Quries are executed by SYS the index usage is not captured. 

    The following output shows how many accesses the index has received:

    SQL> select name, bucket_1_access_count, bucket_2_10_access_count, bucket_11_100_access_count,
    bucket_101_1000_access_count from DBA_INDEX_USAGE where owner='DGOMEZ';


    NAME      BUC_1_ACC_CT BUC_2_10_ACC_CT BUC_11_100_ACC_CT BUC_101_1000_ACC_CT
    --------- ------------ --------------- ----------------- -------------------
    IDX_ID               0               1                 1                   0
    IDX_VAL1             0               1                 1                   0
    IDX_VAL2             0               1                 1                   0

     

    The definition of the column “BUCKET_11_100_ACCESS_COUNT” is “The index has been accessed between 11 and 100 times. At first look it seems that this definition is not correct, because I just executed the same query two times for each index. I didn’t execute a query that accessed the index between 11 and 100 times.

    So apparently this column actually captures its accesses, not operations. Since the first SELECT operations accessed the index 10 times because it returned 10 rows, the bucket_2_10_access_count was increased by one. It is the same for the second query, which accessed the index 11 times because it returned 11 rows; the bucket_11_100_access_count was increased by one.

    But… Wait! TOTAL_ACCESS_COUNT says every index was accessed only two times in total. So, there are some inconsistent definitions here:

    • Either there were two accesses of every index because I executed two SELECT operations that touched the index, in which case TOTAL_ACCESS_COUNT is correct but BUCKET_11_100_ACCESS_COUNT is not correct, because I didn’t execute any query more than 10 times and fewer than 101 times. 
    • Or, the BUCKET_11_100_ACCESS_COUNT is correct and it doesn’t count the operations (SELECTs in this case) and instead counts every access to the b-tree nodes into the index; in which case the definition of TOTAL_ACCESS_COUNT is wrong.

    In the following output we can confirm that every bucket received the correct information. For example, for the bucket bucket_2_10_rows_returned there is 1 execution; this is because the first query always returned 10 rows in every index. The bucket bucket_11_100_rows_returned always has the right value (1 execution) since the second query we executed against every index always returned 11 rows.

    SQL> select name, bucket_2_10_rows_returned, bucket_11_100_rows_returned,
    bucket_101_1000_rows_returned from DBA_INDEX_USAGE where owner='DGOMEZ';


    NAME      BUC_2_10_RW_RETD BUC_11_100_RW_RETD BUC_101_1000_RW_RETD
    --------- ---------------- ------------------ ---------------------
    IDX_ID                  10                 11                     0
    IDX_VAL1                10                 11                     0
    IDX_VAL2                10                 11                     0

     

    Conclusion

    Oracle has been introducing new views that provides very useful information to DBAs so that the DBAs can administrate properly the databases and diagnose problems in order to avoid any reactive problems. For several years scripts, third-parties tools, ALTER INDEX clauses, etc., were used to track the index usage, but this changed now Oracle perform this automatically without overheads in the performance.

    Tags: Oracle

    Deiby Gomez

    Written by Deiby Gomez

    Deiby Gómez is the youngest Oracle ACE and Oracle ACE Director in the world and the first Guatemalan with these awards. Deiby is the youngest Latin American with the highest certifications “Oracle Certified Master 11g” and “Oracle Certified Master 12c”. He received In Vegas, United States the "SELECT Journal Editor’s Choice Award 2016", he became the first Guatemalan with that award. He is a frequent speaker in several Oracle Events around the world like “Technology Network Latin American Tour 2013, 2014, 2015 and 2016 in several countries like Guatemala, Costa Rica, Nicaragua, El Salvador, Uruguay, Argentina, Mexico, Brazil, Ecuador, Colombia, Peru; Collaborate in Vegas, USA; Latin American Oracle Open World in Brazil (2015 and 2016) and Oracle Open World in San Francisco, USA (2015 and 2016). He is the first Guatemalan who was accepted by Oracle Corporation as “Beta Tester” for the version “12cR2” in 2015. He is the official Technical Reviewer of the Book “Oracle Database 12c Release 2 Multitenant (1st Edition, McGraw-Hill)" and Co-Author of the book "Oracle Database 12c Release 2 Testing Tools and Techniques for Performance and Scalability", both can be found in Amazon. He is the first Guatemala who appeared as an outstanding expert in the official magazine of Oracle Corporation called “Oracle Magazine” in the Edition November/December in 2014, this magazine is delivered around the world in several countries. He has published several articles in the Official Website of Oracle (www.oracle.com) in Portuguese, Spanish and English. Currently he is President of Guatemalan Oracle Users Group (GOUG). Director of Support Quality en Latin American Oracle Users Group Community (LAOUC) for 2016-2017, founder of a very well known group in Oracle Community called “Oraworld Team” that has members from India, Guatemala, Brazil, France and Switzerland having in total 4 Oracle ACE Directors, 3 Oracle ACE, 5 Oracle Certified Masters (OCM) and 1 PhD. Deiby was part of Oracle ACE Hackaton in Amsterdam, Netherlands in April 2016 where he was building several solutions using Oracle Cloud Products. Currently Deiby Gómez is CEO in Nuvola Consulting Group, a company that provides excellence on Support and Consulting services with Oracle Technology like Databases, Middleware, Cloud and Engineered systems. Deiby is well known in the community because of his resilience, entrepreneurship and his availability to help and share his knowledge. Deiby loves to travel, to play chess and to enjoy a good cup of coffee with friends. Oracle ACE (at the age of 23) Oracle Certified Master 11g (at the age of 24) Oracle ACE Director (at the age of 25) Oracle Certified Master 12c (at the age 26) SELECT Journal Editor’s Choice Award 2016 Speaker in several Universities, OTN Tour 2013,2014,2015. Collaborate15, LA Oracle Open World. Technical Reviewer of the Book "Oracle Database 12c Release 2 Multitenant (Oracle Press) 1st Edition" Blogger. Oracle Certifications: Oracle Linux Certified Implementation Specialist. Oracle Database 11g Administrator Certified Professional. Oracle Database 11g Administrator Certified Master (OCM 11g) Oracle Database 12c Administrator Certified Master (OCM 12c) Oracle Database 12c Maximum Availability Architecture Certified Master (MAA OCM 12c) Oracle Database 12c Maximum Availability Architecture Certified Expert (MAA OCE 12c) Oracle Database 12c Administrator Certified Professional. Oracle Service Oriented Architecture Infrastructure Implementation Certified Expert. Oracle Exadata Database Machine Administrator. Oracle RAC 11g and Grid Infraestructure Administrator Oracle RAC 12c and Grid Infraestructure Administrator Oracle Real Application Clusters 12c Certified Implementation Specialist Oracle Database 12c: Data Guard Administrator