OCI―Knowing Oracle Autonomous Database

Video 1.  Introduction to Autonomous Database on OCI (YouTube link)

Oracle Autonomous Database powered by Oracle Database 18c is a "self-managing" autonomous database in the cloud.  It delivers automated patching, upgrades, and tuning—including performing all routine database maintenance tasks while the system is running—without human intervention. 

The Oracle Autonomous Database is fully elastic: You simply specify the number of OCPUs and the storage capacity in TB's for the database. At any time, you may scale, increase or decrease either the OCPUs or the storage capacity.

Figure 1.  One Autonomous Database - Optimized by Workload

ADW	ATP
Columnar Storage Format	Row Storage Format
Generates data summaries	Creates indexes (in Oracle Database 19c)
Memory helps to speed up Joins, Aggs, etc	Memory used in Caching to avoid extra IO

Table 1. Optimizations applied in ADW vs ATP

Autonomous Data Warehouse (ADW)

The Autonomous Data Warehouse Cloud for Data Warehouse, Data Mart, Data Science and Data Lake use cases, has been available in Oracle Cloud since March 2018.

ADW in OCI gives you your own fully managed Oracle Database data warehousing environment by

• Handling administration tasks such as 
• Creating the data warehouse database
• Backing up/upgrading/patching the database
• Not requiring any tuning
• The service automatically configures the database for high-performance queries

Figure 2.  Autonomous Data Warehouse Cloud Architecture

ADW Features

ADW Cloud supports (see Figure 2):

• Existing tools (running on-premises or in the cloud)
• Third-party BI tools
• Third-party data-integration tools
• Oracle BI and data-integration tools: BIEE, ODI, etc.
• Oracle Cloud Services
• Analytics Cloud Service, Golden Gate Cloud Service, Integration Cloud Service, etc.
• Connectivity via
• SQL*Net, JDBC, ODBC

Figure 3.  Security and Authentication in ADW

Security and Authentication

The following Security designs and implementation are included in ADW:

• Encrypted data in Oracle Database
• All data are stored in encrypted format in the Oracle Database
• Only authenticated users and applications can access the data when they connect to the database
• Certificate-based authentication and SSL
• All connections to ADW use certificate based authentication and Secure Sockets Layer (SSL)
• You can watch the video above at 30:24 mark to see where and how the Cloud Wallet can be used in SQL Developer client.
• All communications between the client and server are fully encrypted and cannot be intercepted or altered
• For more information on connections to Autonomous Data Warehouse see About Connecting to an Autonomous Data Warehouse Instance

Cloud-Based Inerfaces

The following cloud-based interfaces are included with ADW:

• Service console (web-based)
• Allows you to perform monitoring tasks such as viewing the recent levels of activity on the data warehouse.
• You can watch the video above at 25:04 mark to see how Service Console was launched from the Cloud Console.
• Notebook application (based on Apache Zeppelin)
• Provides simple querying, data-virtualization, and collaboration capabilities.
• You can watch the video above at 28:18 mark to see where the Notebooks are located in the Oracle Machine Learning home page.

Autonomous Transaction Processing (ATP)

Oracle Autonomous Transaction Processing is another family member of cloud services built on the self-managing Oracle Autonomous Database. Autonomous Transaction Processing (ATP) enables businesses to safely run a complex mix of high-performance transactions, reporting, batch, and machine learning along with simpler and faster application development.

The operations of ATP are very similar to ADW.  You can watch the video above starting at 32:10 for the details of it.

Performance Tuning

No more performance tuning is needed for both ADW and ATP because the core component (i.e., Oracle Autonomous Database) provides the following capabilities:

• Cost-based Optimization
• Automatic SQL query optimization, automatic statistics gathering, automatic query rewrite automatic SQL Plan management and automatic SQL tuning.
• Performance Tuning and Diagnostics
• Automatic memory and resource management, automatic index and materialized view advisors, Automatic Database Diagnostic Monitor, Automatic Database Workload capture & replay and Automatic Workload Repository. 
• Data Optimization
• Automatic undo management, automatic segment space management, Automatic Storage Management, Automatic Data Optimization and automatic columnar cache.

Note that many Database API operations are subject to throttling to balance traffics, optimize OCI resources, and prevent unsustainable consumption.

References

1. Oracle Instant Client
2. Autonomous Transaction Processing
3. Autonomous Data Warehouse
4. Oracle Cloud Infrastructure (redthunder.blog)
5. Getting started with Autonomous Data Warehouse Cloud 
6. More articles on OCI (XML and More)