8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Automatic Indexing (DBMS_AUTO_INDEX) in Oracle Database 19c

Oracle database 19c introduced the automatic indexing feature, which lets you hand over some of the decisions about index management to the database.

What It Does
Prerequisites
Configuration
Drop Secondary Indexes
Drop an Automatic Index
Views
Activity Reports

What It Does

The automatic indexing feature does the following.

Identify potential automatic indexes based on the table column usage. The documentation calls these "candidate indexes".
Create automatic indexes as invisible indexes, so they are not used in execution plans. Index names include the "SYS_AI" prefix.
Test the invisible automatic indexes against SQL statements to make sure they give improved performance. If they result in improved performance they are made visible. If performance is not improved, the relevant automatic index is marked as unusuable and later removed. The SQL statements tested against failed automatic indexes are blocklisted, so they will not be considered for automatic indexing in future. Automatic indexes are not considered by the optimizer for first time SQL run against the database.
Delete unused indexes.

I've never used this feature on an Exadata, so I'm not in a position to comment on its effectiveness.

Prerequisites

This feature is currently restricted to Enterprise Edition on Engineered Systems, as described here. There is a workaround for testing by enabling the "_exadata_feature_on" initialisation parameter.

export ORACLE_SID=cdb1
export ORAENV_ASK=NO
. oraenv
export ORAENV_ASK=YES

sqlplus / as sysdba <<EOF

alter system set "_exadata_feature_on"=true scope=spfile;
shutdown immediate;
startup;

exit;
EOF

This is not supported and shouldn't be used on a real system.

Configuration

The DBMS_AUTO_INDEX package is used to manage the automatic indexing feature. The basic management is described below.

Display Configuration

The CDB_AUTO_INDEX_CONFIG view displays the current automatic indexing configuration. The following query is available as the auto_index_config.sql script.

column parameter_name format a40
column parameter_value format a15

select con_id, parameter_name, parameter_value 
from   cdb_auto_index_config
order by 1, 2;

    CON_ID PARAMETER_NAME                           PARAMETER_VALUE
---------- ---------------------------------------- ---------------
         1 AUTO_INDEX_COMPRESSION                   OFF
         1 AUTO_INDEX_DEFAULT_TABLESPACE
         1 AUTO_INDEX_MODE                          OFF
         1 AUTO_INDEX_REPORT_RETENTION              31
         1 AUTO_INDEX_RETENTION_FOR_AUTO            373
         1 AUTO_INDEX_RETENTION_FOR_MANUAL
         1 AUTO_INDEX_SCHEMA
         1 AUTO_INDEX_SPACE_BUDGET                  50
         3 AUTO_INDEX_COMPRESSION                   OFF
         3 AUTO_INDEX_DEFAULT_TABLESPACE
         3 AUTO_INDEX_MODE                          OFF
         3 AUTO_INDEX_REPORT_RETENTION              31
         3 AUTO_INDEX_RETENTION_FOR_AUTO            373
         3 AUTO_INDEX_RETENTION_FOR_MANUAL
         3 AUTO_INDEX_SCHEMA
         3 AUTO_INDEX_SPACE_BUDGET                  50

SQL>

If we switch to a user-defined pluggable database, we only get the values for that container.

alter session set container = pdb1;

SQL> @auto_index_config.sql

    CON_ID PARAMETER_NAME                           PARAMETER_VALUE
---------- ---------------------------------------- ---------------
         3 AUTO_INDEX_COMPRESSION                   OFF
         3 AUTO_INDEX_DEFAULT_TABLESPACE
         3 AUTO_INDEX_MODE                          OFF
         3 AUTO_INDEX_REPORT_RETENTION              31
         3 AUTO_INDEX_RETENTION_FOR_AUTO            373
         3 AUTO_INDEX_RETENTION_FOR_MANUAL
         3 AUTO_INDEX_SCHEMA
         3 AUTO_INDEX_SPACE_BUDGET                  50

SQL>

The parameters are explained in detail here.

Enable/Disable Automatic Indexing

Automatic indexing is configured using the CONFIGURE procedure of the DBMS_AUTO_INDEX package.

The on-off switch for automatic indexing is controlled using the AUTO_INDEX_MODE property, which has the following allowed values.

IMPLEMENT : Turns on automatic indexing. New indexes that improve performance are made visible and available for use by the optimizer.
REPORT ONLY : Turns on automatic indexing, but new indexes remain invisible.
OFF : Turns off automatic indexing.

Examples of switching between modes are shown below.

exec dbms_auto_index.configure('AUTO_INDEX_MODE','IMPLEMENT');
exec dbms_auto_index.configure('AUTO_INDEX_MODE','REPORT ONLY');
exec dbms_auto_index.configure('AUTO_INDEX_MODE','OFF');

Tablespace for Automatic Indexes

By default automatic indexes are created in the default permanent tablespace. If this is not acceptable, you can specify a tablespace to hold them using the AUTO_INDEX_DEFAULT_TABLESPACE property. Below we create a tablespace to hold the automatic indexes, and set the property accordingly.

alter session set container = pdb1;

create tablespace auto_indexes_ts datafile size 100m autoextend on next 100m;

exec dbms_auto_index.configure('AUTO_INDEX_DEFAULT_TABLESPACE','AUTO_INDEXES_TS');

Set to NULL to return to using the default permanent tablespace.

Exec dbms_auto_index.configure('AUTO_INDEX_DEFAULT_TABLESPACE',NULL);

Schema-Level Control

Once automatic indexing is enabled, all schemas are considered when trying to identify candidate indexes. You can alter the default behaviour using the AUTO_INDEX_SCHEMA property, which allows you to maintain an inclusion/exclusion list.

If the ALLOW parameter is set to TRUE, the specified schema is added to the inclusion list. Notice it builds a predicate containing the schemas.

exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', 'TEST', allow => TRUE);
exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', 'TEST2', allow => TRUE);

SQL> @auto_index_config.sql

    CON_ID PARAMETER_NAME                           PARAMETER_VALUE
---------- ---------------------------------------- ----------------------------------------
         3 AUTO_INDEX_COMPRESSION                   OFF
         3 AUTO_INDEX_DEFAULT_TABLESPACE            AUTO_INDEXES_TS
         3 AUTO_INDEX_MODE                          IMPLEMENT
         3 AUTO_INDEX_REPORT_RETENTION              31
         3 AUTO_INDEX_RETENTION_FOR_AUTO            373
         3 AUTO_INDEX_RETENTION_FOR_MANUAL
         3 AUTO_INDEX_SCHEMA                        schema IN (TEST, TEST2)
         3 AUTO_INDEX_SPACE_BUDGET                  50

SQL>

The inclusion list can be blanked using the NULL parameter value.

exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', NULL, allow => TRUE);

SQL> @auto_index_config.sql

    CON_ID PARAMETER_NAME                           PARAMETER_VALUE
---------- ---------------------------------------- ----------------------------------------
         3 AUTO_INDEX_COMPRESSION                   OFF
         3 AUTO_INDEX_DEFAULT_TABLESPACE            AUTO_INDEXES_TS
         3 AUTO_INDEX_MODE                          IMPLEMENT
         3 AUTO_INDEX_REPORT_RETENTION              31
         3 AUTO_INDEX_RETENTION_FOR_AUTO            373
         3 AUTO_INDEX_RETENTION_FOR_MANUAL
         3 AUTO_INDEX_SCHEMA
         3 AUTO_INDEX_SPACE_BUDGET                  50

SQL>

If the ALLOW parameter is set to FALSE, the specified schemas are added to the exclusion list.

exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', 'TEST', allow => FALSE);
exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', 'TEST2', allow => FALSE);

SQL> @auto_index_config.sql

    CON_ID PARAMETER_NAME                           PARAMETER_VALUE
---------- ---------------------------------------- ----------------------------------------
         3 AUTO_INDEX_COMPRESSION                   OFF
         3 AUTO_INDEX_DEFAULT_TABLESPACE            AUTO_INDEXES_TS
         3 AUTO_INDEX_MODE                          IMPLEMENT
         3 AUTO_INDEX_REPORT_RETENTION              31
         3 AUTO_INDEX_RETENTION_FOR_AUTO            373
         3 AUTO_INDEX_RETENTION_FOR_MANUAL
         3 AUTO_INDEX_SCHEMA                        schema NOT IN (TEST, TEST2)
         3 AUTO_INDEX_SPACE_BUDGET                  50

SQL>

The exclusion list can be blanked using the NULL parameter value.

exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', NULL, allow => FALSE);

SQL> @auto_index_config.sql

    CON_ID PARAMETER_NAME                           PARAMETER_VALUE
---------- ---------------------------------------- ----------------------------------------
         3 AUTO_INDEX_COMPRESSION                   OFF
         3 AUTO_INDEX_DEFAULT_TABLESPACE            AUTO_INDEXES_TS
         3 AUTO_INDEX_MODE                          IMPLEMENT
         3 AUTO_INDEX_REPORT_RETENTION              31
         3 AUTO_INDEX_RETENTION_FOR_AUTO            373
         3 AUTO_INDEX_RETENTION_FOR_MANUAL
         3 AUTO_INDEX_SCHEMA
         3 AUTO_INDEX_SPACE_BUDGET                  50

SQL>

Other Configuration

There are other parameters you may wish to consider, which are all explained in detail here.

AUTO_INDEX_COMPRESSION : Undocumented. Presumably used to control the level of compression. Default "OFF".
AUTO_INDEX_REPORT_RETENTION : Retention period for automatic indexing logs. Reporting is based on these logs. Default 31 days.
AUTO_INDEX_RETENTION_FOR_AUTO : Retention period for unused automatic indexes. Default 373 days.
AUTO_INDEX_RETENTION_FOR_MANUAL : Retention period for unused manually created indexes. When set to NULL, manually created indexes are not considered for removal. Default NULL.
AUTO_INDEX_SPACE_BUDGET : Percentage of the default permanent tablespace used to automatic index storage. This parameter is ignored when using the AUTO_INDEX_DEFAULT_TABLESPACE parameter to specify a custom tablespace.

Drop Secondary Indexes

Think very carefully before doing this, and test, test, test!

If you are feeling particularly brave, the DROP_SECONDARY_INDEXES procedure will drop all indexes except those used for constraints. This can be done at table, schema or database level.

-- Table-level
exec dbms_auto_index.drop_secondary_indexes('MY_SCHEMA', 'MY_TABLE');

-- Schema-level
exec dbms_auto_index.drop_secondary_indexes('MY_SCHEMA');

-- Database-level
exec dbms_auto_index.drop_secondary_indexes;

This leaves you with a clean slate, so automatic indexing can make all your indexing decisions for you.

Drop an Automatic Index

The DROP_AUTO_INDEXES procedure allows us to drop automatically created indexes. Depending on the parameters used, that could be a named index, or all auto-indexes for a schema.

Drop a named index, and make sure it is not recreated. Notice the name is double-quoted.

begin
  dbms_auto_index.drop_auto_indexes(
    owner          => 'MY_SCHEMA',
    index_name     => '"SYS_AI_512bd3h5nif1a"',
    allow_recreate => false);
end;
/

Drop all auto-indexes for a named schema, but allow them to be recreated.

begin
  dbms_auto_index.drop_auto_indexes(
    owner          => 'MY_SCHEMA',
    index_name     => null,
    allow_recreate => true);
end;
/

Drop all auto-indexes for the current schema, but allow them to be recreated.

begin
  dbms_auto_index.drop_auto_indexes(
    owner          => null,
    index_name     => null,
    allow_recreate => true);
end;
/

In the initial release of this feature there wasn't a mechanism to drop a specific index created by the automatic indexing functionality, or to prevent specific indexes from being created in the first place. Franck Pachot wrote about some hacks that will allow you to do it.

How to drop an index created by Oracle 19c Auto Indexing?

Views

There are several views associated with the automatic indexing feature, as shown below. Only the first is documented in the reference manual at the time of writing.

select view_name
from   dba_views
where  view_name like 'DBA_AUTO_INDEX%'
order by 1;

VIEW_NAME
--------------------------------------------------------------------------------
DBA_AUTO_INDEX_CONFIG
DBA_AUTO_INDEX_EXECUTIONS
DBA_AUTO_INDEX_IND_ACTIONS
DBA_AUTO_INDEX_SQL_ACTIONS
DBA_AUTO_INDEX_STATISTICS
DBA_AUTO_INDEX_VERIFICATIONS

SQL>

In addition, the {CDB|DBA|ALL|USER}_INDEXES views include the AUTO column, which indicates if an index was created by the automatic indexing feature. The following query is available as the auto_indexes.sql script.

column owner format a30
column index_name format a30
column table_owner format a30
column table_name format a30

select owner,
       index_name,
       index_type,
       table_owner,
       table_name
       table_type
from   dba_indexes
where  auto = 'YES'
order by owner, index_name;

Activity Reports

The DBMS_AUTO_INDEX package contains two reporting functions.

DBMS_AUTO_INDEX.REPORT_ACTIVITY (
   activity_start  IN  TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP - 1,
   activity_end    IN  TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP,
   type            IN  VARCHAR2 DEFAULT 'TEXT',
   section         IN  VARCHAR2 DEFAULT 'ALL',
   level           IN  VARCHAR2 DEFAULT 'TYPICAL')
RETURN CLOB;

DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY (
   type            IN  VARCHAR2 DEFAULT 'TEXT',
   section         IN  VARCHAR2 DEFAULT 'ALL',
   level           IN  VARCHAR2 DEFAULT 'TYPICAL')
RETURN CLOB;

The REPORT_ACTIVITY function allows you to display activity over a specified period of time, which defaults to the last day. The REPORT_LAST_ACTIVITY function reports the last automatic indexing operation. Both allow you to tailor the output using the following parameters.

TYPE : Allowable values (TEXT, HTML, XML).
SECTION : Allowable values (SUMMARY, INDEX_DETAILS, VERIFICATION_DETAILS, ERRORS, ALL). You can also use combinations with the "+" and "-" characters to indicate if something should be included or excluded. For example 'SUMMARY +ERRORS' or 'ALL -ERRORS'.
LEVEL : Allowable values (BASIC, TYPICAL, ALL).

Some examples of using these function from SQL are shown below. Notice the quoting of the LEVEL parameter. This is necessary when using this in a SQL call, so it understands this isn't a reference to the LEVEL pseudo-column.

set long 1000000 pagesize 0

-- Default TEXT report for the last 24 hours.
select dbms_auto_index.report_activity() from dual;

-- Default TEXT report for the latest activity.
select dbms_auto_index.report_last_activity() from dual;

-- HTML Report for the day before yesterday.
select dbms_auto_index.report_activity(
         activity_start => systimestamp-2,
         activity_end   => systimestamp-1,
         type           => 'HTML')
from   dual;

-- HTML report for the latest activity.
select dbms_auto_index.report_last_activity(
         type => 'HTML')
from   dual;

-- XML Report for the day before yesterday with all information.
select dbms_auto_index.report_activity(
         activity_start => systimestamp-2,
         activity_end   => systimestamp-1,
         type           => 'XML',
         section        => 'ALL',
         "LEVEL"        => 'ALL')
from   dual;

-- XML report for the latest activity with all information.
select dbms_auto_index.report_last_activity(
         type     => 'HTML',
         section  => 'ALL',
         "LEVEL"  => 'ALL')
from   dual;

set pagesize 14

Here is an example of the output from the default activity report before any indexes have been created.

select dbms_auto_index.report_activity() from dual;

GENERAL INFORMATION
-------------------------------------------------------------------------------
 Activity start               : 03-JUN-2019 21:59:21
 Activity end                 : 04-JUN-2019 21:59:21
 Executions completed         : 2
 Executions interrupted       : 0
 Executions with fatal error  : 0
-------------------------------------------------------------------------------

SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
 Index candidates            : 0
 Indexes created             : 0
 Space used                  : 0 B
 Indexes dropped             : 0
 SQL statements verified     : 0
 SQL statements improved     : 0
 SQL plan baselines created  : 0
 Overall improvement factor  : 0x
-------------------------------------------------------------------------------

SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------
 Unused indexes    : 0
 Space used        : 0 B
 Unusable indexes  : 0
-------------------------------------------------------------------------------

ERRORS
---------------------------------------------------------------------------------------------
No errors found.
---------------------------------------------------------------------------------------------

SQL>

For more information see:

Hope this helps. Regards Tim...

Back to the Top.