How does Autonomous Transaction Processing differ from the Autonomous Data Warehouse?

In my previous post, I explained that  Oracle Autonomous Transaction Processing has three main attributes: Self-Driving, Self-Securing and Self-Repairing. All of the functionality I described in that post is shared between both the Autonomous Data Warehouse (ADW) and ATP.

Where the two services differ is actually inside the database itself. Although both services use Oracle Database 19c, they have been optimized differently to support two very different but complementary workloads. The primary goal of ADW is to achieve fast complex analytics, while ATP has been designed to efficiently execute a high volume of simple transactions.

Configuration

The differences in the two services begin with how we configure them.In ADW the majority of the memory is allocated to the PGA to allow parallel joins and complex aggregations to occur in memory, rather than spilling to disk. While on ATP the majority of the memory is allocated to the SGA to ensure the critical working set can be cached to avoid IO.

Data Formats

We also store the data differently in each service. In ADW, data is stored in a columnar format as that’s the best format for analytics processing. While in ATP data is stored in a row format. The row format is ideal for transaction processing, as it allows quick access and updates to all of the columns in an individual record since all of the data for a given record stored together in-memory and on-storage.

Statistics Gathering

Regardless of which type of autonomous database service you use, optimizer statistics will be automatically maintained. On ADW statistics (including histograms) are automatically maintained as part of all bulk load activities. With ATP, data is added using more traditional insert statements, so statistics are automatically gathered when the volume of data changes significantly enough to make a difference to the statistics.

Query Optimization

Queries executed on ADW are automatically parallelized, as they tend to access large volumes of data in order to answer the business question. While indexes are used on ATP to access only the specific rows of interest. We also use RDMA on ATP to provides low response time direct access to data stored in memory on other servers in the cluster to avoid IO.

Resource Management

Both ADW and ATP offer multiple database “services” to make it easy for users to control the priority, parallelism and concurrency used by each session.

On ADW the services predefine three priority levels: Low, Medium, and High.   Users just choose the best priority for each aspect of their workload.  On ADW two services automatically runs SQL statements with parallel execution (HIGH and MEDIUM) and only one service (LOW) automatically runs SQL statements serially. 

While on ATP there are 5 predefined services. The same 3 services seen on ADW in order to accommodate real-time analytics and two additional services (TP and TPURGENT).  On ATP you can use the TP service by default for transaction processing workloads, ensuring it won’t be impacted by batch processing or reporting running on the HIGH, MEDIUM and LOW services.  TPURGENT, the highest priority level can be used for more important users or actions.

For each database service, you have the ability to define the criteria of a runaway SQL statement. Any SQL statement that excesses these parameters either in terms of elapsed time or IO will be automatically terminated.