Oracle 19c Automatic Indexing — How well it’s understood?

In my perspective, any performance & tuning discussions or debates are just incomplete without Oracle index topic. We all been having enough experiences on Oracle indexes all through our DBA career, where in some situations a significant performance gained and in some situations it simply didn’t go well as planned. There is so much about indexes which needs lot of efforts and due experience as well. Index with right candidates (columns) is highly important for a good performance, otherwise, the impact can be just opposite. Right from the choice to create, maintain and drop, Oracle provides several approaches with regards to indexing. We all knew how to test index (no segment) without actually creating, hide the indexes from Optimizer to pick (invisible) or validate the use of indexes through monitoring. Although Oracle provides many advisors (compression, index, in-memory, SGA, PGA etc), the ultimate onus will be on DBA to ensure the performance and impact.

Oracle 19c brings in yet another powerful new feature & concept for index management, i.e, auto index, where hardly any efforts required by a DBA. Also, the accuracy of the usage is maintained. Automatic Indexing feature automates the entire process of index management in the database, runs in the background at a predefined interval, every 15 minutes.

This article will discuss the theory of Automatic Indexing concepts, deployments and audit/reporting.

How the methodology works?

Upon enablement, it identifies the candidate (index), validate and implement. The entire process is fully automated without any intervention from DBA.

In Capture stage: it captures application SQL history: SQLs, bind values, plans , execution statistics into a SQL repository

In Identify stage: Identify index candidates which can benefit and create invisible index

In Verify stage: The index performance benefits will be validated with an Optimizer

In Decide stage: If the performance is significant, the index will be made visible. In case of no performance gain, it will remain invisible.

And automatic indexes are continuously monitored and will be dropped automatically if they are not used for long time, by default unused indexes are kept for 373 days (though this settings can be changed).

Use the following to define the retention period:

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO','90');

The above is applicable and useful for all workloads (OLTP, DW and Mixed workload).

This feature does comes with the slight expensive CPU, memory and storage resources. However, DBA can control the behavior through resource manager by limiting the task to just 1 CPU, choosing a temporary table space to build the indexes.

The PL/SQL API, DBMS_AUTO_INDEX, controls the behavior of automatic indexing with the following parameters:

auto_index_exclude_schema, auto_index_retention_for_auto, auto_index_retention_for_manual, auto_index_default_tablespace, auto_index_mode

The new DBMS_AUTO_INDEX package is mainly used for configuring, dropping and reporting automatic indexes.

Below dynamic views can be used to have more details about automatic indexing:

Every auto indexing task generates a report which can be viewed as HTML, Text or XML format. Use the below function to view the report:

dbms_auto_index.report_activity

To enable automatic indexing feature, enable it first using the below command:

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

To disable the feature, use the following command:

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

You can also set preferred schemas in the database to avail this feature with the following command:

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA','SCOTT','TRUE');

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA','SCOTT','FALSE');

Refer AUTO column of DBA_INDEXES view to identify which indexes are auto created.

References:

https://static.rainfocus.com/oracle/oow18/sess/1523250557343001OBEw/PF/TRN3980_Test_Drive_Auto_Index_Creation_in_ADB_1541192406753001UYd0.pdf

https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-indexes.html#GUID-CCBBC9DB-29A1-4F46-B3AA-BF4205793403

https://juliandontcheff.wordpress.com/2019/02/18/automatic-indexing-in-19c/