Oracle ATP: MEDIUM and HIGH services are not for OLTP

The Autonomous Transaction Processing services HIGH and MEDIUM are forcing Parallel DML, which can lock the tables in eXclusive mode.

This may seem obvious that the TP and TPURGENT are for OLTP. But when you know that the service names are associated with Resource Manager consumer groups, you may think that high priority use cases should run on the HIGH service. However those LOW, MEDIUM, HIGH services were probably named when ADW was the only Autonomous Database and it is not directly obvious that they are there for reporting only, or maybe for some batch operations.

Application “enq: TM — contention” and Scheduler “resmgr:pq queued” waits

What is less obvious is that when you do some modifications through these services, they run with Parallel DML (PDML). And PDML can acquire an exclusive lock on tables. And then it blocks your concurrent transactions. You will see some waits on ‘enq: TM contention’ and probably some ‘resmgr:pq queued’

What does the documentation say about parallelism?

The documentation or the Autonomous Database FAQ explains that MEDIUM and HIGH use parallel query, but it is not immediately visible that they also run in parallel DML:

https://www.oracle.com/technetwork/database/bi-datawarehousing/adw-technical-faq-public-5069016.pdf

You may also refer to Maria Colgan description of those services:

Getting started with Oracle Autonomous Transaction Processing

How to enable Parallel DML?

As far as I know, the documentation mentions only two ways to enable Parallel DML. At the session level:

alter session enable parallel DML;

to enable it (when the table degree is >1) or even to force it even for no parallel tables:

alter session force parallel DML;

Or, since 12c, at statement level with the ENABLE_PARALLEL_DML hint:

Parallel DML in 12c - Blog dbi services

It is important that Oracle requires us to explicitly enable PDML because PDML queries can acquire exclusive locks on the tables, and DML is not expected to do that — exclusive lock is usually for DDL to prevent concurrent DML.

Let’s also precise here that there’s a big difference between Parallel Query and Parallel DML. Parallel Query is for SELECT. it can consume a lot of resources, but will never lock tables. Parallel DML concerns only modifications (like INSERT, DELETE, UPDATE). This is a bit misleading because, in SQL, the Data Manipulation Language also includes the SELECT. But it is common that people use the DML term for non-SELECT DML.

What does the dictionary say about parallelism?

V$SESSION has information about the sessions that have Parallel DML enabled:

select distinct regexp_replace(service_name,'.*_')
 ,pdml_enabled, pdml_status, pddl_status, pq_status 
FROM V$session order by 2,3,4;

That tells us that Parallel DML is enabled, for all services. But not forced. When you ALTER SESSION FORCE PARALLEL DML, you see it explicitly:

SQL> connect demo/demo@//localhost/PDB1
Connected.

SQL> select distinct regexp_replace(service_name,'.*_') "service"
    ,pdml_enabled, pdml_status, pddl_status
   FROM V$session where sid=sys_context('userenv','sid');
   service    PDML_ENABLED    PDML_STATUS    PDDL_STATUS
__________ _______________ ______________ ______________
pdb1       NO              DISABLED       ENABLED
SQL> alter session enable parallel dml;
Session altered.
SQL> select distinct regexp_replace(service_name,'.*_') "service"
    ,pdml_enabled, pdml_status, pddl_status
   FROM V$session where sid=sys_context('userenv','sid');
   service    PDML_ENABLED    PDML_STATUS    PDDL_STATUS
__________ _______________ ______________ ______________
pdb1       YES             ENABLED        ENABLED
SQL> alter session force parallel dml;
Session altered.
SQL> select distinct regexp_replace(service_name,'.*_') "service"
    ,pdml_enabled, pdml_status, pddl_status
   FROM V$session where sid=sys_context('userenv','sid');
   service    PDML_ENABLED    PDML_STATUS    PDDL_STATUS
__________ _______________ ______________ ______________
pdb1       YES             FORCED         ENABLED
SQL> alter session disable parallel dml;
Session altered.
SQL> select distinct regexp_replace(service_name,'.*_') "service"
    ,pdml_enabled, pdml_status, pddl_status
   FROM V$session where sid=sys_context('userenv','sid');
   service    PDML_ENABLED    PDML_STATUS    PDDL_STATUS
__________ _______________ ______________ ______________
pdb1       NO              DISABLED       ENABLED

So, in my ATP service, all services have PDML enabled, and none are forced.

What does the execution plan say about parallelism?

However, it seems that when connected with the MEDIUM and HIGH services, PDML can be used:

And you can see that I even created the table explicitly in NO PARALLEL (which is the default anyway). The PDML is enabled but not forced. Then what I often do in this case is look at the ‘+OUTLINE’ dbms_xplan format as there may be some clues about specific optimizer settings:

SQL> select * from dbms_xplan.display(format=>'+outline');
...
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"DEL$1" "DEMO"@"DEL$1")
      OUTLINE_LEAF(@"DEL$1")
      SHARED(8)
      ALL_ROWS
      OPT_PARAM('_fix_control' '20648883:0')
      DB_VERSION('18.1.0')
      OPTIMIZER_FEATURES_ENABLE('18.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */
 
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 8 because of degree limit

ATP service disables the fix about “Lift restrictions on view merging for the CURSOR expression” but this has nothing to do with PDML. However, there’s something interesting here: a SHARED hint.

What does V$SQL_HINT say about parallelism?

Actually, the true hint for running in parallel is not PARALLEL but SHARED. This is visible in V$SQL_HINT as it is the inverse of NOPARALLEL (and its synonym NO_PARALLEL added later to follow the NO_% hint syntax convention):

SQL> select name,inverse,version from v$sql_hint 
     where name like '%SHARED';
     NAME        INVERSE    VERSION
_________ ______________ __________
SHARED    NO_PARALLEL    8.1.0
SQL> select name,inverse,version from v$sql_hint
     where name like '%PARALLEL';
          NAME    INVERSE     VERSION
______________ __________ ___________
NOPARALLEL     SHARED     8.1.0
NO_PARALLEL    SHARED     10.1.0.3

PARALLEL is just a synonym for SHARED.

So, it seems that even with no parallel degree and no forced parallel DML, parallelism has been forced to a degree of 8 as we can see with the SHARED(8) hint. Probably related to the number of CPU:

ATP number of OCPU

SQL> show parameter cpu_count
NAME      TYPE    VALUE 
--------- ------- ----- 
cpu_count integer 20    
SQL> show parameter degree_limit
NAME                 VALUE
--------------------- ----
parallel_degree_limit CPU

I have 10 OCPUs allocated to this ATP service, which means 20 threads (CPU_COUNT) but DBMS_XPLAN also mentions that Auto DOP has computed the degree because of degree limit. The Resource Manager plan has some limits for the maximum degree of parallelism, like:

SQL> select plan,group_or_subplan,parallel_degree_limit_p1
     from DBA_RSRC_PLAN_DIRECTIVES where plan='OLTP_PLAN';
PLAN         GROUP_OR_SUBPLAN       PARALLEL_DEGREE_LIMIT_P1
---------    ----------------       ------------------------OLTP_PLAN    HIGH                                         20 
OLTP_PLAN    MEDIUM                                        4 
OLTP_PLAN    LOW                                           1 
OLTP_PLAN    TPURGENT                                        
OLTP_PLAN    OTHER_GROUPS                                  1 
OLTP_PLAN    TP                                            1

What does the LOGON Trigger say about parallelism?

Usually, when we want to enable Parallel DML automatically for a user we add a Logon Trigger, like:

CREATE OR REPLACE TRIGGER app_user.after_logon_trg
AFTER LOGON ON app_user.SCHEMA
BEGIN
  EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
END;
/

Note that this AFTER LOGON ON SCHEMA comes from Tim Hall example:

Database Triggers Overview

So, let’s have a look at the LOGON triggers defined in the Autonomous Transaction Processing PDB (Don’t forget that for whatever reason the TRIGGERING_EVENT in DBA_TRIGGERS ends with a space):

set echo on long 10000
select dbms_metadata.get_ddl('TRIGGER',trigger_name,owner) 
from dba_triggers where triggering_event like 'LOGON ';

No mention of ENABLE or FORCE parallel DML here, but there is a minimum parallel degree set to the number of CPU, with the DOP policy set to AUTO, for the MEDIUM and HIGH services only.

I have all information here, let’s reproduce the same in a non-autonomous database. And show the reason of the sessions blocked on “enq: TM contention”

What does ksqgtl trace say about parallelism?

I’m connected to a 19.3 database here where I enable PDML (not forced) and I also trace the Kernel Service enQueue (like what we did with 10704 before 12cR2) in order to show the locks acquired.

The Auto DOP is set with a minimum degree equal to CPU, as in the ATP logon trigger.

I have created a simple DEMO table with DEGREE 1 (which means no parallel, and is the default anyway). I run a DELETE and explain plan:

This is what I observed in ATP but here, with no resource manager, the parallel degree is set to 48 which is my CPU_COUNT.

Now looking at the ksq trace for ksqgtl (GeT Lock):

My delete, because it is running as PDML, has acquired a mode=6 (eXclusive) lock on the DEMO table.

If you want to reproduce, here are the commands I’ve run:

set pagesize 1000 echo on
drop table DEMO;
connect demo/demo@//localhost/PDB1
create table DEMO (n) parallel 1 as select rownum from xmltable('1 to 1000');
alter session enable parallel dml;
alter session set parallel_degree_policy=auto parallel_min_degree=cpu;
select distinct regexp_replace(service_name,'.*_') "service"
 ,pdml_enabled, pdml_status, pddl_status, pq_status
from v$session where sid=sys_context('userenv','sid')
/
alter session set events 'trace[ksq] disk medium';
alter session set tracefile_identifier=KSQ;
delete DEMO;
select * from dbms_xplan.display_cursor(format=>'+outline');
alter session set events 'trace[ksq]off';
select object_id , to_char(object_id,'0XXXXXXX') , object_name,object_type from user_objects where object_name='DEMO';
column value new_value tracefile
select value from v$diag_info where name='Default Trace File';
column value clear
host grep TM- &tracefile
rollback;

One question remains…

In my example, I’ve enabled PDML:

alter session enable parallel dml;

because I’ve seen it enabled for my MEDIUM and HIGH services. But I don’t know (yet — please tweet @FranckPachot if you have ideas) where this comes from. There’s nothing about it in the LOGON trigger.

If I do not enable PDML in my non-autonomous database, the SELECT part is done in the parallel query but the DELETE is not in PDML (there is no DELETE below the PX COORDINATOR)

So, the question that remains: how is PDML enabled in the Autonomous Transaction Processing service?

But the most important to remember is that running some INSERT/UPDATE/DELETE with the MEDIUM of HIGH service can block all your Transaction Processing because of the exclusive lock.

Autonomous does not mean that we can run without understanding.