Automatic Indexing (DBMS_AUTO_INDEX) in Oracle Database 19c
The automatic indexing feature automates the index management tasks in an Oracle database. Automatic indexing automatically creates, rebuilds, and drops indexes in a database based on the changes in application workload, thus improving database performance. The automatically managed indexes are known as auto indexes.
The automatic indexing feature does the following.
Runs the automatic indexing process in the background periodically at a predefined time interval.
Analyzes application workload, and accordingly creates new indexes and drops the existing underperforming indexes to improve database performance.
Rebuilds the indexes that are marked unusable due to table partitioning maintenance operations, such as ALTER TABLE MOVE.
Provides PL/SQL APIs for configuring automatic indexing in a database and generating reports related to automatic indexing operations.
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.
Switching between modes is 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’);
Set retention period for unused auto indexes to 90 days:
EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_RETENTION_FOR_AUTO’, ’90’);
Define tablespace of TBSAUTO to store auto indexes:
EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_DEFAULT_TABLESPACE’, ‘TBSAUTO’);
Allocates 5% of the tablespace for auto indexes:
EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_SPACE_BUDGET’, ‘5’);
Ref: Automatic Indexing in 19C (Doc ID 2494118.1)
