How Autonomous is the Oracle Autonomous Data Warehouse?

How Autonomous is the Oracle Autonomous Data Warehouse? Christian Antognini / Dani Schnider @chrisantognini antognini.ch/blog @dani schnider danischnider.wordpress.com BASLE BERN BRUGG DÜSSELDORF HAMBURG COPENHAGEN LAUSANNE FRANKFURT A.M. FREIBURG I.BR. GENEVA MUNICH STUTTGART VIENNA ZURICH

Agenda 1. Introduction 6. Query Performance 2. Set Up 7. Monitoring Performance 3. Connectivity 8. Miscellaneous 4. Loading Data 9. Conclusion 5. ETL Performance 2 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

Introduction 3 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

Larry Ellison, ATP Announcement, 7th August 2018 “There is nothing to learn, there is nothing to do” 4 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

Autonomous Data Warehouse Cloud Automated Database Administration Automated Performance Tuning « you do not need to configure or manage any hardware, or install any software. Autonomous Data Warehouse handles creating the data warehouse, backing up the database, patching and upgrading the database, and growing or shrinking the database.» «When you use Autonomous Data Warehouse, no tuning is necessary. You do not need to consider any details about parallelism, partitioning, indexing, or compression. The service automatically configures the database for high-performance queries.» Source: Using Oracle Autonomous Data Warehouse, Chapter 1 https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/user/getting-started.html#GUID-4B91499D-7C2B-46D9-8E4D-A6ABF2093414 5 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

Set Up 6 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

Creating Number of CPU (1.128) Name Storage (1.128 TB) PDB admin password License type Tags (optional) 7 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

Scale Up/Down At any moment, without downtime, it’s possible to scale up/down the number of CPU and the available storage 8 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

Initialization Parameters That Can Be Modified APPROX FOR AGGREGATION PLSCOPE SETTINGS APPROX FOR COUNT DISTINCT PLSQL CCFLAGS APPROX FOR PERCENTILE PLSQL DEBUG AWR PDB AUTOFLUSH ENABLED1 PLSQL OPTIMIZE LEVEL OPTIMIZER CAPTURE SQL PLAN BASELINES2 PLSQL WARNINGS OPTIMIZER IGNORE HINTS TIME ZONE2 OPTIMIZER IGNORE PARALLEL HINTS NLS * System level only 2Session level only 1 9 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

Resource Manager A plan (DWCS PLAN) with three consumer groups is pre-configured Consumer Group CPU Session Pool PX Server Limit DOP Limit HIGH 4 Unlimited 50 CPU COUNT1 MEDIUM 2 Unlimited 84 4 LOW 1 2*CPU COUNT1 1 OTHER GROUPS 1 Unlimited 1 When several instances are used, CPU COUNT “CPU Core Count” 1 10 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

Resource Manager – Run Away Queries Except for OTHER GROUPS, two thresholds to configure when queries are cancelled can be set Run time in seconds Amount of disk I/O in MB Remark: 0 detection disabled 11 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

Connectivity 12 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

How to Connect to Autonomous Data Warehouse Cloud Tool Purpose Connection Method Service Console service management Apache Zeppelin Oracle Machine Learning browser SQL Developer development, ad-hoc queries JDBC (automatic configuration) SQL*Plus, SQLcl, Toad, development, . ad-hoc queries ETL Tools (ODI, 3rd party) 13 data integration services BI Tools (OBIEE, 3rd BI services 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse? OCI, JDBC, ODBC (manual configuration)

Service Management via Browser Used for Service Console Start/Stop DB Scale up/down Restore Management of credential 14 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

Oracle Machine Learning Notebook style application for advanced SQL users Apache Zeppelin Interactive data analysis Graphical reports Typical Users Data scientists Developers Business users 16 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

Connection with SQL Developer Download client credential file Via Service Console Create new connection Type “Cloud PDB” Import credential file Enter keystore password (only for versions 18.2) Select service level (LOW, MEDIUM, HIGH) 17 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

Manual Connection Configuration Connections using Oracle Net Services (SQL*Net) For SQL*Plus, SQLcl, Toad, . ETL and BI tools, 3rd party tools Oracle Cloud JDBC Thin Client Encrypted SSL connection TCP/IP ODBC 18 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse? Wallet / Keystore JDBC OCI Wallet / Keystore Oracle Call Interface (OCI) Autonomous Data Warehouse Cloud

Loading Data 19 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

Loading Data object storage service client computer cloud Oracle Object Storage SQL*Net Oracle SQL*Loader SQL*Net 3rd party application HTTPS / REST Amazon Simple Storage Service (S3) Microsoft Azure Blob Storage 20 09.04.2023 HTTPS / REST How Autonomous is the Oracle Autonomous Data Warehouse? command-line interface

Object Storage Service (OSS) ADW supports the following object storage services Oracle’s Object Storage Amazon Simple Storage Service (S3) Microsoft’s Azure Blob Storage Those services store data as objects within buckets (containers) Objects are identified with an URI The access to the objects stored within buckets is protected Credentials managed through DBMS CLOUD are used 21 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

Command Line Interface (CLI) Oracle provides a CLI to work with Oracle Cloud Infrastructure (OCI) objects and services The CLI is built on Python and runs on Windows, Linux and Mac The CLI makes calls to the OCI API Among other things, the CLI can be used to manage buckets and objects stored in the supported OSS 22 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

DBMS CLOUD It provides features to manage credentials to access an OSS manage external tables that can be used to query data stored in an OSS handle objects stored in an OSS handle files stored on ADW in DATA PUMP DIR Data Pump 12.2 supports importing (but not exporting) a file stored in an OSS ALTER DATABASE PROPERTY SET default credential 'ADMIN.CHRIS' impdp dumpfile default credentials:https:// /test.dmp 23 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

Example – Loading Data into a 1TB TPC-DS Schema (1) Step 1: Upload the text files from the file system of a server hosted in the cloud (same region) to the Oracle’s object storage oci os object bulk-upload --bucket-name tpcds --src-dir /data/tpcds --include "*.dat" By default, such a statement uses 10 parallel threads The load took 6 hours Step 2: Load the data into the database through DBMS CLOUD The number of allocated CPU cores determines how fast data is loaded With 32 cores it took 67 minutes 24 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

Example – Loading Data into a 1TB TPC-DS Schema (2) Example of scalability: load 22GB into the CATALOG RETURNS table source: https://antognini.ch/2018/07/observations-about-the-scalability-of-data-loads-in-adwc/ 25 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

ETL Performance 26 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

Prepared for High ETL Performance Parallel DML Execution Direct-Path Load Operations Good ETL performance is possible Setup is optimized for DWH and ETL But details must be considered Online Statistics Gathering No Indexes Hybrid Columnar Compression 27 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

Parallel DML Execution and Direct-Path Operations Parallel DML (PDML) is enabled by default Only if CPU core count 1 and consumer group is MEDIUM or HIGH Hint /* parallel */ can be added (usually not needed) – Set optimizer ignore parallel hints FALSE (default: TRUE) Direct-Path INSERT is used For parallel DML If hint /* append */ is added 28 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

Parallel DML Execution and Consumer Groups -------------------------------------------------------------------------------------- Id Operation Name TQ IN-OUT PQ Distrib -------------------------------------------------------------------------------------- 0 INSERT STATEMENT 1 LOAD AS SELECT CUSTOMERS 1 2 PX COORDINATOR 3 PX SEND QC (RANDOM) :TQ10000 Q1,00 P- S QC (RAND) 4 OPTIMIZER STATISTICS GATHERING Q1,00 PCWC 5 PX BLOCK ITERATOR Q1,00 PCWC 6 TABLE ACCESS STORAGE FULL CUSTOMERS Q1,00 PCWP --------------------------------------------------------------------------------------- HIGH MEDIUM - automatic DOP: Computed Degree of Parallelism is 8 ------------------------------------------------- Id Operation Name ------------------------------------------------- 0 INSERT STATEMENT 1 LOAD TABLE CONVENTIONAL CUSTOMERS 1 2 TABLE ACCESS STORAGE FULL CUSTOMERS -------------------------------------------------- LOW - automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation - PDML disabled because object is not decorated with parallel clause - Direct Load disabled because no append hint given and not executing in parallel 29 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

Parallel DML / Direct-Path and Constraints Restrictions must be considered: If FK constraints are defined, PDML / direct-path is disabled Conventional load is used Recommendation: Define reliable constraints --------------------------------------------------- Id Operation Name --------------------------------------------------- 0 INSERT STATEMENT 1 LOAD TABLE CONVENTIONAL CUSTOMERS 1 2 PX COORDINATOR 3 PX SEND QC (RANDOM) :TQ10000 4 PX BLOCK ITERATOR 5 TABLE ACCESS STORAGE FULL CUSTOMERS ---------------------------------------------------Note ----- automatic DOP: Computed Degree of Parallelism is 8 - PDML disabled because parent referential constraints are present ALTER TABLE customers 1 ADD FOREIGN KEY (country id) REFERENCES countries RELY DISABLE NOVALIDATE 30 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

Online Statistics Gathering for Direct-Path Loads Statistics are gathered automatically Unlike 12c, this works also for non-empty tables for histograms ----------------------------------------------------- Id Operation Name ----------------------------------------------------- 0 INSERT STATEMENT 1 LOAD AS SELECT TARGET 2 PX COORDINATOR 3 PX SEND QC (RANDOM) :TQ10000 4 OPTIMIZER STATISTICS GATHERING 5 PX BLOCK ITERATOR 6 TABLE ACCESS STORAGE FULL SOURCE ------------------------------------------------------ Two new undocumented parameters optimizer gather stats on load all (default: TRUE) optimizer gather stats on load hist (default: TRUE) 31 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

Statistics Gathering for Conventional Loads Attention: statistics are not gathered automatically Call DBMS STATS with default values Automatic Statistics Gathering job is enabled, but maintenance windows are disabled 32 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

No Indexes Indexes are not allowed in ADW CREATE INDEX s order item delivery date idx ON s order item (delivery date); ORA-01031: insufficient privileges Good! Avoids many ETL performance issues Indexes are not needed for (most) analytical queries Exception: indexes are created for primary key and unique constraints 33 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

Index Maintenance (PK and Unique Constraints) Index maintenance after direct-path load Very time-consuming, cannot be parallelized Workaround: create PK with RELY DISABLE NOVALIDATE 34 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

Hybrid Columnar Compression All tables are automatically HCC compressed, with row-level locking enabled SELECT table name, compression, compress for FROM user tables ORDER BY table name TABLE NAME -----------------H BEER H BOTTLING H BREW H CUSTOMER H MALT H ORDER 35 09.04.2023 COMPRESS --------ENABLED ENABLED ENABLED ENABLED ENABLED ENABLED COMPRESS FOR ----------------------------QUERY HIGH ROW LEVEL LOCKING QUERY HIGH ROW LEVEL LOCKING QUERY HIGH ROW LEVEL LOCKING QUERY HIGH ROW LEVEL LOCKING QUERY HIGH ROW LEVEL LOCKING QUERY HIGH ROW LEVEL LOCKING How Autonomous is the Oracle Autonomous Data Warehouse?

Example: Loading Data Vault Schema Data Vault Model of Craft Beer Brewery 25 target tables (Hubs, Links, Satellites) 20 beers, 272K customers 16M orders, 173M order items “Daily loads” for 8 months – Random data generator – Data Vault load patterns – Delta detection and versioning – 1.4M order items / day 36 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

Example: Loading Data Vault Schema 4 CPUs 8 CPUs 1 2 37 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

Query Performance 38 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

Result Cache It is enabled by default on ADW RESULT CACHE MODE FORCE For queries being re-executed, it can lead to a tremendous performance improvement To avoid caching, use the NO RESULT CACHE hint Even if OPTIMIZER IGNORE HINTS TRUE (default on ADW) 39 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

Improve Query Performance What cannot be done Partition tables Create materialized views Use In-Memory Column Store Create indexes (except for PK/UK) 40 09.04.2023 What can be done Scale up the number of CPU cores Use different service Use constraints to enable query transformations (e.g. join elimination) How Autonomous is the Oracle Autonomous Data Warehouse?

How Are Queries on a Star Schema Optimized? Since no index is automatically created, the star transformation is not available Instead, the query optimizer can use the vector transformation Introduced in 12.1.0.2 for In-Memory Aggregation In many situations, faster than star transformation Note ----- vector transformation used for this statement Works even if tables are not populated in IMCS In-Memory is enabled (but not used) in ADW – INMEMORY SIZE 1073741824 41 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

Example – TPC-DS Queries Query 38 – Execution Time in Seconds 350 305 300 311 305 303 Service 250 200 HIGH MEDIUM LOW 174 175 150 90 100 91 55 50 0 42 90 2 CPU Cores 09.04.2023 4 CPU Cores 8 CPU Cores How Autonomous is the Oracle Autonomous Data Warehouse? 90 31 16 CPU Cores

Example – Sample Star Schema Benchmark (SSB) 2 millions 2556 6 billions 2 millions 30 millions 43 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

Example – Sample Star Schema Benchmark (SSB) Query 1 (1 Dimension, 1 Fact Table, 1 Aggregated Row) 200 189 150 4 CPU Cores 8 CPU Cores 16 CPU Cores 100 50 42 15 0 44 09.04.2023 0.01 1st Exec 0.01 2nd Exec How Autonomous is the Oracle Autonomous Data Warehouse? 0.01

Example – Sample Star Schema Benchmark (SSB) Query 14 (4 Dimensions, 1 Fact Table, 800 Rows) 450 400 350 300 250 200 150 100 50 0 45 09.04.2023 413 362 4 CPU Cores 8 CPU Cores 16 CPU Cores 132 120 44 42 1st Execution 2nd Execution How Autonomous is the Oracle Autonomous Data Warehouse?

Automated Tuning? Result cache? Exadata storage indexes? (in addition to disk I/O caching) source: https://www.oracle.com/database/data-warehouse.html Not observed 46 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

Monitoring Performance 47 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

Monitoring Performance and SQL Statements Service Console with monitoring capabilities: Activity and utilization – Storage and CPU usage Default 8 days, can be changed with AWR settings – Real-time or time period Running SQL statements – Time & wait statistics, I/O statistics – Runtime execution plan – Parallel processes – Downloadable real-time SQL Monitoring report 48 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

49 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

Monitoring Performance with AWR and ASH Reports AWR reports can be generated (only on PDB level) With DBMS WORKLOAD REPOSITORY With SQL Developer reports SELECT output FROM TABLE( DBMS WORKLOAD REPOSITORY.AWR REPORT HTML (3951758934, 7, 3524, 3535) ) ASH reports can be generated based on V ACTIVE SESSION HISTORY / DBA HIST ACTIVE SESS HISTORY SQL Developer reports 50 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

Miscellaneous 54 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

Backup & Recovery Automatic (incremental) backups take place daily The start time cannot be set The retention period for automatic backups is 60 days Manual backups can be initiated through the console They are stored in the object store The DEFAULT CREDENTIAL and DEFAULT BUCKETS database properties must be set Recovery at any point-in-time can be initiated through the console 58 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

Patching Oracle is patching the service on a regular basis No announcement No downtime There is no way to “schedule” when the installation of the patches takes place 59 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

Oracle Support As soon as a problem cannot be solve because of missing privileges, an SR has to be opened Support has (almost) no visibility E.g. no access to the alert.log of the PDB E.g. no OS access Support relies on Operations to fix things The issues we experienced were fixed in a time frame going from one days to one week 60 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

Conclusion 62 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

Conclusion Appropriate setup for DWH Better than many manual configured DWHs Easy (limited) administration, ready to use No partitioning, no In-Memory (yet) Support in case of a service request can take too long Sometimes “shaky” (no control about patches / changes) Not as simple as it looks at first sight Knowhow about physical DB design still important 63 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse? !

Further Information in Blog Posts DBMS CLOUD Package – A Reference Guide https://antognini.ch/2018/07/dbms cloud-package-a-reference-guide/ Which Privileges Are Required to Use the ADWC Service Console? https://antognini.ch/2018/07/which-privileges-are-required-to-use-the-adwc-service-console/ Observations About the Scalability of Data Loads in ADWC https://antognini.ch/2018/07/observations-about-the-scalability-of-data-loads-in-adwc/ External Tables in Autonomous Data Warehouse Cloud https://danischnider.wordpress.com/2018/07/04/external-tables-in-autonomous-data-warehouse-cloud / Gathering Statistics in the Autonomous Data Warehouse Cloud https://danischnider.wordpress.com/2018/07/11/gathering-statistics-in-the-autonomous-data-warehouse-cloud / 10 Tips to Improve ETL Performance – Revised for ADWC https://danischnider.wordpress.com/2018/07/20/10-tips-to-improve-etl-performance-revised-for-adwc/ Star Schema Optimization in Autonomous Data Warehouse Cloud https://danischnider.wordpress.com/2018/09/13/star-schema-optimization-in-autonomous-data-warehouse-cloud / 64 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?

Questions and Answers Christian Antognini Dani Schnider 65 @chrisantognini antognini.ch/blog @dani schnider danischnider.wordpress.com 09.04.2023 How Autonomous is the Oracle Autonomous Data Warehouse?