19c Auto Index: the dictionary views

Franck Pachot
Feb 25, 2019 · 7 min read

The abbreviation AI may be misleading but it has nothing to do with Artificial Intelligence. And you may have been surprised that the ‘A’ means ‘Automatic’ rather than ‘Autonomous’ as the latter is constantly used to tag any new feature in the database since 18c. But this difference is really important: ‘Autonomous’ supposes that you don’t have anything to do and don’t even need to be notified about what happened. On the opposite, ‘Automatic’ means that some things are done without your intervention, in order to help you, but you are still in charge of managing them. And you need to look at the dictionary views, to be aware of the findings, recommendations, and implementations. 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.

This posts present those views, mainly defined in the catproc script $ORACLE_HOME/rdbms/admin/cataivw.sql, grouped in the following areas:

  • Configuration
  • Activity log
  • Indexes created
  • SQL statements

Automatic Indexing — Configuration Parameters


The configuration parameters are displayed with this view.

Here I’ve set AUTO_INDEX_MODE to run and automatically implement its findings:

The other parameters are the default:

Actually, the table behind this view is SMB$CONFIG which is from the SQL Management Base. SMB stores what the optimizer needs to persist about SQL statements: SQL Profiles, SQL Plan Baselines, SQL Patches and in 19c SQL Quarantine. And Automatic Indexing is heavily linked with them to control the scope of its implementations.


SMB$CONFIG shows additional underscore parameters.

Here I’ve set _AUTO_INDEX_TRACE to the value 2 to get more tracing in the job trace. I’ve set it with:


Automatic Indexing is based on the Optimizer Advisor Framework. Here are the new Advisor tasks:

Automatic Indexing — Activity Log


The standard Advisor views can give information about the Auto Indexing activity. The TASK_ID=5 and TASK_NAME=SYS_AUTO_INDEX_TASK


You don’t need to go to those views because Auto Indexing provides specific ones, based on the same WRI$_ADV_TASKS and WRI$_ADV_EXECUTIONS tables.


The Advisor Framework stores additional information as objects in WRI$_ADV_OBJECTS. The Automatic Indexing ones are the log information from its activity, with TYPE_ID=31 and TYPE=’AUTO INDEX INFORMATION’ (these types are defined in X$KEAOBJT)


Those Advisor views show general attributes that have a different signification for each object type, and for AUTO INDEX INFORMATION the ATTR7 identifies the finding. Those, as far as I know, are not visible from a table but hardcoded in the DBMS_AUTO_INDEX_INTERNAL function. Here are all possible values:


Oracle has an internal view to display the advisor objects as a log of its activity:

Unfortunately, there’s no public dictionary view on it except an aggregated one to sum the statistics.


Based on “_auto_index_log” we have some summary counters

Automatic Indexing — New Indexes


The goal of Auto Indexing activity is to create (and drop) indexes and this index information is stored as objects with TYPE_ID=2 and TYPE=’INDEX’ in the Advisor Framework objects


This internal view decodes the attributes in the context of Automatic Indexes to display more information about the created indexes


The full metadata is available from the dictionary views about indexes. The name of those indexes start with ‘SYS_AI_’ and are flagged with this new AUTO column.

The DDL as generated by DBMS_METADATA has this AUTO attribute, but we cannot use it ourselves. The tablespace here is SYSTEM because I’ve not set the configuration AUTO_INDEX_DEFAULT_TABLESPACE parameter. It seems that 12cR2 ADVANCED LOW compression is used. I’ll update this post when having more information about the licensing consequences. [Update: I raised the point and a new parameter should come in 19.3]


A public view is available to see the commands that were executed to create this index, which shows the different steps:

  • created as unusable to analyze the execution plans
  • rebuilt (online) but invisible to control which statements will verify it
  • made visible (which does not lock the table since 12c) when accepted

Those actions, with start/end timestamp, give more detail about the operations that we have seen in “_auto_index_log” (New index candidate, Index rebuilt, Index marked visible).

Automatic Indexing — SQL Statements

The Automatic Indexing works basically by capturing a SQL Tuning Set on which it runs the SQL Access Advisor, and the tuning set is visible as SYS_AUTO_STS (here created at the same time as my first ‘Statements in STS’ in “_auto_index_log”):


Automatic Indexing goes further than the SQL Access Advisor. The statements are continuously verified to detect improvement and regressions. They are stored with some flags as TYPE_ID=7 and TYPE=’SQL’


The internal view decodes attr2 as the Plan Hash Value and attr7 as some flags about the verification(my guess on a quick test is that flag is set to 1 when the SQL was improved, 2 when regression has been seen)


The goal of those verifications is to prevent the regressions by blacklisting the new index usage for some queries. We can see that in SQL actions:

This is an example where Automatic Indexing has called loaded the previous plan as accepted in a SQL Plan Baseline by calling DBMS_SPM_INTERNAL.LOAD_PLANS_FROMSQL_SET


This SQL Plan Baseline is identified with the ‘EVOLVE-AUTO-INDEX-LOAD’ origin.

This is quite surprising because fixing the previous plan does not only prevent the usage of the new AUTO index, but also any new one that I can create manually (at least until it automatically evolved).


I expected to see the regressions locked down by SQL Patches rather than SQL Plan Baselines. In this first test, I see no SQL Patch created, but this is another dictionary view to look at when trying to understand Automatic Indexing.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch

Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore

Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store