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

DBA_AUTO_INDEX_CONFIG

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;
PARAMETER_NAME                    PARAMETER_VALUE   LAST_MODIFIED
AUTO_INDEX_DEFAULT_TABLESPACE
AUTO_INDEX_MODE                   IMPLEMENT         14:20:12
AUTO_INDEX_REPORT_RETENTION       31
AUTO_INDEX_RETENTION_FOR_AUTO     373
AUTO_INDEX_RETENTION_FOR_MANUAL
AUTO_INDEX_SCHEMA
AUTO_INDEX_SPACE_BUDGET           50

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

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);

DBA_ADVISOR_TASKS

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
      2 SYS_AUTO_SPM_EVOLVE_TASK           SPM Evolve Advisor
      3 SYS_AI_SPM_EVOLVE_TASK             SPM Evolve Advisor
      4 SYS_AI_VERIFY_TASK                 SQL Performance Analyzer
      5 SYS_AUTO_INDEX_TASK                SQL Access Advisor
      6 AUTO_STATS_ADVISOR_TASK            Statistics Advisor
      7 INDIVIDUAL_STATS_ADVISOR_TASK      Statistics Advisor

Automatic Indexing — Activity Log

DBA_ADVISOR_EXECUTIONS

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;

DBA_AUTO_INDEX_EXECUTIONS

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;

DBA_ADVISOR_OBJECTS

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;

dbms_auto_index_internal.finding_name(attr7)

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;
  ATTR7 SYS.DBMS_AUTO_INDEX_INTERNAL.FINDING_NAME(ATTR7)
      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
     36
     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

“_auto_index_log”

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.

DBA_AUTO_INDEX_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

DBA_ADVISOR_OBJECTS

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;

“_auto_index_ind_objects”

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;

DBA_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]

DBA_AUTO_INDEX_IND_ACTIONS

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”):

DBA_ADVISOR_OBJECTS

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;

“_auto_index_sql_objects”

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;

DBA_AUTO_INDEX_SQL_ACTIONS

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

DBA_SQL_PLAN_BASELINES

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).

SQL_PATCHES

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.