How to Leverage the Automatic Indexing Feature in Oracle Databases

As the name suggests and simplifies, Oracle has upgraded itself with its 19c version where the Automatic Indexing feature will sense the need for indexes, will create them and drop them “automatically” without anyone’s (read DBA) intervention. It is like an in-box database administrator, sitting inside the database itself.

This feature will run as a background process, every 15 mins and perform the following functions:

  • Identify candidates for auto indexing based on table column usage
  • Test the created auto index against the SQL text
    1. If there is performance improvement, the index is made visible
    2. If the performance degrades, the index is marked unusable
    3. Any auto index that is not used for 373 days (default value which is modifiable), is dropped

The Auto Indexing feature can be implemented in a test or development environment and the in-box performance tuner will automatically finetune the SQL’s. If significant or even some performance improvement is seen, we can thoroughly test the index(s) and then decide whether to enable them in the production box. This feature can be used during all pre-production stages of the application lifecycle and can prove substantially useful for OLTP (online transactional processing) and sometimes for mixed workload databases, as well.

Often, the SQL texts, tables, and columns do not change in a database. But the data pattern or data inside the table is constantly changing. Manually created indexes can suddenly become compromised and cause performance issues. Auto-indexing comes in as a star during such times because it can identify bad indexes, create new indexes, validate them against the SQL statements, and make them visible or invisible per the requirements. Phew, so much for one feature…

Below are few examples related to auto indexing that show how to enable, modify, and use this feature in a 19c database.

There is lot more technicality that Oracle has taken into consideration when creating and maintaining auto indexing. We are not including all of that in this article since we are only looking at this feature from a birds-eye view.

Enable auto index: 

begin
dbms_auto_index.configure ('AUTO_INDEX_MODE', 'IMPLEMENT');
end;
/

If you wary of directly implementing the Auto Index feature, using the below example you can create the index but keep it invisible till it is ready for the world. To explain better, the “report_only” feature will capture SQL statements and identify potential candidates that can be helped with an index creation. It then creates those indexes automatically in invisible mode.

begin
dbms_auto_index.configure ('AUTO_INDEX_MODE','REPORT ONLY');
end;
/

Then, the Automatic Index feature will create this “invisible” index, so it remains not known to the database optimizer. This is done to test the index against the SQL statements. If the SQL performance improves, the index is marked “visible”, if not, it stays “invisible”.

The indexes created by Automatic Indexing, begin with “SYS_AI” to distinguish them from manually created indexes. Additionally, there is a new column in the DBA_INDEXES table which will be marked “YES” for auto indexes.

All the statements mentioned above may make you think that “not all indexes will be able to help all SQL statements, it can improve performance for some but fade away others, what happens then?”

Auto Indexing comes to the rescue here too… The Automatic Indexing feature creates a SQL baseline that will stop a SQL sentence that has degraded in performance due to the index, from using that index. But other SQL’s that improved in performance will continue using the index.

Below are few other elements of auto indexing:

To Syntax
disable auto index DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_MODE’,’OFF’);
exclude any schema from the auto index feature DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_EXCLUDE_SCHEMA’, ‘<SCHEMA_NAME>’);
hints that can be used to control the use of auto indexing /*+ USE_AUTO_INDEXES */
/*+ NO_USE_AUTO_INDEXES */

There are few other amazing features that Oracle considered when creating this feature:

  • The system does not consider auto indexes created for any SQL statements that ran for the first time.
  • A DBA can disable an auto index feature for a short time, on an as needed basis.
  • If an auto index job does not complete in the stipulated time, the next run will be skipped, to prevent runaway jobs.

Auto Indexing allows a DBA to worry little less about the performance trouble but does not cause a loss of control in how and when indexes get created.

We almost forgot to include some exciting news concerning this feature. Currently Auto Indexing is only available and licensed with Enterprise Edition on engineered systems, like Exadata. However, for short-term testing purpose ONLY, you can enable the “_exadata_feature_on” parameter on a test environment of an enterprise edition database to test the Auto Indexing feature.

For licensing impact, please check with Oracle support or your Oracle sales resp, as required. Learn more here.

The Auto Indexing feature recently introduced with Oracle Database 19c allows you hand over some of the decisions about index management to the database and enables you (the DBA) to focus on other more-pressing challenges. This automation capability can detect the need for indexes, create them, and drop them automatically paving the way for future innovation in your system.