Meridian Retail: Choosing an Autonomous Database Shape for a Spiky Warehouse
Meridian Retail runs a 12 TB Oracle 12c data warehouse on-premises. Four hundred BI reports and 90 nightly ETL jobs run against it, and a two-person DBA team spends most of its week on patching, index rebuilds and tablespace alerts. Steady-state CPU is about 25% of peak. On the last three business days of each month, concurrent reporting sessions jump from roughly 40 to 300, and a handful of aggregation queries take three times longer than they do in normal weeks. Finance has frozen hardware purchases for 18 months and wants the warehouse on OCI with a predictable monthly bill. The team has no Exadata experience. A separate order-entry application, certified only on 19c with vendor PL/SQL packages, will stay on-premises for now. The CIO wants a recommendation covering workload type, deployment model, and how the month-end concurrency will be absorbed without doubling the run rate.
1. Which Autonomous Database workload type and deployment option should Meridian choose for the warehouse, and which details in the setup justify it?
Read the worked answer
Workload type: Autonomous Data Warehouse. The load is analytic - aggregations over a 12 TB star schema, 400 reports, nightly batch ETL. ADW's columnar formatting, result cache, materialized views with automatic query rewrite and parallel execution are the features the month-end queries need. Autonomous Transaction Processing would optimise for the wrong access pattern and would still require the same reporting tuning.
Deployment: serverless, for three reasons taken straight from the setup.
- Steady state at roughly 25% of peak with a three-day monthly spike is the textbook auto-scaling case. Dedicated Exadata infrastructure sized for month-end would idle for the other 27 days.
- A two-person team with no Exadata experience should not be running infrastructure; serverless removes patching, which is the team's largest current time sink.
- Consumption-based pricing fits the finance freeze and the demand for a predictable bill.
What would flip the decision: if the month-end peak exceeds the serverless ECPU ceiling, or the ETL turns out to need custom resource plans, dedicated infrastructure becomes the answer.
2. Auto-scaling alone will not serve 300 concurrent month-end sessions. What else should be configured, and in what order?
Read the worked answer
Auto-scaling raises compute up to three times the base ECPU count, but it does not increase the number of concurrent statements the database will admit. Work in this order:
- Raise the concurrency limit. ADW exposes a LOW/MEDIUM/HIGH concurrency setting. HIGH admits more concurrent statements by reducing per-statement parallelism. Three hundred mostly small report sessions is a HIGH-concurrency shape.
- Pre-aggregate. Materialized views with query rewrite let 300 sessions hit small summaries instead of scanning the fact tables.
- Separate the workloads with consumer groups. Put ETL in the MEDIUM or LOW group and interactive reporting in HIGH so the nightly load cannot starve month-end users.
- Move the ETL window. Use DBMS_SCHEDULER so the load no longer overlaps the close.
- Replay and measure. Capture the month-end SQL, replay it, and confirm the ECPU ceiling is not reached. If it is, the answer is dedicated infrastructure, not more tuning.
3. What should be validated before cutover, and what would disqualify the warehouse from Autonomous Database entirely?
Read the worked answer
Validation here is about compatibility, not performance. Run the Autonomous Database compatibility assessment against the 12c source and inspect the ETL code, because the blockers are almost always procedural:
- Operating-system coupling. Any ETL step that shells out, writes through UTL_FILE to a local directory, or calls a host script cannot run on a managed service.
- Privileges. SYSDBA/SYSOPER use, vendor packages that assume a full RDBMS install, and direct-path operations that need OS access.
- Parameters and hints. Unsupported initialisation parameters and legacy hints that assume a specific optimiser behaviour.
- Reachability and types. Database links to hosts unreachable over the private subnet, and long-deprecated data types.
- Sizing. Profile peak ECPU and storage growth from AWR, and confirm the largest table and longest month-end query fit the serverless limits.
If the first three cannot be refactored - which is exactly why the order-entry application is staying on-premises - the warehouse belongs on Exadata Database Service, not Autonomous.