Classification of ADB based on workload

With the optimization and integration of hardware and software capabilities available across the stack in database machines, along with Oracle Enterprise edition databases, Oracle offers three distinct flavors of ADB for running your workload: ATP, ADW, and AJD.

Oracle ADW is designed to run data warehousing, data marts, data lakes, analytics, and ML workloads. Oracle ATP is designed for online transaction processing, batch, reporting, the Internet of Things (IoT), application development, ML, and mixed workload environments.

In the following sections, we will discuss each flavor in detail.

ADW

This is the first offering available with OCI in the ADB service portfolio. As the name indicates, Oracle ADW is designed for data warehouses and related workloads, including data marts, data lakes, and ML workloads. Most organizations architect analytical workloads to run on a separate system other than their OLTP systems, as the requirements for these systems are different and widely used for decision-making and data analytics business use cases. Data warehouses are characterized by star schemas and snowflake schemas and normally have very high data ingestion rates. As part of the data warehousing requirements, facts are often derived from several dimensions, and keeping aggregated data is often considered as summary tables for data analysis. This system demands a high level of parallelism for running SQLs as well as a faster response time to serve business users. Oracle ADW is specifically designed to provide faster response times to queries and desired level of parallel processing for data ingestion.

Quick note

ADW optimizes complex SQL. It uses the columnar format and creates data summaries. Optimizer and PARALLEL hints are ignored in ADW. Users can override this behavior by changing two parameters, optimizer_ignore_hints and optimizer_ignore_parallel_hints, to FALSE, which, by default, are usually set to TRUE.

Optimizer statistics: Stats are gathered automatically for direct load operations. If your workload uses conventional DML in ADW, gather stats manually with the GATHER AUTO option. For example, see the following:

BEGIN

DBMS_STATS.GATHER_SCHEMA_STATS('SH', options=>'GATHER AUTO');

END;

/

ATP

Oracle ATP is designed for online transaction processing and workloads that are not data warehousing-related. ATP is primarily suited for mission-critical transactional workloads that often include operational reporting or batch data processing. With ATP, you can run mixed workloads in a single database, which eliminates the need to segregate transactional data from analytics data. Users can run their mixed workload in the same system without worrying about any potential data management options. ATP also supports the IoT and ML, in addition to OLTP workloads. ATP makes application development much simpler, as there is no need for traditional data management skills for someone to get started with these services.

Quick note

ATP optimizes the response time for SQLs. Data is stored in a ‘ROW’ format and creates indexes as required automatically. Optimizer and PARALLEL hints are honored in ATP and are set to TRUE by default. Users can override this behavior by changing two parameters, optimizer_ignore_hints and optimizer_ignore_parallel_hints, to TRUE, which are set to FALSE by default. This will disable both the default behavior and the setting.

ALTER SESSION SET OPTIMIZER_IGNORE_HINTS=TRUE; 

ALTER SESSION SET OPTIMIZER_IGNORE_PARALLEL_HINTS=TRUE;

Optimizer statistics: ATP gathers stats with a nightly auto stats job. Real-time statistics collection gathers a subset of optimizer statistics for conventional DML operations: number of rows, MAX and MIN column values, and so on. High-frequency statistics collection gathers full optimizer statistics every 15 minutes if statistics are stale.

AJD

Oracle AJD is a new cloud service launched by Oracle around mid-August 2020. It is built for organizations and developers who want to build interactive applications and microservices that primarily deal with JSON data without compromising scalability, availability, performance, full ACID support, and complete SQL functionality. With cloud-native development all around, JSON is becoming a more and more popular choice to store data, as it can be easily consumed by several programming languages and provides a persistent format for application objects – another reason being that JSON is schema-flexible, so applications can change over time to accommodate new types of data without having to modify backend data definitions. This lets you quickly react to changing application requirements without requiring you to normalize data into relational tables and with no restriction to changing data structure or organization at any time.

With AJD, your JSON document-centric applications typically use Simple Oracle Document Access (SODA). SODA is a set of NoSQL-style APIs that help create and store collections of documents in JSON format and eliminate the need for SQL expertise for retrieving and querying JSON data. SODA collection APIs are exposed in several forms:

• Database tools, SQL Developer Web, and SQLcl
• SODA REST services
• Programming language drivers for Java, Node.js, Python, C, and PL/SQL

Quick note

AJD is similar to ATP with largely equivalent functionality and the same performance characteristics.

AJD provides all of the same features as ATP but with important limitations as you can only store up to 20 GB of data other than JSON document collections. There is no storage limit for JSON collections though. This could be a possible reason why AJD is offered at a lower price than ATP.

You can promote an AJD service to an ATP service to remove the 20 GB restriction on non-JSON data. You can not convert AJD to ATP, however.

AJD uses document-based databases and provides most of the same benefits that are typically associated with NoSQL document stores:

• High availability and performance at scale: Transparently scale the compute and storage capacity of your database while maintaining millisecond latencies for reads and writes.
• Simple document APIs: The SODA APIs make it easy to store JSON natively in the database. Using these APIs, you can build an entire application without having to write SQLs.

As I said, AJD is much more than a simple document store. It provides a rich set of features that are typically not found in NoSQL databases.

• Automatic administration and performance tuning: Routine database administration tasks, such as provisioning, performance tuning, encryption, patching, and taking backups, are performed automatically, so you can focus on developing your application.
• Full SQL query support: Natively-stored JSON using the document store APIs is fully accessible using ISO standard SQL. With AJD, you can use SQL to perform real-time analytics over JSON collections. You can also create real-time relational views over JSON to expose collections to existing relational tools and applications.
• ACID transactions: AJD supports robust transactions over JSON collections. ADB follows ACID protocols for JSON datatypes, like other RDBMS workloads, and makes it easy to perform complex operations over multiple collections atomically.
• Advanced security: Encryption and data-safe options are available with AJD.
• Supporting tools and services: AJD comes with a number of supporting services for processing and accessing data, including the following:
  • Oracle REST Data Services (ORDS): This can be used to build custom REST services over your JSON data.
  • APEX: This is used for building low-code applications over JSON collections.
  • Oracle Machine Learning Notebooks: This enables data scientists and data analysts to explore JSON data visually and develop analytical methodologies.

Keep one thing in mind: any SODA collection within AJD will have only JSON data. It cannot be mixed with LOB documents, unlike ATP databases.

Autonomous Data Guard is available for AJD. A standby database can be enabled either in a local region, cross-region, or both based on availability requirements. With Autonomous Data Guard, both the primary and standby (local or remote standby) databases are monitored for transactions and take the following actions:

• In case of the failure of the primary database, the standby database is converted into the primary database without user invention and with minimal interruption. Once failover is completed, a new standby database is automatically created by Autonomous Data Guard.
• Additionally, application or database admins can perform a manual switchover operation to convert the primary database into a standby database and vice versa.

Note that this is for shared infrastructure ADB only.

Now, since we have already looked at the classifications of ADB based on workload types, we will explore which infrastructure option is appropriate for your workload in terms of deployment.