Boosting Performance with Oracle Automatic Indexing: Smarter Tuning for Modern Databases

 Tuning SQL performance has always been one of the most challenging—and time-consuming—tasks for DBAs. With growing data volumes and evolving applications, finding the right indexes can feel like a never-ending puzzle.

Enter Oracle Automatic Indexing, a groundbreaking feature introduced in Oracle Database 19c and continuously improved in newer versions. It’s Oracle’s answer to the question: “Why not let the database tune itself?”

In this blog post, we’ll break down what Automatic Indexing is, how it works, and why it’s changing the game for performance optimization.

What Is Oracle Automatic Indexing?

Automatic Indexing is an AI-driven feature that allows the Oracle Database to:

  • Monitor SQL workloads

  • Create indexes automatically when they improve performance

  • Test them in the background

  • Drop unused or ineffective indexes

The goal is simple: improve query performance without manual intervention, while ensuring stability and avoiding over-indexing.

Unlike static advisors or manual tuning tools, Automatic Indexing is an ongoing process. It continuously learns and adapts to changing workloads—ideal for dynamic, modern environments.

How Does It Work?

The process follows these key steps:

  1. Monitor Workload: The database observes SQL statements running on the system.

  2. Analyze Candidate Indexes: Based on performance metrics, it identifies potentially useful indexes.

  3. Create Hypothetical Indexes: These are “invisible” test indexes created in the background.

  4. Evaluate Performance: Oracle runs queries against the new indexes (without impacting live queries).

  5. Implement or Reject: If the index improves performance, it's made permanent and visible; if not, it’s discarded.

This entire cycle is handled automatically, usually via a background task that runs during maintenance windows.

Key Benefits

  • ⚡ Improved Query Performance: Indexes are optimized for actual workload patterns, not guesswork.

  • 🤖 Less Manual Tuning: Frees up DBAs to focus on higher-value tasks.

  • 🔁 Adaptability: As workloads change, the system adjusts by adding or removing indexes.

  • 📊 Detailed Reporting: Automatic Indexing provides visibility into what it’s doing via system views and reports.

Real-World Use Cases

  • Dynamic SaaS Applications: Where workloads shift frequently and require rapid adaptation.

  • Cloud Databases: Especially in Oracle Autonomous Database, where tuning must be hands-off.

  • Large Data Warehouses: Where manual indexing becomes impractical due to volume and complexity.

Getting Started

To enable Automatic Indexing on an Oracle 19c+ database:

BEGIN
  DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');
END;

You can check activity using:


SELECT * FROM DBA_AUTO_INDEX_EXECUTIONS;
SELECT * FROM DBA_AUTO_INDEX_REPORTS;

And manage settings with the DBMS_AUTO_INDEX package.

Note: This feature is licensed separately in Oracle Enterprise Edition (except in Oracle Autonomous Database, where it's built-in).

Final Thoughts

Oracle Automatic Indexing represents a major step toward self-managing databases. In an era where agility and speed are critical, letting the database manage its own performance tuning is no longer just a luxury—it’s becoming a necessity.

Whether you're managing hundreds of applications or just starting with Oracle 19c or 23c, consider taking advantage of Automatic Indexing to optimize your performance without lifting a finger.

Comments

Post a Comment

Popular posts from this blog

How to Solve - "WAIT FOR EMON PROCESS NTFNS"

Event Monitor Process in short called as EMON is generally used by oracle to send notification. Often applications uses Database Change Notification process also called as DBCN to be aware of changes in the database, based on these notifications they take further actions. Often clients subscribe to the database and notifications are send as callback to them. In some cases you will notice database sessions are showing wait event "WAIT FOR EMON PROCESS NTFNS" and it can even make your database un-responsive. One observation can be these processes are taking more CPU (may not in be in all cases). As stated EMON process deals with database notification and it's spawn and gets down itself and Administrators have no control on theses processes. Anyone can go through oracle documents to read more EMON functioning process.  EMON uses oracle advanced queues and database queue notifications into them and EMON process these notifications to clients based on their subscriptions. In s...
Read more

Query Regression - "OR" Transformation Oracle 19c

Recently while upgrading Oracle Database from version 12.1 to 19.10 found there were couple of queries which was running slow post upgrade. Usually these type of behavior is seen while upgrading to newer version and oracle provide various ways to fix them. The difference between good and bad plan was an introduction of  "MERGE JOIN CARTESIAN" (aka MJC) and that's in where regressed query in 19c taking most of the time. MJC sometimes works for queries but in this case it was clearly not helping.  We had following options here:     1. Copy the profile from 12c to 19c database and it worked, but the problem was not just to specific query but with many similar query. This means profile option is not very attractive in this case.     2. We also tried running query with OFE 12.1 to use the same optimizer features as previous version and query behaves well. Though this option can't be placed as we need to use 19c OFE and can be placed only for testing purpose. ...
Read more

Error Processing Request. - Oracle APEX Post upgrade (Invalid WWV_FLOW_SESSION_RAS)

There would be occasions where you would upgrade your oracle database which is hosting your APEX and post upgrade you get error while opening APEX URL " Error Processing Request Contact your application administrator " This looks very strange as you have just upgraded only database. Database version and APEX version are compatible. For Argument lets say both are on 19c.  Now since UI doesn't give detailed information about the error you would need to get to backend oracle database and query view apex_debug_messages.     select * from apex_debug_messages; Looking at the results of this view you would get the clue of the error in this case it was:      ORA-04063 package body APEX_190100.WWV_FLOW_SESSION_RAS has error This means this package body has problem and looking at the status of this body it's found to be invalid and when you try to compile it get following error      alter package APEX_050100.WWV_FLOW_SESSION_RAS compile body;  ...
Read more