Oracle 19c Automatic Indexing: A Deep-Dive DBA Guide
One of the most talked-about features introduced in Oracle 19c is Oracle 19c automatic indexing — a machine-learning-driven capability that monitors SQL workloads and creates, validates, and drops indexes on your behalf. After two decades of manually analyzing AWR reports and hand-tuning indexes, I was skeptical. Could Oracle really automate one of the most nuanced parts of a DBA's job? After deploying this feature across several production environments, my answer is: yes, with caveats. In this guide I'll walk you through exactly how automatic indexing works under the hood, how to configure and control it, how to interpret its reports, and where it still needs a human DBA to keep it honest.
What Is Automatic Indexing and Why Does It Matter?
Before Oracle 19c, index management was a mostly manual discipline. You'd pull an AWR or ADDM report, identify high-load SQL, look for full table scans on large objects, and craft B-tree indexes to bring those queries to heel. Good DBAs built intuition for this over years. The problem is scale: modern applications can run thousands of distinct SQL statements, and no human can keep up with all of them.
Oracle 19c automatic indexing addresses this with a background task that runs inside the Automatic Maintenance Window (or on demand). It uses the SQL workload captured in the Automatic Workload Repository (AWR), feeds candidate SQL statements into an internal index advisor, validates proposed indexes using SQL performance analyzer logic, and — only after verifying improvement — makes them visible to the optimizer.
The key design principle is safety first. An automatically created index starts life as an invisible index. The optimizer can only use it during a controlled verification phase. If the index demonstrably improves the SQL being targeted without regressing anything else, it gets promoted to a visible, usable index. If it fails that test, or if it falls out of use over time, Oracle drops it automatically. You never end up with orphaned indexes silently degrading DML performance.
How Automatic Indexing Works: The Internal Workflow
Understanding the mechanics prevents nasty surprises. The automatic indexing task cycles through these stages:
- Workload Capture: The task reads high-load SQL from AWR — specifically looking for SQL with high elapsed time, high CPU, or many executions. Single-execution ad-hoc queries are typically ignored.
- Candidate Index Generation: An internal advisor analyzes predicate usage in WHERE, JOIN ON, and ORDER BY clauses of the captured SQL. It proposes B-tree index candidates (composite and single-column).
- Index Creation (Invisible): Candidate indexes are built as invisible. The optimizer does not use them during normal operations at this stage.
- Verification: The task replays target SQL statements using
OPTIMIZER_USE_INVISIBLE_INDEXES=TRUEin a controlled session. It measures execution plan improvement and checks for regressions in related SQL. - Promotion or Rejection: Indexes that pass verification become visible (status:
VALID). Those that fail remain invisible, are markedUNUSABLE, and are eventually dropped. Oracle retains metadata on rejected candidates to avoid proposing the same index repeatedly. - Ongoing Monitoring: Accepted indexes are monitored for usage. Indexes that stop being used are dropped in a subsequent cycle.
How to Enable Automatic Indexing in Oracle 19c
By default, Oracle 19c ships with automatic indexing in REPORT ONLY mode. This is sensible — Oracle reports what it would do without actually touching your indexes. Here's how to check the current configuration:
-- Check current auto-indexing mode
SELECT parameter_name, parameter_value
FROM dba_auto_index_config;
You'll typically see output like this on a fresh 19c install:
PARAMETER_NAME PARAMETER_VALUE
------------------------------- ---------------
AUTO_INDEX_MODE REPORT ONLY
AUTO_INDEX_SCHEMA NULL
AUTO_INDEX_TABLESPACE NULL
AUTO_INDEX_RETENTION_FOR_AUTO 373
AUTO_INDEX_RETENTION_FOR_UNUSED 30
AUTO_INDEX_SPACE_BUDGET 50
To enable full automatic indexing — allowing Oracle to actually create and manage indexes — use DBMS_AUTO_INDEX.CONFIGURE:
-- Enable automatic indexing (implement mode)
BEGIN
DBMS_AUTO_INDEX.CONFIGURE(
parameter_name => 'AUTO_INDEX_MODE',
parameter_value => 'IMPLEMENT'
);
END;
/
There is also an OFF setting that completely disables the feature, including reporting:
-- Disable automatic indexing entirely
BEGIN
DBMS_AUTO_INDEX.CONFIGURE(
parameter_name => 'AUTO_INDEX_MODE',
parameter_value => 'OFF'
);
END;
/
Controlling Which Schemas Are Eligible
By default, all schemas are eligible for automatic indexing. In most production environments, you'll want to restrict this to application schemas only — you don't want Oracle building indexes on SYS, SYSTEM, or internal schemas:
-- Allow only the APP_OWNER schema
BEGIN
DBMS_AUTO_INDEX.CONFIGURE(
parameter_name => 'AUTO_INDEX_SCHEMA',
parameter_value => 'APP_OWNER',
allow => TRUE
);
END;
/
-- Exclude a schema explicitly (e.g., a reporting schema that manages its own indexes)
BEGIN
DBMS_AUTO_INDEX.CONFIGURE(
parameter_name => 'AUTO_INDEX_SCHEMA',
parameter_value => 'REPORT_USER',
allow => FALSE
);
END;
/
Controlling Tablespace and Space Budget
Automatic indexes are created in the default permanent tablespace unless you specify otherwise. In production, always direct auto indexes to a dedicated tablespace so you can monitor space consumption independently:
-- Direct auto indexes to a specific tablespace
BEGIN
DBMS_AUTO_INDEX.CONFIGURE(
parameter_name => 'AUTO_INDEX_TABLESPACE',
parameter_value => 'AUTO_IDX_TBS'
);
END;
/
-- Set a space budget (in MB) — default is 50% of current index space used
BEGIN
DBMS_AUTO_INDEX.CONFIGURE(
parameter_name => 'AUTO_INDEX_SPACE_BUDGET',
parameter_value => '10240' -- 10 GB
);
END;
/
Reading Automatic Indexing Reports
This is where many DBAs drop the ball — they enable the feature and never look at what it's actually doing. Oracle provides rich reporting through DBMS_AUTO_INDEX.REPORT_ACTIVITY. I run this as part of my weekly health checks.
-- Generate a report for the last 7 days
SET LONG 1000000
SET PAGESIZE 0
SET LINESIZE 200
SELECT DBMS_AUTO_INDEX.REPORT_ACTIVITY(
activity_start => SYSDATE - 7,
activity_end => SYSDATE,
type => 'TEXT',
section => 'ALL',
level => 'TYPICAL'
)
FROM DUAL;
The report sections to pay attention to:
- SUMMARY: How many indexes were created, validated, rejected, and dropped this period.
- INDEX DETAILS: The actual DDL for each proposed or created index, including the table and columns involved.
- SQL DETAILS: Which SQL statements triggered the creation of each index and what plan improvement was measured (reduction in elapsed time or logical reads).
- ERRORS: Any failures during index creation or verification. These are often space-related.
You can also query the underlying views directly for automation and monitoring scripts:
-- View all automatically managed indexes
SELECT index_name,
table_name,
status,
visibility,
auto,
last_analyzed
FROM dba_indexes
WHERE auto = 'YES'
ORDER BY last_analyzed DESC;
-- View auto index activity log
SELECT execution_start,
execution_end,
status,
new_indexes_count,
dropped_indexes_count,
errors_count
FROM dba_auto_index_executions
ORDER BY execution_start DESC
FETCH FIRST 20 ROWS ONLY;
Validating Index Impact Yourself
Trust but verify. Even when Oracle marks an index as VALID, I always do my own spot-check on the SQL statements that were supposed to benefit. Pull the relevant SQL_ID from the activity report and compare plans before and after:
-- Compare execution plans for a target SQL (before/after auto index)
SELECT *
FROM TABLE(
DBMS_XPLAN.DISPLAY_AWR(
sql_id => '&target_sql_id',
format => 'ALLSTATS LAST'
)
);
Also check DBA_AUTO_INDEX_IND_ACTIONS to see the full history of actions taken on a specific index — this view shows the reason for each state transition, which helps when you're explaining to a developer why their table's index landscape changed overnight.
-- History of actions on auto-managed indexes
SELECT index_name,
table_name,
action,
reason,
action_time
FROM dba_auto_index_ind_actions
ORDER BY action_time DESC;
Common Pitfalls and How to Avoid Them
1. Letting It Run Unchecked in OLTP + DW Mixed Workloads
If your Oracle instance serves both an OLTP application and analytical queries, automatic indexing can get confused. A full-scan analytical query might cause Oracle to propose a wide composite index that benefits reporting but hurts OLTP DML throughput on the same table. Always review the "dropped DML performance" metrics in the activity report. In mixed environments, consider restricting auto indexing to the reporting schema only.
2. Not Setting a Tablespace or Space Budget
Without an explicit tablespace, auto indexes land in the table owner's default tablespace — the same one that holds your application data. I've seen production systems run out of space because auto indexing filled up the data tablespace. Always configure AUTO_INDEX_TABLESPACE before enabling IMPLEMENT mode.
3. Trusting REPORT ONLY Mode as a Full Preview
REPORT ONLY mode shows you what candidates would be generated, but it skips the full verification phase. An index that shows up as a recommendation in REPORT ONLY mode might still get rejected during actual IMPLEMENT mode verification. Don't assume every suggestion in the report will become a real index.
4. Ignoring the Errors Section
Automatic indexing errors often surface space problems or privilege issues that require DBA attention. Check errors_count in dba_auto_index_executions regularly. A non-zero error count silently means Oracle is failing to build indexes it thinks are needed.
5. Applying This Feature to Oracle 18c or Earlier
Automatic indexing is exclusively a 19c feature — it does not exist in Oracle 18c, 12cR2, or earlier releases. The DBMS_AUTO_INDEX package and associated views are absent in those versions. If you're planning an upgrade and want to leverage this feature, it's a compelling reason to jump directly to 19c rather than 18c.
Should You Use Automatic Indexing in Production?
My recommendation after running this in multiple production environments: yes, but start in REPORT ONLY for at least 30 days. Review the reports weekly. Get comfortable with what Oracle is proposing before you let it execute. Once you move to IMPLEMENT mode, enforce schema restrictions and set a tablespace budget. Then monitor weekly using the queries shown above.
Automatic indexing is not a replacement for a skilled DBA — it won't help you with index design for batch processes, it can't account for application release changes, and it has no awareness of your maintenance windows or backup schedules. But for steady-state OLTP workloads, it does a credible job catching missed indexes and cleaning up stale ones without human intervention.
For a comprehensive look at how to analyze the SQL workloads that feed automatic indexing decisions, read our guide on interpreting Oracle AWR Reports.
Conclusion
Oracle 19c automatic indexing is a genuinely useful feature when deployed thoughtfully. The invisible index → verification → promotion lifecycle means it won't blindly create indexes that hurt your workload. The DBMS_AUTO_INDEX package gives you fine-grained control over scope, tablespace, and space limits. And the reporting views give you the visibility to keep it accountable. The key is not to treat it as a "set and forget" feature — treat it as a junior DBA whose work you review regularly.
Have you deployed automatic indexing in your production environment? I'd love to hear what surprises you've encountered. Drop a comment below, and if you found this guide useful, subscribe to oratab19c.blogspot.com for more hands-on Oracle 19c technical content published twice a week.
No comments:
Post a Comment