In my previous post, I explained that Oracle Autonomous Transaction Processing has three main attribute: 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 18c, they have been optimized differently to support two very different but complimentary 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.
The differences in the two services begins 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.
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.
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.
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.
Both ADW and ATP offer multiple database “services” to make it easy for users to control the priority and parallelism used by each session. The services predefine three priority levels: Low, Medium, and High. Users can just choose the best priority for each aspect of their workload. 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 elapse time or IO will be automatically terminated.
On ADW only one service (LOW) automatically runs SQL statements serially. While on ATP only one service (PARALLEL) automatically runs SQL statements with parallel execution. On ATP you can use the Medium priority service by default which allows the Low priority service to be uses for requests such as reporting and batch to prevent them from interfering with mainstream transaction processing. The High priority level can be used for more important users or actions.