📋Steps in this guide1/12

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.

oracle 19cconfigurationintermediate

by OracleDba

20 views

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. This is not supported and shouldn't be used on a real system.

Code/Command (click line numbers to comment):

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

Configuration

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

Display Configuration

The view displays the current automatic indexing configuration. The following query is available as the auto_index_config.sql script. If we switch to a user-defined pluggable database, we only get the values for that container. The parameters are explained in detail here .

Code/Command (click line numbers to comment):

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>

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>

Enable/Disable Automatic Indexing

Automatic indexing is configured using the procedure of the package. The on-off switch for automatic indexing is controlled using the property, which has the following allowed values. - : Turns on automatic indexing. New indexes that improve performance are made visible and available for use by the optimizer. - : Turns on automatic indexing, but new indexes remain invisible. - : Turns off automatic indexing. Examples of switching between modes are shown below.

Code/Command (click line numbers to comment):

1
2
3

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 property. Below we create a tablespace to hold the automatic indexes, and set the property accordingly. Set to NULL to return to using the default permanent tablespace.

Code/Command (click line numbers to comment):

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

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 property, which allows you to maintain an inclusion/exclusion list. If the parameter is set to TRUE, the specified schema is added to the inclusion list. Notice it builds a predicate containing the schemas. The inclusion list can be blanked using the NULL parameter value. If the parameter is set to FALSE, the specified schemas are added to the exclusion list. The exclusion list can be blanked using the NULL parameter value.

Code/Command (click line numbers to comment):

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>

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>

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>

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 . - : Undocumented. Presumably used to control the level of compression. Default "OFF". - : Retention period for automatic indexing logs. Reporting is based on these logs. Default 31 days. - : Retention period for unused automatic indexes. Default 373 days. - : Retention period for unused manually created indexes. When set to NULL, manually created indexes are not considered for removal. Default NULL. - : 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 procedure will drop all indexes except those used for constraints. This can be done at table, schema or database level. This leaves you with a clean slate, so automatic indexing can make all your indexing decisions for you.

Code/Command (click line numbers to comment):

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

Drop an Automatic Index

The 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. Drop all auto-indexes for a named schema, but allow them to be recreated. Drop all auto-indexes for the current schema, but allow them to be recreated. 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.

Code/Command (click line numbers to comment):

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

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

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

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. In addition, the views include the column, which indicates if an index was created by the automatic indexing feature. The following query is available as the auto_indexes.sql script.

Code/Command (click line numbers to comment):

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>

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 package contains two reporting functions. The function allows you to display activity over a specified period of time, which defaults to the last day. The function reports the last automatic indexing operation. Both allow you to tailor the output using the following parameters. - : Allowable values (TEXT, HTML, XML). - : 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'. - : Allowable values (BASIC, TYPICAL, ALL). Some examples of using these function from SQL are shown below. Notice the quoting of the parameter. This is necessary when using this in a SQL call, so it understands this isn't a reference to the pseudo-column. Here is an example of the output from the default activity report before any indexes have been created. For more information see: - Managing Auto Indexes - DBMS_AUTO_INDEX Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

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;

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

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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!