Oracle Automatic Indexing: Moving From Report-Only to Implement Safely

Start With Report-Only Before Enabling Implement

Oracle Automatic Indexing (19c+) can create indexes automatically based on your SQL workload. If you enable IMPLEMENT mode on a busy production system without reviewing recommendations first, the background task SYS_AUTO_INDEX_TASK may create many indexes quickly, increasing DML maintenance overhead and storage use. The useful takeaway is to run in REPORT_ONLY mode for one to two weeks, review the candidates, and only switch to IMPLEMENT after you confirm the suggested indexes match your workload.

How Automatic Indexing Validates New Indexes

The feature analyzes SQL statements from AWR and ASH, identifies missing-index candidates, creates them as invisible indexes, and tests them using SQL Plan Management. An index becomes visible and eligible for use only if it improves the target statement without regressing other SQL statements. Automatic Indexing does not create function-based, bitmap, domain, or global indexes, and it creates local indexes for partitioned tables but not global ones.

Configuration Example

Run the following in SQL*Plus, SQLcl, or Oracle SQL Developer as a user with the DBA role, typically SYS or another privileged administrative account. Replace HR and OE with your actual application schema names; schema names are case-sensitive.

-- View current Automatic Indexing settings
SELECT * FROM DBA_AUTO_INDEX_CONFIG;

-- Start in report-only mode: recommendations are generated but no indexes are created
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','REPORT_ONLY');

-- Retain auto-created indexes for one year; manual indexes for 90 days
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO','365');
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_MANUAL','90');

-- Include specific schemas (exact case required)
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA','HR','TRUE');
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA','OE','TRUE');

-- Exclude administrative schemas; this list overrides inclusion
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_EXCLUDED_SCHEMAS','SYS,SYSTEM');

Expected check: query DBA_AUTO_INDEX_CONFIG afterward and confirm AUTO_INDEX_MODE equals REPORT_ONLY and the schema rows list HR and OE. A typo such as 'hr' instead of 'HR' silently excludes nothing, so no indexes are generated for that schema.

Verify What the Task Actually Did

Automatic Indexing runs inside the database maintenance window as SYS_AUTO_INDEX_TASK. If maintenance windows are disabled or too short, the task may not run. After the task has executed, review the results:

-- Recent task executions
SELECT EXECUTION_NAME, STATUS, START_TIME, END_TIME,
       INDEXES_CREATED, INDEXES_DROPPED
FROM DBA_AUTO_INDEX_EXECUTIONS
ORDER BY START_TIME DESC
FETCH FIRST 5 ROWS ONLY;

-- Indexes created by the feature
SELECT INDEX_NAME, TABLE_NAME, VISIBILITY, AUTO
FROM DBA_INDEXES
WHERE AUTO = 'YES';

-- SQL statements affected by the most recent run
SELECT DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY() FROM DUAL;

Look for INDEXES_CREATED greater than zero in DBA_AUTO_INDEX_EXECUTIONS and review REPORT_LAST_ACTIVITY for SQL Statements Improved versus SQL Statements Regressed. If regressed is greater than zero, do not enable IMPLEMENT yet; investigate the offending SQL_IDs first.

Switch to Implement Mode Only After Review

Once report-only output shows indexes you are willing to keep, enable implementation:

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');

Required permission: DBA role or EXECUTE on DBMS_AUTO_INDEX. Risk: in IMPLEMENT mode, validated indexes become visible and usable by the optimizer immediately. On high-DML tables, each new index adds overhead to INSERT, UPDATE, and DELETE statements. Monitor DBA_SEGMENTS for index growth:

SELECT SUM(BYTES)/1024/1024 AS MB
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME IN (
  SELECT INDEX_NAME FROM DBA_INDEXES WHERE AUTO = 'YES'
);

Limits and Common Mistakes

• Maintenance window dependency: If the maintenance window is disabled, Automatic Indexing does not run, leaving gaps in coverage with no error alert to the application.
• Case-sensitive schema names: AUTO_INDEX_SCHEMA and AUTO_INDEX_EXCLUDED_SCHEMAS require exact schema name casing. Exclusion overrides inclusion.
• Invisible until validated: New auto indexes are invisible at creation. Application hints such as /*+ INDEX(...) */ do not use them until they are promoted to visible.
• Unsupported index types: The feature does not create function-based, bitmap, domain, or global indexes. Complex access paths still require manual design.
• Space location: Auto indexes are created in the same tablespace as the parent table unless DEFAULT_TABLESPACE is configured. This can fill the table's tablespace unexpectedly.

Reverting the Change

Enabling IMPLEMENT changes database state by allowing new indexes to be created and promoted. To revert, return to report-only mode or disable the feature entirely:

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','REPORT_ONLY');
-- Or disable completely
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');

Dropping indexes already created by the feature is a separate operation. Use DBA_AUTO_INDEX_IND_ACTIONS to identify auto indexes, then drop them manually with DROP INDEX if they are no longer needed.

Practical Check Before Production Use

1. Confirm AUTO_INDEX_MODE is REPORT_ONLY in DBA_AUTO_INDEX_CONFIG.
2. Verify schema inclusion lists use exact, case-sensitive names.
3. Confirm maintenance windows are enabled and long enough for SYS_AUTO_INDEX_TASK to complete.
4. Run REPORT_LAST_ACTIVITY and check that regressed SQL statements are zero or acceptable.
5. Check DBA_INDEXES for AUTO = 'YES' and VISIBILITY = 'VISIBLE' to see promoted indexes.
6. Measure index size in DBA_SEGMENTS before and after enabling IMPLEMENT.

Automatic Indexing is a workload-driven tool, not a set-and-forget replacement for manual indexing. Review its output regularly, especially after major application releases or changes in query patterns.