Enable and disable Auto indexing feature in Oracle 19

Enable and disable Auto indexing in Oracle 19c

Oracle introduced the auto indexing feature in Oracle 19c enterprise edition.
This feature automatic manages the index based on application workload in database by creating, dropping and rebuilds indexes.

Automatic indexing process runs in the background every 15 minutes.
DBMS_AUTO_INDEX.CONFIGURE procedure is used for enable and disable the Auto-indexing feature.

How it works?
1. identifies the auto indexes candidate by workload.
2. creates the invisible indexes for auto indexes candidate. it is not used in SQL queries.
3. Verify the invisible indexes against SQL queries.
4. If performance is good then it used as visible index in SQL Query otherwise blacklisted.
5. Deletes the unused indexes.

Check the Auto indexing configuration

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;

Note: If AUTO_INDEX_MODE is OFF then its feature is disable.

Enable the Auto indexing in Oracle 19c
When its enabled, all schemas present in DB are used auto indexing by default.

-- Create index in visible state
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');

-- Create index in invisible state
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','REPORT ONLY');

Disable auto indexing

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');

Include and exclude Schema from Auto Indexing
By default, all schema is in when Auto Indexing feature is enabled.
HR and Scott schema add in exclusion list, so that not used Auto Indexing feature.

Exclusion List
-- Auto-indexing feature not applicable on both schema. both in exclusion list.
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'HR', allow => FALSE);
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'SCOTT', allow => FALSE);

--Remove HR schema from exclusion list.
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'HR', NULL);

--Remove all schema from exclusion list.
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', NULL, TRUE);

Tablespace for auto index
create new auto indexes in a separate tablespace.

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE', 'Tablespace_name');

Other parameters used for configuration of AUTO indexing feature

AUTO_INDEX_REPORT_RETENTION : Retention period for automatic indexing logs. Default 31 days. Reporting is depend upon it.
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.

Views for check more information

DBA_AUTO_INDEX_EXECUTIONS: History of execution of automatic indexing tasks.
DBA_AUTO_INDEX_STATISTICS: Statistics related to auto indexes.
DBA_AUTO_INDEX_IND_ACTIONS: Actions performed on auto indexes.
DBA_AUTO_INDEX_SQL_ACTIONS: Actions performed on SQL statements for verifying auto indexes.
DBA_AUTO_INDEX_CONFIG: Configuration settings related to auto indexes.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.