Oracle 19c Automatic Indexing: A Senior DBA's Deep Dive Into What It Really Does
If you have ever inherited a production schema that looked like a graveyard of missing indexes—hundreds of full table scans buried in AWR, developers who dropped an index "to test something" and never put it back, and a backlog of tuning tickets longer than your arm—you already understand why Oracle 19c automatic indexing feels like a revelation. I have been managing Oracle databases for over two decades, and this is one of the few features that genuinely changed how I think about index lifecycle management in large OLTP environments. In this post I am going to cut through the marketing language and give you the real operational picture: how Oracle 19c automatic indexing works under the hood, how to configure it safely, and where it will still let you down if you are not paying attention.
What Problem Does Oracle 19c Automatic Indexing Actually Solve?
Let's be honest about the problem space. Index management at scale is messy. Developers run queries against production-like data in lower environments, miss an index, and the gap only shows up under real load six months later. Meanwhile, DBAs are firefighting and cannot proactively audit every new query pattern that hits the database. The traditional index advisor—running DBMS_ADVISOR against a workload or manually inspecting the SQL Access Advisor—requires deliberate effort and a scheduled window. Most shops run it quarterly at best.
Oracle 19c automatic indexing is a machine-learning-driven, continuous background process that monitors your SQL workload, identifies candidate indexes, creates them as invisible indexes, validates them against the optimizer, measures their real-world benefit, and only makes them visible if they demonstrably improve performance without regressing anything else. Think of it as a tireless index advisor that runs 24/7, learns from your workload, and does not need a service request to act.
This is architecturally different from previous advisory features. The key distinction is the validation loop: Oracle does not just recommend an index—it actually builds the index invisibly, runs representative SQL against both the old and new plan, compares elapsed times and I/O using real optimizer statistics, and only promotes the index to visible if it clears a performance improvement threshold. Rejected candidates are dropped. This closed-loop approach is what separates automatic indexing from anything Oracle shipped before 18c.
How Automatic Indexing Works: The Internal Mechanics
The engine behind Oracle 19c automatic indexing is the Auto Index task, a background job managed by the Automatic Database Diagnostic Monitor (ADDM) infrastructure and scheduled through the Automatic Maintenance Task framework. It runs every 15 minutes by default, driven by the SYS_AUTO_INDEX_TASK task object.
Here is the processing pipeline at a high level:
- Workload Capture: The task continuously reads SQL from the cursor cache and the Automatic Workload Repository (AWR). It focuses on high-load SQL—statements with significant elapsed time, buffer gets, or execution frequency. Single-execution, ad hoc queries are generally excluded.
- Candidate Generation: For each qualifying statement, Oracle's internal index advisor identifies columns that appear in WHERE clauses, JOIN predicates, and ORDER BY expressions that are currently unindexed or only partially indexed. It applies heuristics to avoid obvious dead ends (e.g., low-cardinality columns on their own).
- Index Build (Invisible): Candidate indexes are built with
VISIBILITY = INVISIBLE. They consume real storage and are fully maintained by DML, but the optimizer will not use them unless explicitly hinted—until promotion. - Verification: The task uses
EXPLAIN PLANand real execution feedback to compare plans with and without the new index. It uses a controlled A/B comparison framework. The index must provide a statistically significant improvement (the internal threshold is roughly a 50% reduction in cost) without causing plan regressions in related SQL. - Promotion or Rejection: Indexes that pass verification are set to
VISIBILITY = VISIBLEand marked asAUTOin theDBA_INDEXESview. Those that fail are dropped. Borderline candidates may be kept invisible for a monitoring period before a final decision.
How to Enable Automatic Indexing in Oracle 19c
By default, Oracle 19c automatic indexing is set to REPORT ONLY mode on most on-premises installations. It captures and evaluates candidates but does not create any indexes. This is the right starting point—it lets you audit what the feature would do without touching your schema.
To check the current mode:
-- Check current auto index configuration
SELECT parameter_name, parameter_value
FROM dba_auto_index_config
ORDER BY parameter_name;
The key parameter is AUTO_INDEX_MODE. Possible values are:
OFF— Feature completely disabled; no analysis performed.REPORT ONLY— Analysis runs but no indexes are created. Recommendations are logged.IMPLEMENT— Full automation; indexes are created, validated, and promoted or dropped automatically.
To enable full automation:
-- Enable automatic indexing in IMPLEMENT mode (run as DBA or with EXECUTE on DBMS_AUTO_INDEX)
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE', 'IMPLEMENT');
To revert to report-only (recommended for initial evaluation):
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE', 'REPORT ONLY');
Controlling Which Schemas Participate
You probably do not want automatic indexing running against every schema in the database, especially if you have internal Oracle schemas or third-party application schemas with tightly managed index sets. Use the schema inclusion/exclusion parameters:
-- Allow automatic indexing only for specific schemas
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'SALES', TRUE);
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'OE', TRUE);
-- Exclude a schema explicitly
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'SCOTT', FALSE);
-- Verify schema configuration
SELECT parameter_name, parameter_value
FROM dba_auto_index_config
WHERE parameter_name LIKE '%SCHEMA%';
If no schemas are explicitly included, the feature applies to all non-Oracle-owned schemas (SYS, SYSTEM, etc. are always excluded). Explicit inclusion/exclusion overrides the default behavior on a per-schema basis.
Controlling Tablespace Targets
Auto-created indexes will go into the default tablespace of the owning schema unless you configure a specific target tablespace. For large environments, you want to direct auto indexes to a dedicated tablespace so they do not chew through your application data tablespace:
-- Direct auto indexes to a specific tablespace
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE', 'AUTO_IDX_TS');
Monitoring Automatic Index Activity
Once you have enabled Oracle 19c automatic indexing, the most important views for day-to-day monitoring are DBA_AUTO_INDEXES and the auto index activity report.
-- View all automatically created indexes and their current status
SELECT index_name,
table_name,
index_type,
visibility,
status,
auto,
last_analyzed,
leaf_blocks
FROM dba_auto_indexes
ORDER BY creation_date DESC;
The AUTO column in DBA_INDEXES will show YES for any index created by the automatic indexing feature. The VISIBILITY column tells you whether it has been promoted (VISIBLE) or is still in validation (INVISIBLE).
For a full narrative report covering what the task did in its last execution window, use the built-in report function:
-- Generate an auto index activity report (last 24 hours)
SET LONG 1000000
SET PAGESIZE 0
SET LINESIZE 200
SELECT DBMS_AUTO_INDEX.REPORT_ACTIVITY(
sysdate - 1,
sysdate,
'TEXT',
'ALL',
'ALL'
)
FROM dual;
This report is the equivalent of an AWR report for the automatic indexing subsystem. It shows how many candidates were identified, how many were built, how many passed verification, and the estimated improvement in query performance for each promoted index. I run this weekly as part of my regular DBA health-check routine. For deeper AWR-based performance analysis of the indexes themselves, see our guide on reading Oracle AWR Reports.
Understanding Auto Index States
Not all automatic indexes behave the same way. Here is the full lifecycle state model you need to understand:
- USABLE / INVISIBLE: Index has been built and is under evaluation. The optimizer cannot use it unless you use an
INDEXhint explicitly referencing it—but the auto index task will use it for its internal A/B verification. - USABLE / VISIBLE: Index passed verification; it is now live and the optimizer can choose it freely.
- UNUSABLE: Index failed validation or became stale. Oracle may drop it at the next maintenance window.
- VALID (AUTO): The DBA_INDEXES view shows
AUTO=YESfor indexes created by this feature.
You can also manually force an invisible auto index to visible (or vice versa) using standard ALTER INDEX syntax—but understand that Oracle may override your manual setting at the next task execution if the index's performance profile does not meet the retention criteria.
-- Force an invisible auto index to visible (use cautiously)
ALTER INDEX SYS_AI_ VISIBLE;
-- Or drop an auto index you no longer want
DROP INDEX SYS_AI_;
Common Pitfalls and What to Watch Out For
1. Storage Sprawl from Invisible Candidate Indexes
The invisible evaluation period can accumulate significant storage. Oracle builds the index physically before deciding whether to promote it. In a heavily queried OLTP system with hundreds of candidate columns, you can find yourself with gigabytes of invisible indexes sitting in your tablespace. Monitor DBA_SEGMENTS filtered on segment names starting with SYS_AI_ and ensure your auto index tablespace has room to breathe.
-- Check storage consumed by auto indexes (visible and invisible)
SELECT owner, segment_name, bytes / 1024 / 1024 AS size_mb
FROM dba_segments
WHERE segment_name LIKE 'SYS_AI_%'
ORDER BY bytes DESC;
2. Automatic Indexing Does Not Handle Function-Based or Bitmap Indexes
The current implementation (as of 19c RU through mid-2026) only creates standard B-tree indexes. Function-based indexes, bitmap indexes, and composite indexes on expression columns are outside the scope of automatic indexing. If your workload depends heavily on function-based predicates (UPPER(last_name) = :val), you still need to create those manually.
3. RAC Environments Have Additional Considerations
In a RAC cluster, the auto index task runs on only one instance at a time (the instance that owns the automatic maintenance task schedule). The indexes it creates are visible across all nodes, but the workload capture only reflects the cursor cache of the active node at evaluation time. If your workload distributes unevenly across nodes—common in service-pinned RAC environments—you may find that indexes get created for Node 1's workload while Node 2's hot SQL is never evaluated. Monitor AWR at the cluster level and consider whether your service-to-instance affinity is masking important workload patterns.
4. Third-Party Application Schemas With Fixed Index Sets
Some commercial applications (ERP, HRMS, etc.) explicitly forbid schema changes including index additions, citing support agreement terms. Oracle's automatic indexing will happily create indexes in those schemas unless you explicitly exclude them with AUTO_INDEX_SCHEMA exclusions. Check your vendor support agreements before enabling IMPLEMENT mode.
5. Auto Indexes and Export/Import
When you export a schema with Data Pump and import it into another database, auto indexes tagged with AUTO=YES are exported but will be imported as regular indexes. The auto index management metadata (task history, verification scores) does not transfer. On the target database, these indexes will not be managed by the automatic indexing task unless you drop and re-evaluate them. This matters during migration projects—do not assume the auto index inventory in production will self-replicate to new environments.
Practical Recommendations for Production Deployment
Based on my experience rolling this out across multiple 19c environments:
- Start in
REPORT ONLYmode for at least two weeks. Review the activity report daily. This tells you whether the feature is finding real gaps or chasing noise. - Explicitly set your target tablespace before switching to
IMPLEMENT. Index storage should be predictable and isolated. - Explicitly exclude any schema whose index set is controlled by a vendor or configuration management process.
- Build a weekly monitoring query against
DBA_AUTO_INDEXESandDBA_SEGMENTSto track growth and promotion rates. - Review the auto index report after any major application release or data load cycle, since new query patterns will generate a burst of candidate activity.
- On RAC, validate that your automatic maintenance task schedule is round-robin or pinned to the node with the highest workload share.
Conclusion
Oracle 19c automatic indexing is not a silver bullet, and it is not a replacement for a skilled DBA's judgment—but it is a genuinely useful continuous improvement mechanism for large, fast-moving OLTP workloads where manual index management cannot keep pace with query pattern evolution. The invisible index validation approach is sound engineering: build it, test it against real workload, promote only what works. The pitfalls are manageable as long as you approach deployment thoughtfully: isolate the tablespace, exclude vendor schemas, and monitor the activity reports regularly.
If you found this useful, I'd love to hear how automatic indexing is working in your environment—drop a comment below with your experience, especially if you're running it on RAC or a large CDB. And if you want to stay on top of Oracle 19c performance and internals topics like this, subscribe to the blog at oratab19c.blogspot.com—new posts go up every few days.
