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.