Autonomous Transaction Processing – your slice of the pie

I grabbed the following screen shot from a slide deck I’ve been giving about Autonomous Transaction Processing (ATP). It shows the services that are made available to you once you create your database. At first glance, it looks like we have a simple tier, where the lower in the tier you are, the smaller the slice of the database resource “pie” you get.

And this is true in terms of the share of resources that are assigned to each service. But it is also important to note the 3rd column, namely, the parallelism capacities that are assigned. This is the level of parallel capabilities that are active on that service. For example, here’s a simple little SQL demo that creates a table, and performs a couple of set-based deletes on that table. I’ll start by connecting to the LOW service.

SQL> set echo on
SQL> connect xxx/xxx@myservice_low
Connected.
SQL> drop table t purge;

Table T dropped.

SQL> create table t as select * from all_objects;

Table T created.

SQL> delete from t where rownum <= 1000;

1,000 rows deleted.

SQL> delete from t where rownum <= 1000;

1,000 rows deleted.

I might think to myself “Hmmm…I want to make sure that these deletes get a larger slice of the pie, so I’ll use the MEDIUM service instead”. Let’s see what happens when I do that.

SQL> connect xxx/xxx@myservice_medium
Connected.
SQL> drop table t purge;

Table T dropped.

SQL> create table t as select * from all_objects;

Table T created.

SQL> delete from t where rownum <= 1000;

1,000 rows deleted.

SQL> delete from t where rownum <= 1000;

Error starting at line : 5 in command -
delete from t where rownum <= 1000
Error report -
ORA-12838: cannot read/modify an object after modifying it in parallel

My script now crashes . So its important to be aware of the parallelism facilities defined for MEDIUM and HIGH. We’ll do operations in parallel whenever possible. You get faster queries and faster DML but there are implications on the way you write your scripts, the locking that will result and how commit processing must be handled. HIGH and MEDIUM are not just “bigger” versions of the LOW service.

This is all outlined in the docs:

Predefined Database Service Names for Autonomous Transaction

The predefined service names provide different levels of performance and concurrency for Autonomous Transaction Processing.

• tpurgent: The highest priority application connection service for time critical transaction processing operations. This connection service supports manual parallelism.
• tp: A typical application connection service for transaction processing operations. This connection service does not run with parallelism.
• high: A high priority application connection service for reporting and batch operations. All operations run in parallel and are subject to queuing.
• medium: A typical application connection service for reporting and batch operations. All operations run in parallel and are subject to queuing. Using this service the degree of parallelism is limited to four (4).
• low: A lowest priority application connection service for reporting or batch processing operations. This connection service does not run with parallelism.

TL;DR: For standard transactional activities on ATP, make sure it uses the TP or TPURGENT services. If you need faster performance for volume operations, then HIGH and MEDIUM are your friend, but understand the locking and commit implications.