Month-End Batch Slowdown on Autonomous Database Serverless
A retail customer runs its order-management suite on an Autonomous Database Serverless instance in the Frankfurt region, provisioned with 8 ECPUs and auto scaling enabled. Every month-end the finance team runs a single-threaded PL/SQL aggregation job that historically finished in 25 minutes. Last close it ran for 74 minutes, and the APM trace shows the session never using more than one CPU core. During the same window the web tier logged 41 ORA-00018 (maximum number of sessions exceeded) errors, and the database's CPU utilization graph stayed flat at roughly 12 percent of the 8-ECPU baseline. The application connects through a shared connection pool configured with 400 sessions that never time out and is not restarted between deployments. The customer also wants a disaster-recovery posture of RPO under 5 minutes and RTO under 30 minutes for a regional outage, and today has no standby. Budget approval for a second region is already in place.
1. Explain why auto scaling prevented neither the slow batch job nor the ORA-00018 errors, and state what you would change.
Read the worked answer
Auto scaling is a compute response, not a session-limit response, and two different problems are being conflated here.
The batch job. The 12 percent CPU figure is the tell. Auto scaling reacts to sustained CPU pressure on the instance as a whole and adds headroom up to about three times the base ECPU count. The instance never became CPU-bound because the job is single-threaded: one serial session can use one core, and one of eight cores is roughly 12.5 percent, which matches the graph exactly. There was nothing for auto scaling to react to, and adding ECPU would not make a serial job finish sooner.
The ORA-00018. ADB Serverless derives its SESSIONS and PROCESSES parameters and its concurrency limits from the provisioned base ECPU count. Auto scaling adds CPU and memory headroom but is not a mechanism for raising those limits. A 400-session pool that never expires sits permanently against a ceiling sized for 8 ECPUs, so any burst tips it over.
Changes: cap the pool to a realistic size (typically well under 200 for this workload), enable connection timeouts and pool recycling on deploy, and only if genuinely more concurrent sessions are required, raise the base ECPU count. Then make the batch job parallel rather than buying compute for it.
2. Design the disaster-recovery posture that meets RPO under 5 minutes and RTO under 30 minutes, and note any assumption the customer is likely to get wrong.
Read the worked answer
Enable Autonomous Data Guard with a standby in a second region, for example primary in Frankfurt and standby in Amsterdam. Replication is asynchronous but typically lags by seconds, which comfortably satisfies a 5-minute RPO as long as the transaction volume does not spike beyond the standby's apply capacity.
The RTO is where most designs fail. Failover is a manual operation initiated through the console or API, so 30 minutes is really a statement about the runbook and the people, not about the feature. Preconditions you must verify in advance: the application connection strings and wallets can reach the standby region, DNS or the load-balancer target can be switched, DBAs have the required IAM permissions in the standby's compartment, and the switchover has been rehearsed end to end and timed. A single untested failover will consume the entire 30 minutes.
The assumption to correct: the ADB standby cannot be opened read-only for reporting. If the customer is planning to offload analytics to the standby to justify the cost, that is not available, and the reporting tier needs its own clone or a refreshable clone.
3. The customer refuses to raise the base ECPU count. How would you get the month-end job back under 25 minutes?
Read the worked answer
Stop running it serially. There are four levers, and you should investigate in this order:
- Find out what the session is waiting on. A job at 12 percent CPU is usually waiting, not computing. Query ASH or the session's wait events during the run. If the top wait is a full scan, a missing index join, or a log sync per row, parallelism will just multiply the wait.
- Parallelize deliberately. Enable parallel DML and query with a degree derived from the 8 ECPUs, or use DBMS_PARALLEL_EXECUTE to chunk the aggregation by key range. This is the only way to consume the ECPU headroom that auto scaling or a larger base would otherwise buy.
- Reduce the work. Check whether the aggregation can be incremental: a materialized view refreshed on change, or a rolling summary table that only processes the new partition. A 74-minute full recompute for a monthly close is often avoidable.
- Tune the SQL. Use ADB's SQL Tuning Advisor and automatic indexing, and re-gather statistics on the base tables before the run.
Validate in a clone of the instance, not in production, and confirm the improvement against the 25-minute target before the next close.