Consolidating Three Applications onto One Autonomous Database
Northwind Retail is consolidating three Oracle 19c databases onto Autonomous Database to cut patching effort. The order-entry application handles 650 transactions per minute with a 10 ms response target and connects through a Java pool of 180 sessions. A merchandising dashboard runs 30 analytical queries every two hours; several scan 180 GB of sales history and currently run for 12 to 20 minutes. A product-content API stores 40 GB of JSON documents and serves 90 requests per second, mostly key lookups. The team wants one database, 16 ECPUs, with auto scaling enabled, and asks whether Autonomous Data Warehouse or Autonomous Transaction Processing is the right workload type, how to map the three applications to service levels, and what could go wrong. Month-end closing adds a 90-minute reporting burst while order entry continues.
1. Which Autonomous Database workload type should they choose, and why is the choice not settled by the 16 ECPU size alone?
Read the worked answer
Choose Autonomous Transaction Processing. The decisive facts are concurrency and mixed workload: 650 short transactions per minute plus 90 JSON requests per second need the OLTP-oriented service and resource management that ATP provides. Autonomous Data Warehouse is optimized for analytic workloads; using it for the order-entry and JSON paths would make latency and concurrency tuning harder, even though it can execute DML. ECPU size does not solve the mismatch because the workload type determines the default resource plan, services, and optimizer behavior. Within ATP, keep the applications on separate services and consumer groups: order entry on TP, possibly TPURGENT for a small protected subset; dashboard on MEDIUM; content API on LOW or TP after measuring latency. The 16 ECPU and auto scaling give headroom, but they do not isolate a 180 GB scan from short transactions. That isolation comes from service-level and consumer-group limits, not from raw capacity.
2. How should they map services and resource-manager limits, and what should they change for the month-end reporting burst?
Read the worked answer
Map each application to a distinct consumer group so that limits can be enforced and measured. Put order entry in TP with a guaranteed share and a cap that still leaves room for peaks. Put the dashboard in MEDIUM with a parallel degree limit and a CPU cap, and put the JSON API in LOW or TP depending on measured p99 latency; LOW's serial execution suits short key lookups but must be tested. For month-end, do not simply move reporting to HIGH and let it compete with order entry. Either keep the reporting job in MEDIUM with an explicit cap, or run it against a refreshable clone or a separate analytic database. Before the burst, confirm TEMP and undo headroom and capture a baseline from Performance Hub. After the burst, compare DB time by consumer group and run-queue waits. The judgment is that guaranteed shares protect the SLO while caps make the reporting job predictable; uncapped HIGH access would turn a capacity problem into a latency incident.
3. What evidence would justify splitting the workload onto a second Autonomous Database instead of keeping one consolidated instance?
Read the worked answer
Split when measurements show persistent interference that limits cannot fix within cost. Evidence would include order-entry p99 latency rising during dashboard runs even when the reporting consumer group is capped; run-queue or CPU-quantum waits in the order-entry group; TEMP or undo pressure caused by the 180 GB scans; and month-end bursts repeatedly exhausting the instance despite auto scaling. A second database is also justified if reporting needs a different maintenance or cloning lifecycle, or if a refreshable clone would let analysts run long queries without touching production. The counterargument is operational overhead and cost: a second instance doubles some administration and may duplicate storage. A reasonable decision path is to first enforce consumer-group caps and test a refreshable clone for reporting. If the SLO is still missed, move reporting to a separate Autonomous Data Warehouse or ATP instance and keep order entry plus the JSON API on the consolidated ATP.