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:

1. 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.
2. 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).
3. 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.
4. Verification: The task uses EXPLAIN PLAN and 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.
5. Promotion or Rejection: Indexes that pass verification are set to VISIBILITY = VISIBLE and marked as AUTO in the DBA_INDEXES view. 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 INDEX hint 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=YES for 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 ONLY mode 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_INDEXES and DBA_SEGMENTS to 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.

How to Read an Oracle AWR Report: A Step-by-Step Guide for DBAs

If you've been doing Oracle performance tuning for any length of time, you know that knowing how to read an Oracle AWR report is one of the most important skills in your toolkit. The Automatic Workload Repository (AWR) report is a snapshot-based diagnostic report that captures database statistics at regular intervals — and it contains just about everything you need to identify what's hurting your database. In this post, I'm going to walk through an AWR report from top to bottom the way I do it after 20 years of performance work, so you know exactly what to look at and in what order.

What Is the Oracle AWR Report?

The Oracle Automatic Workload Repository (AWR) collects, processes, and maintains performance statistics for problem detection and self-tuning purposes. It's available in Oracle 11g and later and requires the Oracle Diagnostic Pack license (or Enterprise Edition with the Diagnostic and Tuning Pack). In Oracle 19c, AWR has been enhanced with global AWR for multitenant environments (CDB-level AWR), but the structure of a single-instance AWR report has been stable for years.

AWR snapshots are taken by default every 60 minutes and retained for 8 days. You can adjust this:

-- Check current AWR settings
SELECT snap_interval, retention
FROM   dba_hist_wr_control;

-- Change snapshot interval to 30 minutes, retention to 14 days
BEGIN
  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
    retention => 20160,   -- 14 days in minutes
    interval  => 30       -- 30 minutes
  );
END;
/

Generating the AWR Report

Before you can analyze anything, you need to generate the report. The quickest way on the command line:

-- Find snapshot IDs for your window of interest
SELECT snap_id, begin_interval_time, end_interval_time
FROM   dba_hist_snapshot
ORDER BY snap_id DESC
FETCH FIRST 20 ROWS ONLY;

-- Generate HTML AWR report between snap_id 1100 and 1105 for instance 1
@$ORACLE_HOME/rdbms/admin/awrrpt.sql

You'll be prompted for report type (text or HTML — always choose HTML for readability), the number of days to look back, and the begin/end snapshot IDs. For programmatic generation:

SELECT output
FROM   TABLE(
         DBMS_WORKLOAD_REPOSITORY.awr_report_html(
           l_dbid       => (SELECT dbid FROM v$database),
           l_inst_num   => 1,
           l_bid        => 1100,
           l_eid        => 1105
         )
       );

The AWR Report Header: Establishing Baseline Context

The first thing I look at on every Oracle AWR report analysis is the header. It tells you the scope and health of the snapshot window before you dig into anything else. Key fields:

• DB Time: Total time all sessions spent doing database work during the interval. This is the denominator for everything that follows.
• Elapsed Time: Wall-clock duration of the snapshot window.
• DB CPU: Time spent on CPU (as opposed to waiting).
• Avg Active Sessions (AAS): DB Time / Elapsed Time in seconds. If this number is consistently above your CPU count, you have a congestion problem.

Example header values that would concern me:

Snap Id      Snap Time         Sessions  Curs/Sess
---------    ---------------   --------  ---------
Begin: 1100  20-Jun-26 10:00:00   320      12.5
End:   1105  20-Jun-26 11:00:00   318      13.1

Elapsed:           60.05 (mins)
DB Time:          847.31 (mins)
DB CPU:           112.44 (mins)
Redo size:    4,521,034,240  bytes

Here, DB Time is 847 minutes over a 60-minute window — that means roughly 14 average active sessions (847/60). If this server has 16 CPUs and those sessions are CPU-bound, you're nearly saturated. If they're wait-bound, something else is the culprit. This ratio is the very first diagnostic signal you should internalize.

Load Profile: The Big Picture in Numbers

The Load Profile section gives you per-second and per-transaction rates for key workload metrics. I look at this to understand the type of workload:

• Logical reads/sec (consistent gets + db block gets): High values with low physical reads → good buffer cache hit ratio. Extremely high values overall → possibly inefficient SQL doing too many block visits.
• Physical reads/sec: Reads from disk. A spike here is worth investigating in Top SQL.
• Parses/sec vs. Hard parses/sec: Hard parses are expensive. If hard parses are > 5-10% of total parses, you have a cursor-sharing or bind variable problem.
• Executes/sec: The throughput rate.
• Redo size/sec: Proxy for write-heaviness of the workload.

A red flag I've seen many times: a system where parses/sec ≈ executes/sec. That almost always means the application is not using bind variables or is reconnecting constantly. Every parse is wasted CPU that could be serving real work.

Instance Efficiency Percentages

This section is a quick sanity check. You want most of these above 99%:

• Buffer Hit %: Should be >99% for OLTP. Below 95% warrants buffer cache investigation.
• Execute to Parse %: Should be high (80%+). Low values → application re-parsing too often.
• Library Hit %: Shared pool efficiency. Low values mean you're wasting time re-loading cursors.
• Parse CPU to Parse Elapsd %: If this is low, parses are spending more time waiting than on CPU — shared pool latch contention or hard parse overload.
• Redo NoWait %: Should be near 100%. Low values mean redo log groups are too small or I/O is too slow for log writes.

Top 10 Foreground Wait Events: The Real Story

This is where I spend most of my time on an AWR report analysis. The Top Wait Events section tells you what Oracle sessions spent the most time waiting for. Do not conflate "foreground" (session waits) with "background" waits — we focus on foreground for user impact.

Common wait events and what they mean:

• db file sequential read: Single-block I/O reads — usually index lookups. High values mean your indexes are doing a lot of work (which can be fine) or you have I/O subsystem latency. Check avg wait time. Under 1ms is excellent; above 5ms is a concern on SSD storage.
• db file scattered read: Multi-block I/O — full table or index fast full scans. High values → full scans, possibly missing indexes.
• log file sync: Sessions waiting for their redo to be flushed to disk on COMMIT. Consistently high → redo log groups are on slow storage, or the application is committing too frequently.
• enq: TX - row lock contention: Sessions blocked waiting for row locks. Application-level locking issue — commits are not happening often enough, or there's a hot row.
• library cache: mutex X: Contention in the shared pool — often too many hard parses or too many versions of the same cursor (literally thousands of child cursors due to bind-sensitive plans).
• latch: cache buffers chains: Hot blocks in the buffer cache — a block being read or modified constantly by many sessions. Usually points to a hot index block (right-hand side of a monotonically increasing index) or a segment header.
• CPU time: Not really a wait — this is time on CPU. If CPU time is at the top of this list, you are CPU-bound. Check Top SQL for CPU-hungry statements.

Here's the mental model I use: calculate each wait event's percentage of total DB Time. If one event accounts for more than 20-25% of DB Time, it is the thing to fix first. Everything else is noise until that one is resolved.

Top SQL: Finding the Offenders

The AWR report includes several Top SQL subsections:

• SQL ordered by Elapsed Time — the most important for overall throughput impact
• SQL ordered by CPU Time — pure CPU consumers
• SQL ordered by Gets — logical I/O heavy hitters
• SQL ordered by Reads — physical I/O heavy hitters
• SQL ordered by Executions — high-frequency statements
• SQL ordered by Parse Calls — statements being re-parsed frequently

For each statement you identify, grab the SQL_ID and examine it further:

-- Pull the full SQL text from AWR history
SELECT sql_text
FROM   dba_hist_sqltext
WHERE  sql_id = '&sql_id';

-- Review execution history and plan hash values
SELECT snap_id,
       executions_delta,
       elapsed_time_delta / NULLIF(executions_delta, 0) / 1e6 AS avg_elapsed_secs,
       rows_processed_delta / NULLIF(executions_delta, 0) AS avg_rows,
       plan_hash_value
FROM   dba_hist_sqlstat
WHERE  sql_id = '&sql_id'
ORDER  BY snap_id DESC;

-- Retrieve the actual execution plan used during the window
SELECT * FROM TABLE(
  DBMS_XPLAN.display_awr('&sql_id', format => 'ALLSTATS LAST')
);

The plan_hash_value column is gold. If you see a statement that had one plan hash value for months and suddenly switched to a new one right before a performance incident, you've found your culprit. Pin the old plan using SQL Plan Baselines — see our guide on Oracle SQL Plan Baselines for Performance Stability.

Memory Statistics: SGA and PGA

The SGA Breakdown and PGA sections confirm whether memory is sized correctly:

• Buffer Cache Advisory: Shows estimated physical reads if the cache were larger or smaller. If the curve flattens before your current size, you're fine. If it's still steep at your current size, you need more buffer cache.
• PGA Memory Advisory: Shows estimated PGA over-allocation factor. A factor >1 means Oracle had to spill sorts and hash joins to disk — you need more PGA.
• Shared Pool Advisory: Tracks library cache hit ratios at different shared pool sizes.

-- Confirm PGA spill to disk from AWR
SELECT snap_id,
       cache_hit_percentage,
       over_alloc_count
FROM   dba_hist_pgastat
WHERE  name = 'cache hit percentage'
ORDER  BY snap_id DESC;

Common Pitfalls When Reading AWR Reports

Over the years I've seen DBAs make the same mistakes on Oracle AWR report analysis. Avoid these:

1. Comparing across different snapshot intervals. A 30-minute AWR snapshot and a 60-minute AWR snapshot will show wildly different absolute numbers. Always normalize to per-second rates or look at percentages of DB Time.
2. Ignoring "idle" wait events. Events like SQL*Net message from client and jobq slave wait are idle waits — the session is doing nothing. The AWR Top 10 shows all wait events; filter out idle ones when computing the percentage of active work.
3. Fixing the second problem before fixing the first. The wait event at the top of the list is the one to fix. Don't go hunting for the third or fourth event while the number-one wait is still unresolved — fixing it may eliminate the downstream waits entirely.
4. Tunnel vision on SQL without checking instance-level metrics. Sometimes the SQL looks fine but the storage is degraded, or a node is down in RAC and all connections are on one instance. The header and the Load Profile often tell this story before you even get to Top SQL.
5. Generating AWR reports over windows that span a restart. Post-startup stats are not comparable to steady-state stats. Always check the header for instance start time and avoid snapshots that cross it.

A Quick Triage Workflow

When I open an AWR report under pressure — because the business is calling and production is slow — I use this 5-minute triage sequence:

1. Header → AAS. Is AAS above CPU count? If yes, resource saturation. If no, latency problem.
2. Top Wait Events → #1 event. What are sessions waiting for? This is the primary constraint.
3. Load Profile → Hard Parses. Are hard parses unusually high? If yes, application or shared pool problem.
4. Top SQL by Elapsed Time → Top 1-3 statements. Grab SQL_IDs, check execution plans.
5. Instance Efficiency → Buffer Hit %, Redo NoWait %. Quick confirmation of cache and redo health.

In most cases, five minutes of this process narrows the problem to one of three buckets: bad SQL, resource saturation, or I/O subsystem degradation. Everything else is a follow-up investigation.

Conclusion

Knowing how to read an Oracle AWR report efficiently is what separates a reactive DBA from a proactive one. The report contains an enormous amount of data, but the sequence matters: start with the header to understand workload intensity, move to wait events to understand the primary constraint, then drill into Top SQL to find the specific statements driving that constraint. Combined with the advisory sections for memory and the Load Profile for workload characterization, you have a complete picture of database health in a single document.

If you found this Oracle AWR report tutorial useful, drop a comment below — I'd especially love to hear about specific wait events you're struggling to interpret. And if you want posts like this delivered to you twice a week, subscribe to oratab19c.blogspot.com. Next up: SQL Plan Baselines for locking in good execution plans and preventing unplanned regressions.