Oracle DBA

Oracle 19c elevates database intelligence by enabling Automatic Indexing by default, empowering DBAs and architects to achieve optimal performance without manual intervention. This strategic capability transforms the database into a self-managing ecosystem where indexing decisions are continuously evaluated, implemented, and refined based on real workload patterns.

Automatic Indexing monitors SQL statements, identifies performance bottlenecks, and transparently creates, validates, and maintains indexes that enhance query efficiency. It functions as an adaptive, workload-aware optimization engine that drives sustained performance gains while reducing operational overhead.

Automatic Indexing operates in the background, generating candidate indexes, validating them against actual workload improvements, and implementing them only when they demonstrate clear performance value. It also auto-tunes itself by dropping or marking unused indexes, ensuring a lean and effective indexing structure. This innovation dramatically simplifies performance engineering, especially for dynamic applications where query patterns evolve.

Automatic Indexing metadata and decisions can be observed directly through system views. DBAs retain full transparency and governance, with the option to override or fine-tune behavior if required.

Below is a demonstration of how Automatic Indexing can be monitored and configured.

Checking Automatic Indexing Status

SELECT parameter_name, parameter_value 
FROM dba_auto_index_config;

Enabling or Disabling Automatic Indexing (If Needed)

ALTER SYSTEM SET AUTO_INDEX = ON;

ALTER SYSTEM SET AUTO_INDEX = OFF;

Viewing Automatic Indexes Created by Oracle

SELECT auto_index_mode, auto_index_candidate, auto_index_status, index_name 
FROM dba_indexes 
WHERE origin = 'AUTO';

Viewing Automatic Index Activity

SELECT task_id, type, status, created, details 
FROM dba_auto_index_logs
ORDER BY created DESC;

Example: Forcing a Test Scenario

Suppose an application runs a frequent join query on a column without an index. Oracle Automatic Indexing evaluates this workload, identifies the missing index, builds a candidate index, validates its benefit, and if it provides improvement, promotes it to a permanent auto index.

SELECT e.employee_id, e.department_id, d.department_name
FROM employees e
JOIN departments d 
ON e.department_id = d.department_id;

If this join is executed repeatedly and contributes to significant resource consumption, Oracle may automatically create an index such as:

CREATE INDEX "SYS_AI_8x9m2s3j4n1" 
ON "HR"."EMPLOYEES" ("DEPARTMENT_ID");

This index is created automatically, validated internally, and only fully accepted if it improves the overall performance of the system.

Automatic Indexing in Oracle 19c represents a forward-thinking, adaptive, and workload-aware approach to performance tuning. It empowers organizations to benefit from continuous optimization, reduces manual maintenance, and positions the database as an intelligent partner in the performance engineering journey.