Automatic Indexing in Oracle Database 19c | DBMS_AUTO_INDEX

Introduction to Automatic Indexing

Oracle Database 19c introduced a new automatic indexing feature through DBMS_AUTO_INDEX, automating index management tasks to maintain database performance without extensive manual tuning. This feature allows the database to assess indexing needs dynamically, creating, testing, and implementing indexes based on actual usage patterns, saving valuable time for DBAs.

How DBMS_AUTO_INDEX Works

DBMS_AUTO_INDEX monitors query patterns and creates candidate indexes as invisible indexes to test their effectiveness before activating them in production. If an index proves beneficial for query performance, it is made visible and integrated into the execution plans. Unused or redundant indexes are periodically cleaned up to optimize storage and performance further.

Automatic Indexing Features

• Candidate Identification: The database identifies potential indexing candidates based on column usage in SQL queries.
• Invisible Index Testing: Initial indexes are created invisibly, allowing DBMS_AUTO_INDEX to verify their impact on query performance before they are made fully operational.
• Unused Index Cleanup: Automatic indexing also involves deleting indexes that are no longer needed, thus freeing up space and improving efficiency.

Prerequisites for Automatic Indexing

Automatic indexing is designed for Oracle Enterprise Edition on Engineered Systems (e.g., Exadata). For testing purposes, you may use the "_exadata_feature_on" initialization parameter, though this approach is not officially supported for production environments. A typical setup involves setting the Oracle SID, enabling the parameter, and restarting the database instance.

Configuration Options

The DBMS_AUTO_INDEX package provides various configuration options, allowing DBAs to control how and when automatic indexing operates:

Display Current Configuration

The CDB_AUTO_INDEX_CONFIG view provides an overview of current automatic indexing settings.

SELECT con_id, parameter_name, parameter_value 
FROM cdb_auto_index_config 
ORDER BY con_id, parameter_name;

    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

Enable/Disable Automatic Indexing

The AUTO_INDEX_MODE parameter can be set to:

• IMPLEMENT: Enables automatic indexing, allowing indexes that improve performance to be used by the optimizer.
• REPORT ONLY: Runs indexing as a test, keeping new indexes invisible.
• OFF: Disables automatic indexing entirely.

-- Enable automatic indexing
BEGIN DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE', 'IMPLEMENT'); END;

Specify Tablespace for Automatic Indexes

By default, automatic indexes are created in the default tablespace. You can specify a different tablespace:

ALTER SESSION SET CONTAINER = pdb1;
CREATE TABLESPACE auto_indexes_ts datafile size 100m autoextend on next 50m;;
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE', 'AUTO_INDEXES_TS');

Schema-Level Control

Automatic indexing can target specific schemas, using AUTO_INDEX_SCHEMA with allow => TRUE for inclusion or allow => FALSE for exclusion.

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

SELECT con_id, parameter_name, parameter_value 
FROM cdb_auto_index_config 
ORDER BY con_id, parameter_name;

    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 (DBDOCS)
         3 AUTO_INDEX_SPACE_BUDGET                  50

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', 'DBDOCS', allow => FALSE);

SELECT con_id, parameter_name, parameter_value 
FROM cdb_auto_index_config 
ORDER BY con_id, parameter_name;

    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

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

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

SELECT con_id, parameter_name, parameter_value 
FROM cdb_auto_index_config 
ORDER BY con_id, parameter_name;

    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 (DBDOCS)
         3 AUTO_INDEX_SPACE_BUDGET                  50

Practical Examples and Use Cases

DBMS_AUTO_INDEX analyzes workloads and adds indexes for tables that are queried heavily, boosting query performance. To check the list of created indexes:

SELECT INDEX_NAME, TABLE_NAME FROM DBA_AUTO_INDEXES;

Monitoring and Activity Reports

Oracle provides monitoring functions in DBMS_AUTO_INDEX to track automatic indexing activity:

-- Generate a summary report for the last 24 hours
SELECT DBMS_AUTO_INDEX.REPORT_ACTIVITY() FROM DUAL;

The REPORT_ACTIVITY and REPORT_LAST_ACTIVITY functions offer detailed insights and can be customized for output format and detail level.

Conclusion

Automatic indexing in Oracle 19c is a powerful tool that greatly reduces the time and effort needed to maintain indexing, enabling the database to dynamically adjust to workload changes. It represents a significant advancement in database automation, especially for complex environments.

Rate Your Experience

: 0 : 0