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 18c, 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.

Services

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.

ADW_ATP_Comparison

4 thoughts on “How does Autonomous Transaction Processing differ from the Autonomous Data Warehouse?”

  1. Thank you. It’s very helpful.
    I have a question, looking forward to your reply.
    If users want to use ADW/ATP, are there any special things they need to pay attention to when designing their applications to maximize the efficiency of ADW/ATP usage?
    I know that users don’t need to focus on performance tuning, but whether the different design of table structures has different impact on performance?
    Or nothing special, just same as On-Premise DB design principles.
    Thanks in advance.

  2. I setup an Autonomous Database in Oracle Cloud with admin user. From there I go to “Tools” and open “SQL Developer Web”. SQL Worksheet came up. I created a simple table. >> Create table xx_test (id number, name varchar2(2000)); Inserted a row. >>insert into xx_test values (1, ‘The Test User’); I was able to query it. >> select * from xx_test; I fired rollback command. >> rollback Output came: Rollback complete. Elapsed: 00:00:00.001 When, I query the table again, I found rollback did not take place and record available in table. Even though I did not apply Commit; >> select * from xx_test; My quesiton is in Oracle Cloud Autonomous Database, commit and rollback has no role. Even without Commit a record permanently inserted in table and I can’t fire the rollback for that session. Kindly share your thoughts.

    1. Hi Pallab,

      Oracle SQL Dev Web is a stateless connection. After each command, it will auto-commit.

      If you want to be able to do an insert and then have the option to roll it back you would need to do it as part of a script. Since the entire script will be executed at once followed by the auto-commit.

      If you are looking for a stateful connection you will need to SQLCL or one of the other tools.

      Thanks,
      Maria

Leave a Reply

Your email address will not be published. Required fields are marked *