Oracle 19c Automatic Indexing in Production: What to Trust and What to Override

How Automatic Indexing Works

Oracle 19c Automatic Indexing runs as a background task managed by the Automatic Database Diagnostic Monitor (ADDM) infrastructure. Every 15 minutes (by default), it analyzes SQL statements from the cursor cache and AWR, identifies high-load queries that could benefit from additional indexes, and uses SQL Plan Management (SPM) baselines to safely validate candidate indexes before making them visible to the optimizer.

The validation process is what makes it safer than simply creating indexes automatically. A candidate index is created as invisible — the optimizer cannot use it. Oracle then runs the candidate query with the index manually forced via a SQL profile, compares actual execution performance against the baseline, and only makes the index visible if it demonstrates a statistically significant improvement. If the improvement falls below the threshold, the index remains invisible and is eventually dropped.

This is fundamentally sound engineering. The risk isn't the mechanism — it's the boundary conditions where the mechanism's inputs are unreliable.

Enabling and Configuring Automatic Indexing

-- Check current Automatic Indexing configuration
SELECT parameter_name, parameter_value
FROM dba_auto_index_config;

-- Enable Automatic Indexing (IMPLEMENT = allow creation of visible indexes)
-- REPORT ONLY = create invisible indexes and log recommendations without making them visible
-- OFF = disable entirely
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE', 'IMPLEMENT');

-- Restrict Automatic Indexing to a specific schema (recommended for production)
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'APP_SCHEMA', allow => TRUE);

-- Set retention period for unused auto indexes before they are dropped
-- Default is 373 days; reduce for aggressive cleanup
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO', '90');

-- Set retention for manually created indexes that AI found unnecessary
-- Default 90 days
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_MANUAL', NULL);

Starting in REPORT ONLY mode is the right approach for any production database being introduced to Automatic Indexing for the first time. It gives you weeks of visibility into what the feature would do before it starts making changes.

Reading the Automatic Indexing Report

-- Generate the Automatic Indexing activity report
SELECT DBMS_AUTO_INDEX.REPORT_ACTIVITY(
  activity_start => SYSDATE - 7,
  activity_end   => SYSDATE,
  type           => 'TEXT',
  section        => 'ALL',
  top_n          => 10
) FROM dual;

-- Check the index candidates and their status
SELECT index_name, table_name, column_name, index_type,
       index_status, last_reported, error_message
FROM dba_auto_index_ind_cols
ORDER BY last_reported DESC;

-- View all auto-created indexes with their verification history
SELECT ai.index_name, ai.table_name,
       ai.creation_date, ai.status,
       ai.auto_drop,
       ai.last_used
FROM dba_auto_indexes ai
ORDER BY ai.creation_date DESC;

The status column in dba_auto_indexes is the key field to monitor. Indexes cycle through: CANDIDATE → INVISIBLE (under testing) → VISIBLE (validated and active) → UNUSABLE (disabled but not dropped) → dropped. An index stuck in INVISIBLE for more than a few days typically means the validation test wasn't able to find a workload execution that used it — which itself is useful information.

What Automatic Indexing Gets Right

Automatic Indexing performs well on a specific class of problem: single-column or simple composite indexes on high-cardinality columns that appear in WHERE clauses of high-frequency queries. A new application feature that introduces a query pattern your schema wasn't indexed for — Automatic Indexing will usually identify and create the needed index within 24-48 hours of the feature going live.

It also handles index cleanup reasonably well. Indexes that stop being used after an application code change are flagged and eventually dropped, which prevents index bloat from accumulating over years of application evolution. This alone is worth something — most production Oracle databases we audit have 15-25% of indexes that haven't been used in over six months.

Where Automatic Indexing Fails: The Four Problem Categories

1. Month-End and Quarter-End Batch Workloads

Automatic Indexing analyzes recent workload patterns. If your database has a significant batch processing window that runs monthly or quarterly — month-end close, regulatory reporting, quarterly reconciliation — the indexes those batch jobs need may not appear in recent workload data during the weeks between runs.

Automatic Indexing will see the batch queries, create candidate indexes, validate them during the batch window, and mark them as useful. But if the next batch run is six weeks away, the system may flag those indexes as unused and drop them before the next batch cycle. Then the batch runs without the indexes, performance degrades, and Automatic Indexing recreates them — a cycle of create/drop/create that adds overhead without solving the underlying problem.

-- Protect specific indexes from Automatic Indexing management
-- Useful for indexes critical to infrequent batch workloads
EXEC DBMS_AUTO_INDEX.CONFIGURE(
  'AUTO_INDEX_RETENTION_FOR_AUTO', '400'
); -- Keep auto indexes for longer than your batch cycle

-- Or explicitly exclude a table from auto index management
-- (manual indexes on this table won't be touched)
EXEC DBMS_AUTO_INDEX.CONFIGURE(
  'AUTO_INDEX_SCHEMA', 'BATCH_SCHEMA', allow => FALSE
);

2. Composite Index Column Ordering

Automatic Indexing creates single-column indexes and simple multi-column indexes, but it doesn't always get multi-column index column ordering right. A composite index on (status, customer_id) and one on (customer_id, status) serve different query patterns. Automatic Indexing chooses column order based on selectivity and frequency analysis, but it doesn't account for range-scan queries that require a specific leading column.

If you have a query pattern like WHERE status = 'pending' AND customer_id BETWEEN 1000 AND 2000, the optimal index has status as the leading column (equality filter first, range filter second). Automatic Indexing may create (customer_id, status) instead if customer_id appears more frequently across all analyzed queries. The index is used but not optimally.

3. Function-Based Indexes

Automatic Indexing does not create function-based indexes. If your application has queries like WHERE UPPER(last_name) = :name or WHERE TRUNC(created_date) = :date, Automatic Indexing will see these as unindexable and will not recommend anything. You must create function-based indexes manually.

-- These patterns require manual function-based indexes
-- Automatic Indexing will not create these
CREATE INDEX idx_orders_created_trunc
ON orders (TRUNC(created_date));

CREATE INDEX idx_customers_upper_name
ON customers (UPPER(last_name));

CREATE INDEX idx_orders_json_status
ON orders (JSON_VALUE(metadata, '$.status'));

4. Partitioned Table Index Strategy

On partitioned tables, Automatic Indexing creates local partitioned indexes by default. This is usually correct — a local index on a range-partitioned table allows partition pruning. But for queries that span multiple partitions or use columns that aren't the partition key, a global index may be more efficient. Automatic Indexing doesn't evaluate the local vs. global tradeoff; it defaults to local. For partitioned tables with cross-partition query patterns, you need to manually create global indexes where appropriate.

The Override Strategy: Locking Specific Indexes

When you need to prevent Automatic Indexing from dropping an index you've manually created and validated, use the DBMS_AUTO_INDEX exclusion mechanism:

-- Prevent Automatic Indexing from dropping a manually-created index
-- by marking it as excluded from auto-management
-- NOTE: This does not exist as a built-in API in 19c base release
-- Use the workaround: invisible then visible cycle with comment

-- Alternative: mark the index with a hint in its name to track manually
-- and set a long retention period for your auto indexes

-- Check which indexes AI has flagged as candidates for dropping
SELECT index_name, table_name, status, last_used, auto_drop
FROM dba_auto_indexes
WHERE status = 'UNUSABLE'
   OR (last_used < SYSDATE - 90 AND auto_drop = 'YES')
ORDER BY last_used NULLS FIRST;

-- If a manually-created index appears in dba_auto_indexes, AI found it redundant
-- Review the recommendation before accepting it
SELECT *
FROM dba_auto_index_verifications
WHERE index_name = 'YOUR_INDEX_NAME'
ORDER BY verification_date DESC;

Using REPORT ONLY Mode for Change Control

In regulated environments where all schema changes require change control approval, IMPLEMENT mode is incompatible with your process — it makes DDL changes autonomously. REPORT ONLY mode is the answer: Automatic Indexing generates recommendations but creates only invisible indexes. You review the report, approve specific recommendations, and create the indexes manually through your standard change control process.

-- Set to REPORT ONLY for change-controlled environments
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE', 'REPORT ONLY');

-- Weekly review query: what would AI have created?
SELECT DBMS_AUTO_INDEX.REPORT_ACTIVITY(
  activity_start => SYSDATE - 7,
  activity_end   => SYSDATE,
  type           => 'TEXT',
  section        => 'INDEX CANDIDATES'
) FROM dual;

-- For each recommended index, review the verification details
SELECT v.index_name, v.table_name,
       v.column_names,
       v.workload_impact,
       v.error_message,
       v.verification_date
FROM dba_auto_index_verifications v
WHERE v.verification_date > SYSDATE - 7
ORDER BY v.workload_impact DESC;

The Practical Stance: Trust But Verify

The right production posture for Oracle 19c Automatic Indexing is neither full automation nor full suppression. For most OLTP workloads, IMPLEMENT mode with schema restrictions to your application schema (not SYS, SYSTEM, or DBA-managed schemas) and a weekly review of the activity report is a reasonable operational posture.

Explicitly manage the exceptions: batch workloads with infrequent cycles, function-based index requirements, cross-partition query patterns on partitioned tables, and any index that your team has tuned manually through AWR analysis. For these, disable automatic management at the schema or table level and maintain those indexes through your standard DBA workflow.

Automatic Indexing doesn't replace DBA judgment. It handles the routine — the obvious missing single-column indexes on a growing application's new query patterns. It doesn't handle the nuanced, which is still the DBA's job.