Oracle Autonomous Database (ADB) is often described in marketing terms: “self-driving, self-securing, self-repairing.” For architects and senior DBAs, the more useful question is: what does it actually do automatically, how does it do it, and where do you need to intervene? This article peels back the automation layers.

The Infrastructure Foundation

Every ADB instance runs on Exadata Cloud Service (ExaCS). This is not incidental — it is the technical prerequisite for the automation that ADB delivers. The Smart Scan offload, HCC compression, and storage index features described elsewhere in this blog are all active beneath every ADB workload.

ADB comes in two shapes:

  • Autonomous Data Warehouse (ADW) — optimised for analytics; columnar in-memory cache enabled by default, parallel query auto-enabled.
  • Autonomous Transaction Processing (ATP) — optimised for OLTP; parallel query conservative, connection pools via DRCP.

Both run Oracle Database 19c (or 23ai in newer provisioning) with a set of automated management layers on top.

Automatic Indexing

Automatic indexing is the most impactful ADB automation for DBA practitioners. The database continuously monitors SQL workloads, identifies tables with full table scans that could benefit from indexes, creates candidate indexes invisibly, validates them against the actual workload, and promotes effective ones to visible status.

How the Cycle Works

  1. Observation: The optimizer collects workload SQL from V$SQL and the AWR.
  2. Candidate selection: The auto-index background process identifies high-frequency SQL with large full table scans.
  3. Index creation (invisible): Candidate indexes are created as INVISIBLE — the optimizer can only use them if explicitly told to.
  4. Validation: The auto-index task runs the SQL with the candidate index forced and compares actual execution statistics.
  5. Promotion or rejection: If the index reduces elapsed time or logical I/Os below a threshold, it is promoted to VISIBLE. Otherwise, it is marked UNUSABLE and eventually dropped.

Monitoring Automatic Indexing

-- View all auto-created indexes
SELECT index_name, table_name, auto, visibility, status
FROM dba_indexes
WHERE auto = 'YES'
ORDER BY last_analyzed DESC;

-- Detailed auto-index activity report
SELECT DBMS_AUTO_INDEX.REPORT_ACTIVITY(
  activity_start => SYSDATE - 7,
  activity_end   => SYSDATE,
  type           => 'TEXT',
  section        => 'ALL'
) AS report
FROM DUAL;

Example output snippet:

SUMMARY
-------
Index candidates considered  : 18
Indexes created (visible)     : 5
Indexes created (invisible)   : 3
Indexes dropped               : 2

Controlling Automatic Indexing

-- Disable automatic indexing for the entire CDB (or at PDB level)
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE', 'OFF');

-- Allow auto-index but don't make any visible (report-only mode)
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE', 'REPORT ONLY');

-- Exclude specific schemas from auto-indexing
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'EXCLUDE', schema_list => 'SCHEMA1,SCHEMA2');

Automatic Optimizer Statistics

ADB runs a continuous statistics maintenance job that goes beyond what standard DBMS_STATS.GATHER_DATABASE_STATS does. The automation includes:

  • Incremental statistics for partitioned tables (only changed partitions are re-analysed).
  • Real-time statistics (Oracle 19c+): statistics are maintained on-the-fly during bulk DML without a separate gather job.
  • Pending statistics evaluation: new statistics are validated against the workload before being published.
-- Check whether real-time statistics are active
SELECT pname, pval1
FROM sys.optstat_hist_control$
WHERE pname = 'APPROXIMATE_NDV_ALGORITHM';

-- View statistics age for critical tables
SELECT table_name, last_analyzed, num_rows, stale_stats
FROM dba_tab_statistics
WHERE stale_stats = 'YES'
ORDER BY table_name;

Automatic Resource Management

ADB uses Oracle Database Resource Manager internally with dynamically adjusted consumer groups. The automation targets three resource dimensions:

  • CPU: queries competing for CPU are queued by the resource manager. ADB’s consumer groups map roughly to: HIGH (parallel, large), MEDIUM, LOW.
  • Parallel execution: ADB auto-tunes the degree of parallelism (DOP) per query based on available resources and statement type (ADW favours high DOP; ATP constrains it).
  • Concurrency: ADB enforces per-service concurrency limits to prevent runaway connection storms.

For ADW workloads, the three services (HIGH/MEDIUM/LOW) translate directly to consumer groups. Use the HIGH service only for large analytical queries — it consumes more parallel server resources per query.

-- Monitor resource manager statistics
SELECT consumer_group_name, cpu_waits, cpu_wait_time, yields
FROM v$rsrcmgrmetric_history
WHERE consumer_group_name LIKE 'ORA$%'
ORDER BY end_time DESC, consumer_group_name;

Storage Tiering and Compression in ADB

All ADB data is stored on Exadata Smart Flash Cache by default for hot data. Cold data migrates to magnetic storage automatically. HCC (Hybrid Columnar Compression) is applied by default on ADW tables during bulk loads.

Verifying HCC Compression

-- Check compression type per segment
SELECT table_name, compress_for, compression
FROM dba_tables
WHERE owner = 'SCHEMA1'
ORDER BY table_name;

TABLE_NAME        COMPRESS_FOR    COMPRESSION
----------------- --------------- -----------
SALES_HISTORY     QUERY HIGH      ENABLED
ORDERS            BASIC           ENABLED
CUSTOMERS                         DISABLED

HCC QUERY HIGH is appropriate for read-mostly analytical tables. Do not use HCC on OLTP tables with frequent single-row updates — the decompression overhead per update is prohibitive.

Patching and Availability Automation

ADB patches are applied automatically. Patch windows are configurable:

-- View scheduled maintenance windows
SELECT window_name, enabled, next_start_date, duration
FROM dba_autotask_window_clients
WHERE window_name LIKE 'WEEKEND%';

For RAC-based ADB (Dedicated or Serverless with high-availability enabled), patches are applied using rolling methodology — one instance at a time — with zero application downtime if the connection pool handles reconnection.

Where Automation Ends: What Still Needs Expert Attention

Automation handles the broad surface area, but experts are still needed for:

  • SQL plan management (SPM) — when the optimizer regresses a stable plan after automatic statistics collection, use SQL baselines to pin the good plan.
  • Partition design — ADB does not redesign your table partitioning strategy. Incorrect partitioning bypasses Smart Scan and partition pruning regardless of automatic features.
  • Application design — connection pooling, transaction scope, row-chaining, and LOB management remain the application architect’s responsibility.
  • Data masking and security — Oracle Data Safe integrates with ADB but requires configuration.
  • Custom diagnostic queries — AWR and ASH are available in ADB (on dedicated infrastructure) and should be used for workload characterisation.

The self-driving label is accurate for the operational day-to-day. Architecture and workload design remain thoroughly human responsibilities.