Oracle 19c: Automatic Indexing. Part 1. Introduction

Added 16.10.2020: See also related presentation: Oracle 19c: Automatic Indexing.

This is the first of a two-part post that looks at the Automatic Indexing feature introduced in Oracle 19c, available on engineered systems only. Initially, I simply wanted to see what it does and to understand how it worked.
Next, I wanted to see how good it is. I created a test based on Dominic Giles' Swingbench Sales Order Entry benchmark. Having dropped the secondary indexes (ones not involved in key constraints), I wanted to see which Automatic Indexing would recreate and whether that would reinstate the original performance.

References and Acknowledgements 

This blog is not intended to provide a comprehensive description of Automatic Indexing.  I explain some things as I go along, but I have referenced the sources that I found helpful.
The Oracle 19c documentation is not particularly verbose. Automatic Indexing is introduced in New Database Features Guide: Big Data & Data Warehousing: Automatic Indexing.

"The automatic indexing feature automates index management tasks, such as creating, rebuilding, and dropping indexes in an Oracle Database based on changes in the application workload. This feature improves database performance by managing indexes automatically in an Oracle Database."

However, there is more information the Database Administrator's Guide at Managing Auto Indexes:

"The automatic indexing feature automates the index management tasks in an Oracle database. Automatic indexing automatically creates, rebuilds, and drops indexes in a database based on the changes in application workload, thus improving database performance. The automatically managed indexes are known as auto indexes.

Index structures are an essential feature to database performance. Indexes are critical for OLTP applications, which use large data sets and run millions of SQL statements a day. Indexes are also critical for data warehousing applications, which typically query a relatively small amount of data from very large tables. If you do not update the indexes whenever there are changes in the application workload, the existing indexes can cause the database performance to deteriorate considerably. 

Automatic indexing improves database performance by managing indexes automatically and dynamically in an Oracle database based on changes in the application workload." 

Maria Colgan (the Master Oracle Database product manager) has blogged and presented on this feature:

• Oracle Database 19c Is Now Available 
• Test Drive Automatic Indexing 

Automatic Indexing is certainly intended for use in the Autonomous Database, but also for other 19c Exadata databases. These presentations also make it clear that Automatic Indexing is intended for OLTP as well as Warehouse and Analytic databases. Some of the examples refer to packaged applications (an unnamed Accounts Receivable system, and a PeopleSoft ERP system).
I found a number of other valuable resources that helped me to get it going, monitor it, and to begin to understand what was going on behind the scenes.

• Richard Foote has blogged extensively about this feature over the last year. 
• Franck Pachot has written several blogs, I will refer to others later. 
• 19c Auto Index: the dictionary views: "Automatic Indexing is an evolution of the Advisors that were introduced since 10g and, in the same way, it provides many dictionary views to understand its activity." 
• Tim Hall: Automatic Indexing (DBMS_AUTO_INDEX) in Oracle Database 19c 
• Julian Dontcheff: Automatic Indexing in 19c 

How does Automatic Indexing Work?

Automatic Indexing is an expert system that runs with two background scheduler automatic tasks. By default, both run every 15 minutes.

• Auto STS Capture Task captures workload into a SQL tuning set SYS_AUTO_STS. This process runs regardless of the Automatic Indexing configuration. It has a maximum runtime of 15 minutes. 
• Auto Index Task runs if AUTO_INDEX_MODE is not OFF. It has a maximum runtime of 1 hour. This process creates automatic indexes. Initially, they are invisible and unusable. It checks whether the optimizer will use them. If so, it rebuilds them as usable invisible indexes and checks for improved performance before making them visible. It may also make them invisible again later.

Indexes created by Automatic Indexing are created with the AUTO option, and are identified in ALL_INDEXES with the AUTO attribute.  Automatic indexes will be dropped if they haven't been used for longer than a specified retention period (default 373 days). Optionally, manually created indexes can be considered by Automatic Indexes, and can also be dropped after a separately specified retention period.
This creates a feedback loop where indexes are created and dropped in response to changing load on the database while assuring that the newly created indexes will be used and will improve performance and that any indexes that are dropped were not being used.
Automatic Indexing is only available on engineered (Exadata) systems (see Database Licensing Information User Manual, 1.3 Permitted Features, Options, and Management Packs by Oracle Database Offering, Performance). This includes Oracle Database Enterprise Edition on Engineered Systems, Oracle Database Exadata Cloud Service, and Oracle's Autonomous databases. Automation of index creation and removal is an important part of the 'self-driving' aspiration for the Autonomous database, where it will do 'database management by using machine learning and automation to eliminate human labor, human error, and manual tuning'.
(In 20c, there are two additional automatic tasks to flush and purge the SQL Tuning Sets).