Oracle Autonomous Database, in depth: workload types, ECPU scaling, service names, networking, loading data and what is still your job

Deployment and workload choices

There are two deployment models. Serverless is the default. You get a database in Oracle-managed Exadata infrastructure shared with other tenants, and you size it in ECPUs and storage. Dedicated Exadata Infrastructure gives you your own Exadata system, on which you create container databases and many autonomous databases, with more control over maintenance windows and isolation. Serverless suits most teams. Choose dedicated for regulatory isolation, many databases with pooled capacity, or control of the maintenance schedule. The rest of this article assumes serverless unless it says otherwise.

The workload type sets defaults for memory, parallelism, optimizer statistics and storage formats. Transaction Processing (db_workload = "OLTP") suits mixed OLTP. Lakehouse, formerly Data Warehouse, uses the Terraform values DW and LH and suits analytics, with storage sized in terabytes. JSON (AJD) suits document workloads and requires license-included pricing. APEX suits low-code applications. All of them run the same engine, so the choice is about defaults and pricing, not features. Pick the type that matches the dominant workload and use service names to handle the rest.

Two special shapes are useful for non-production work. A Developer instance is fixed at 4 ECPUs and 20 GB of storage for development and testing. Always Free instances can only be created in the tenancy's home region. Neither is a production shape.

The ECPU compute and storage model

Compute is measured in ECPUs, with a minimum of 2. OCPU is the legacy model, and new databases should use ECPU. The number you provision is the base. With compute auto scaling, which is enabled by default, the database may use up to three times the base ECPUs when the workload needs it, and you are billed for that extra use. Storage auto scaling is off by default. When enabled, it lets storage grow to up to three times the provisioned amount. Transaction Processing and JSON storage can be specified in GB or TB, and Lakehouse storage in TB.

Worked example. Suppose an order system runs on 4 base ECPUs for a 720-hour month, which is 2,880 ECPU-hours. Every day there is a two-hour batch window in which auto scaling takes it to the full 12 ECPUs, 8 above base. That adds 8 x 2 x 30 = 480 ECPU-hours, for 3,360 in total, a 16.7 percent increase over base. Without auto scaling you would provision 12 ECPUs all month to survive the batch, which is 8,640 ECPU-hours, or 2.6 times as much. Auto scaling suits spiky workloads. With a flat, high load, raising the base is cleaner, because a database that runs at 3x all day is paying for extra capacity it always uses. Look up current ECPU and storage prices for your region rather than relying on remembered figures.

You can also stop the database. While stopped, you pay for storage but not compute, which makes stop/start schedules worthwhile for development and test databases. Scaling the base ECPU count is an online operation, so there is no need to schedule downtime to resize.

Service names: how a connect string sets priority

Every serverless database exposes five predefined services, dbname_tpurgent, _tp, _high, _medium and _low. Each maps to a Database Resource Manager consumer group, so the service you connect to decides your priority, whether your SQL runs in parallel and how many statements may run at once.

The most common performance complaint about Autonomous Database is really a service-name mistake. An OLTP application pointed at _high gets parallel query and a concurrency limit of three statements, so the fourth request queues. A dashboard pointed at _low runs serially at the lowest priority and looks slow. Give each application its own database user and the right service, and when in doubt use _tp for request-response traffic and _medium or _high for reporting.

Networking and TLS

There are three network access options: secure access from everywhere, access restricted to allowed IPs and VCNs through an access control list, and private endpoint access only. A private endpoint gives the database a private IP and hostname in your subnet and blocks all public access. For production, use a private endpoint in a dedicated subnet with a network security group that admits only the application tier. The subnet design follows normal OCI networking practice.

Connections are always encrypted. With mutual TLS, clients need the downloadable wallet containing client certificates. With plain TLS, clients use a normal connect string and the server certificate is verified, with no wallet to distribute. Since July 2023 the Terraform default for is_mtls_connection_required is false. Oracle only allows TLS without mTLS once an ACL or private endpoint restricts who can connect. Plain TLS makes connection pools in containers much simpler, and the network restriction is the real control anyway.

import os
import oracledb   # thin mode: no Oracle Client install needed

# Connect string copied from the console's "TLS" connection strings for the _tp service.
# It already carries retry_count / retry_delay; keep them for failover and maintenance.
DSN = os.environ["ORDERS_TP_DSN"]

pool = oracledb.create_pool(user="app_rw", password=os.environ["APP_PW"], dsn=DSN,
                            min=2, max=20, increment=2)

def place_order(customer_id, amount):
    with pool.acquire() as conn, conn.cursor() as cur:
        cur.execute("insert into orders (customer_id, amount) values (:1, :2)",
                    [customer_id, amount])
        conn.commit()

Use a connection pool. Autonomous Database limits sessions per ECPU, maintenance and failover drop connections, and a pool with the retry settings from the console's connect strings recovers without application changes.

Provisioning with Terraform

Clicking through the console is fine for a first database, but production databases should be code. These attributes come from the OCI provider's oci_database_autonomous_database resource:

resource "oci_database_autonomous_database" "orders" {
  compartment_id              = var.compartment_id
  db_name                     = "ORDERS"            # unique in tenancy, starts with a letter
  display_name                = "orders-prod"
  db_workload                 = "OLTP"              # OLTP, DW, AJD, APEX or LH
  db_version                  = "26ai"              # check availability in your region
  compute_model               = "ECPU"
  compute_count               = 4                   # base ECPUs, minimum 2
  is_auto_scaling_enabled     = true                # may bill up to 3x base when busy
  data_storage_size_in_tbs    = 1
  is_auto_scaling_for_storage_enabled = false       # opt-in; can grow to 3x
  license_model               = "LICENSE_INCLUDED"  # or BRING_YOUR_OWN_LICENSE
  admin_password              = var.admin_password  # 12-30 chars, upper, lower, digit

  # Private endpoint: traffic only from this VCN, public access blocked.
  subnet_id                   = var.db_subnet_id
  nsg_ids                     = [oci_core_network_security_group.db.id]
  is_mtls_connection_required = false               # TLS without a wallet is allowed here
}

A few details matter. db_name must be unique in the tenancy and cannot be changed later, so include the environment in it. Keep the admin password in a vault-backed variable, never in state files you share. The ADMIN account is for administration only, so create application users with minimal grants straight after provisioning. data_storage_size_in_gb and data_storage_size_in_tbs are mutually exclusive; this example uses terabytes.

Loading data, auto indexing and runaway queries

Autonomous Database reads directly from Object Storage through the DBMS_CLOUD package. The cleanest authentication uses the database's resource principal, so there are no API keys in the database, together with an IAM policy that grants the database's dynamic group read access to the bucket. Stage files in OCI Object Storage and load them with one call:

-- 1. Load a file from Object Storage using the database's resource principal.
EXEC DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL();

BEGIN
  DBMS_CLOUD.COPY_DATA(
    table_name      => 'ORDERS_STAGE',
    credential_name => 'OCI$RESOURCE_PRINCIPAL',
    file_uri_list   => 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/mytenancy/b/landing/o/orders_2026_09.csv',
    format          => JSON_OBJECT('type' VALUE 'csv', 'skipheaders' VALUE '1'));
END;
/

-- 2. Let the database create and validate indexes (off unless you enable it).
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE', 'IMPLEMENT');

-- 3. Stop runaway reports: cancel HIGH-service statements after 120 s or 1,000 MB of IO.
BEGIN
  CS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(
    consumer_group     => 'HIGH',
    io_megabytes_limit => 1000,
    elapsed_time_limit => 120);
END;
/

Automatic indexing is off by default. When set to IMPLEMENT, the database watches the workload, creates candidate indexes as invisible, tests them against real statements and makes visible only those that help. REPORT ONLY mode is a safe way to start, because you can read its recommendations before letting it act. It does not replace deliberate index design for primary access paths.

Runaway-query limits are set per consumer group with CS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE. Statements exceeding the elapsed-time or IO limit are cancelled, and setting the values to null lifts the limits. Put limits on HIGH and MEDIUM so an ad-hoc query cannot take over the database, and leave TP alone.

What Oracle runs and what stays yours

Backups and disaster recovery

Automatic backups are on for every serverless database. On the ECPU model, retention can be set anywhere from 1 to 60 days. Point-in-time restore and cloning from a backup cover most mistakes, such as a bad deployment or a dropped table. A clone also makes a realistic copy for testing without touching production. Rehearse a restore at least once per quarter and time it, because your recovery time is what you have measured, not what you assume.

For availability beyond one database, Autonomous Data Guard adds a standby, either local in another availability domain or cross-region. Enabling a standby adds cost, and applications only benefit if their connection strings and pools tolerate the switchover. Test a switchover in staging with real traffic before you rely on it.

Failure modes seen in practice

• Wrong service name. OLTP on _high queues behind the three-statement limit, and reports on _low crawl. Check SYS_CONTEXT('USERENV','SERVICE_NAME') from the application.
• Session exhaustion. Sessions are bounded per base ECPU. Hundreds of unpooled serverless functions opening connections hit the limit well before CPU. Use pools, or a connection broker in front of the database.
• Surprise bills. Auto scaling quietly runs at 3x for weeks because of one inefficient batch query. Alert on ECPU usage above base in OCI Monitoring and set a budget alarm.
• Wallet drift. With mTLS, rotated or re-downloaded wallets differ across application instances. Move to TLS with a private endpoint, or distribute the wallet from one secret store.
• Stopped by a schedule and forgotten. A stopped database refuses connections, so health checks fail with network-like errors. Record stop schedules with the database's tags.
• Assuming full DBA control. Some parameters, operating-system access and features are restricted. Check the documented restrictions before migrating a database that relies on them.

Trade-offs against the alternatives

Compared with running Oracle yourself or with Base Database Service, Autonomous removes most operations work, including patching, backup configuration, capacity changes and much of the tuning, and it scales online. In return you accept restrictions on privileges and parameters, a pricing model where elastic capacity is convenient but must be watched, and less control over maintenance timing on serverless. Compared with other managed databases, the case for it is strongest when you already have Oracle applications, PL/SQL or Oracle-specific features. For a greenfield application with no Oracle dependency, weigh it against the options in cloud-native databases on price and portability, not only on features.

What to do next

1. Pick serverless unless you need isolation or control of maintenance, and choose the workload type by the dominant workload.
2. Provision with Terraform, using ECPU, a private endpoint, an NSG, TLS without a wallet and license model set explicitly.
3. Create one database user per application and map each one to the right service: _tp for requests, _medium/_high for reports.
4. Set runaway-query limits on HIGH and MEDIUM, and enable auto indexing in REPORT ONLY mode first.
5. Alert on ECPU usage above base and set a budget, then review auto scaling after two weeks of real traffic.
6. Set backup retention, rehearse a point-in-time restore and a clone, and decide whether you need Autonomous Data Guard.