ATP vs ADW – the Autonomous Database lockdown profiles

By Franck Pachot

.
The Oracle database has always distinguished two types of workloads: transactional (OLTP) and datawarehouse (VLDB, DWH, DSS, BI, analytics). There is the same idea in the managed Oracle Cloud with two autonomous database services.

To show how this is old, here is how they were defined in the Oracle7 Tuning Book:

The definition has not changed a lot. But the technology behind DSS/DWH has improved. Now, with In-Memory Column Store, Smart Scan, Result Cache we can even see that indexes, materialized views, star transformation, hints,.. are disabled in the Autonomous Datawarehouse cloud service.

The difference between the two autonomous cloud services, ATP (Autonomous Transaction Processing) for OLTP and ADW (Autonomous Datawarehouse) for analytics have been described by Maria Colgan after Larry Ellison announce:
https://sqlmaria.com/2018/08/07/how-does-autonomous-transaction-processing-differ-from-the-autonomous-data-warehouse/

PDBaaS

Those autonomous services are PDB as a Service. They are using the consolidation and isolation features of 12cR2 multitenant. And we can even see that the ATP and ADW services can run within the same CDB and same instance. They are different PDBs and they differ only by their resource management plans and PDB lockdown profiles. So let’s see the differences from this point of view: ATP lockdown profile is called ‘OLTP’ and ADW lockdown profile is called ‘DWCS’.

Options

The only difference is about partitioning which is enabled for ATP and disabled for ASW

SQL> select (select value from v$parameter where name='pdb_lockdown') type,v$lockdown_rules.* from v$lockdown_rules where rule_type='OPTION';
 
TYPE   RULE_TYPE   RULE           CLAUSE   CLAUSE_OPTION   STATUS   USERS   CON_ID
----   ---------   ----           ------   -------------   ------   -----   ------
OLTP   OPTION      PARTITIONING                            ENABLE   ALL          284
 
SQL> select (select value from v$parameter where name='pdb_lockdown') type,v$lockdown_rules.* from v$lockdown_rules where rule_type='OPTION' order by 1, 2 ,3 nulls first, 4 nulls first,5;
 
TYPE   RULE_TYPE   RULE           CLAUSE   CLAUSE_OPTION   STATUS    USERS   CON_ID   
----   ---------   ----           ------   -------------   -------   -----   ------
DWCS   OPTION      PARTITIONING                            DISABLE   ALL           73 

Features

All disabled features are the same:
Disabled for all users: COMMON_SCHEMA_ACCESS, CONNECTIONS, CTX_LOGGING, NETWORK_ACCESS, OS_ACCESS, SYSTEM_DATA
Disabled for local users only: USER_PASSWORD_VERIFIERS
Enabled for all users: COMMON_USER_CONNECT, TRACE_VIEW_ACCESS, UTL_FILE

Those last ones are good news. We can query V$DIAG_OPT_TRACE_RECORDS, V$DIAG_SQL_TRACE_RECORDS, $DIAG_TRACE_FILE_CONTENTS to show some diagnostics. Unfortunately, I’ve seen no allowed ways to enable SQL Trace.

DDL Statements

In the ADW service, the local user cannot create indexes and materialized views, They are supposed to be autonomously created when required (probably by a common user):

SQL> select (select value from v$parameter where name='pdb_lockdown') type,v$lockdown_rules.* from v$lockdown_rules where regexp_like(rule,'(INDEX|MATERIALIZED)') order by 1, 2 ,3 nulls first, 4 nulls first,5;
 
TYPE   RULE_TYPE   RULE                           CLAUSE   CLAUSE_OPTION   STATUS    USERS     CON_ID   
----   ---------   ----                           ------   -------------   ------    -----     ------ 
DWCS   STATEMENT   ALTER INDEX                                             DISABLE   LOCAL         73 
DWCS   STATEMENT   ALTER INDEXTYPE                                         DISABLE   LOCAL         73 
DWCS   STATEMENT   ALTER MATERIALIZED VIEW                                 DISABLE   LOCAL         73 
DWCS   STATEMENT   ALTER MATERIALIZED VIEW LOG                             DISABLE   LOCAL         73 
DWCS   STATEMENT   CREATE INDEX                                            DISABLE   LOCAL         73 
DWCS   STATEMENT   CREATE INDEXTYPE                                        DISABLE   LOCAL         73 
DWCS   STATEMENT   CREATE MATERIALIZED VIEW                                DISABLE   LOCAL         73 
DWCS   STATEMENT   CREATE MATERIALIZED VIEW LOG                            DISABLE   LOCAL         73 
DWCS   STATEMENT   DROP INDEX                                              DISABLE   LOCAL         73 
DWCS   STATEMENT   DROP INDEXTYPE                                          DISABLE   LOCAL         73 
DWCS   STATEMENT   DROP MATERIALIZED VIEW                                  DISABLE   LOCAL         73 
DWCS   STATEMENT   DROP MATERIALIZED VIEW LOG                              DISABLE   LOCAL         73 

The ATP service enables all those for all users.

System parameters

Both services disable all ALTER SYSTEM statements except KILL SESSION. They allow all ALTER SYSTEM SET for the common users but allow only a few parameters to be changed by local users.

Basically, the ADW service sets the following:

_default_pct_free=1
_ldr_io_size=33554432
_ldr_io_size2=33554432
_max_io_size=33554432
_optimizer_allow_all_access_paths=FALSE
_optimizer_answering_query_using_stats=TRUE
optimizer_ignore_hints=TRUE
optimizer_ignore_parallel_hints=TRUE
parallel_degree_policy=AUTO
parallel_min_degree=CPU
result_cache_max_result=1
result_cache_mode=FORCE

The ATP service keeps the defaults.

Basically, all the features for OLTP are there for years in the Oracle Database. Under the ‘autonomous’ umbrella we can see some well-known features

• Automatic provisioning: pluggable databases
• Automatic scaling: PDB resource manager
• Automatic tuning: SQL Plan Management, Adaptive Plans, SQL Tuning Advisor, Storage Indexes
• Automatic security: Rolling patches, Encryption, Database Vault
• Automatic Fault Tolerant Failover: RAC, Data Guard
• Automatic Backup and Recovery: RMAN, Flashback

All these proven features are enhanced to work together in a consolidated multitenant environment. Povisioning is as fast as a CREATE PDB. The ATP and ADW services enable and configure the right set of features for each workload.