jump to navigation

Oracle 19c Automatic Indexing: Methodology Introduction (After Today) July 24, 2019

Posted by Richard Foote in Automatic Indexing, Oracle Indexes, Oracle19c.
1 comment so far

young americans

For the past month or so I’ve been playing around extensively with the new Oracle 19c “Automatic Indexing” feature, so I thought it was time to start blogging about it.

Considering it’s only in “Version 1” status, my initial impression is very positive in that it works extremely well doing at what it’s initially designed to do. There are certainly “interesting” bits of behavior here and there and some missing functionality, but it’s a whole lot better than DB running sub-optimally due to missing indexes.

This post is purely an introduction in which I’ll just discuss the general methodology behind the Automatic Indexing (AI) capability, initially in a somewhat simplistic manner in order to convey the more important concepts. I’ll expand and discuss many of the actual complexities behind this cool feature in future posts.

The basic concept behind AI is that the DBA via a simple “switch” can turn on the AI  feature and have the Oracle Database automatically create/drop/modify any necessary database indexes as required.

The Oracle database continually captures SQL workloads and keeps track of any new column usages within SQL predicates (as well as associated plans and execution statistics) that might warrant the need for a new index. Note that currently, only equality predicates are considered when determining potential candidate Automatic Indexes.

By default, a background task is executed every 15 minutes that by default runs for up to an hour, to determine if the database can identify any new indexes that “might” be warranted. There are a number of reasons why the tasks might actually take considerably more time than the default 1 hour (I’ll expand on reasons why in future posts).

Candidate indexes that have been detected based on the previous 15 mins database workload are initially created as INVISIBLE/UNUSABLE indexes and hard parsed with the captured SQLs to determine if the index could be considered by the CBO.

If the candidate indexes are indeed viable, the indexes are then created as INVISIBLE/USABLE indexes and verified via the SQL Performance Analyzer using SQL Tuning Sets to determine if the performance of the captured SQLs have indeed improved from the existing plans when using the newly created Automatic Indexes.

If performance actually improves for all captured SQLs using a new Automatic Index, the Automatic Index is made VISIBLE and is now available for general database use. If performance is worse for all captured SQLs, the Automatic Index is made UNUSABLE again and hence not available to the CBO. If performance is better for some SQLs but worse for others, well the story gets a little complicated. In “theory”, the Automatic Index is made USABLE but SQL baselines are created for the SQLs that suffer performance degradation to not use the Automatic Index. Any such SQLs are effectively “blacklisted” and are not (easily) considered for future “new” AI deliberations.  As I’ll discuss in future posts, things are not actually quite as straightforward as that.

So depending on the scenario, Automatic Indexes can end up being in any of the following states:

  • Invisible and Unusable
  • Invisible and Valid
  • Visible and Unusable
  • Visible and Valid

When Visible and Valid, Automatic Indexes can ultimately be:

  • Used by the CBO
  • Not used by the CBO (even by the SQL that caused its creation)

As part of the identify candidate index process, Oracle will consider if a new index can be logically “merged” with an existing index and effectively replace an existing index by dropping and replacing it with a new Automatic Index.

AI will also monitor if existing (either Automatic or Manual) indexes are not currently being used within the database. If after a configurable period of time an index is deemed not to have been used within that time, the index will be automatically dropped during the AI 15 minute task.

This is the basic AI story. The actual story is a tad more complicated and which I’ll expand upon in many many future posts, so keep your questions until then 🙂

 

Importantly, AI is ONLY available on the Exadata platform or on Oracle Cloud environments. It is NOT available on standard On-Premises Oracle 19c deployments. Attempts to turn the feature on where not supported will only result in disappointment:

 

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_MODE’,’IMPLEMENT’);
BEGIN DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_MODE’,’IMPLEMENT’); END;

*
ERROR at line 1:
ORA-40216: feature not supported
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 79
ORA-06512: at “SYS.DBMS_AUTO_INDEX_INTERNAL”, line 9180
ORA-06512: at “SYS.DBMS_AUTO_INDEX”, line 283
ORA-06512: at line 1

 

I’ll next discuss the AI configuration options available to the DBA…