Top 60 Oracle Blogs

Recent comments

19c Auto Index: the dictionary views

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:

exec dbms_auto_index.configure('auto_index_mode','implement');

The other parameters are the default:

select * from dba_auto_index_config order by 1;

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.

select * from sys.smb$config where parameter_name like '%AUTO_INDEX%' order by 1;

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

exec sys.dbms_auto_index_internal.configure( '_AUTO_INDEX_TRACE', 2, allow_internal=>true);


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

SQL> select * from dba_advisor_tasks where owner='SYS' order by task_id;
TASK_ID TASK_NAME                          ADVISOR_NAME
4 SYS_AI_VERIFY_TASK SQL Performance Analyzer
6 AUTO_STATS_ADVISOR_TASK Statistics Advisor

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

select * from dba_advisor_executions where task_name='SYS_AUTO_INDEX_TASK' order by execution_id;


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.

select * from dba_auto_index_executions order by execution_start;


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)

select * from dba_advisor_objects where task_name='SYS_AUTO_INDEX_TASK' and type='AUTO INDEX INFORMATION'order by object_id;


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:

SQL> select attr7,sys.dbms_auto_index_internal.finding_name(attr7) from (select rownum attr7 from xmltable('1 to 51')) order by 1;
1 Compiled statements
2 Statements using auto index in compilation verification(final)
3 Statements discarded (misestimate or high selectivity of indexes)
4 New index candidate
5 Candidate indexes
6 Index for rebuild
7 Rebuilt indexes
8 Redundant index
9 Redundant indexes
10 Misestimate in sql_id
11 Pruned indexes
12 SPM begin
13 Statements considered by SPM
14 Indexes in first verification
15 Indexes in second verification
16 No significant improvement with index
17 Ineffective indexes
18 Significant improvement with index
19 Effective indexes
20 Error for statement
21 Timeout for statement
22 No buffer gets for statement
23 Statement regressed or no significant improvement
24 Regressed statements
25 Statement produced same plan
26 Statement has same performance
27 Unchanged statements
28 Statement improved
29 Improved statements
30 Index created
31 Index dropped
32 Index rebuilt
33 Index marked unusable
34 Index marked visible
35 Index marked invisible
37 Auto index clean up work done
38 Execution validation for mis-estimated statements done
39 Auto index action based on performance validation done
40 Auto index compilation verification done
41 Statements using auto index in compilation verification
42 SPM end
43 Max space budget reached
44 Report mode, performance validated but index stays invisible
45 Out-of-space during rebuild
46 Statements in STS
47 Auto index execution start
48 Resuming auto index execution
49 Skipping table from auto index creation
50 Auto index execution end
51 Implement validated auto index


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

select * from sys."_auto_index_log" order by log_id;

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

select * from dba_auto_index_statistics where value>0 order by 1;

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

select * from dba_advisor_objects where task_name='SYS_AUTO_INDEX_TASK' and type='INDEX' order by object_id;


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

select * from sys."_auto_index_ind_objects" order by object_id;


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
select * from dba_auto_index_ind_actions order by action_id;

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’

select * from dba_advisor_objects where task_name='SYS_AUTO_INDEX_TASK' and type='SQL' order by object_id;


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)

select * from sys."_auto_index_sql_objects" order by object_id;


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:

select * from dba_auto_index_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.