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.
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.