Autonomous Data Warehouse Cloud Hands-On Lab : My Thoughts

I signed up to a hands-on lab for the Autonomous Data Warehouse Cloud Service. These are the notes I took during the lab. They are a little scrappy, but I think you will get the idea…

I had some prior information due to the briefings I attended before OpenWorld, but everything I’ve mentioned here has been said publicly in OpenWorld sessions and is part of the hands-on lab.

Lab Outline

During the hands-on lab we did the following.

• Connected to the service from SQL Developer.
• Created new schemas and connections to the service.
• Created tables and constraints with RELY DISABLE NOVALIDATE.
• Loaded data into the tables from an object store.
• Created external tables to files in an object store.
• Connected to a sample schema and ran some queries before and after online scaling the service.

Introduction

• The Oracle Public Cloud interface and provisioning looks similar to the current DBaaS offering, but a little more simplified. There are fewer options to fill in.
• The minimum storage is one 1TB with increments of 1TB. The storage scales on demand, so no dramas about starting small and increasing as you go. The storage is paid for on a monthly basis.
• CPU is paid for on an hourly basis. You can scale down to 0 and stop paying for the compute if you have downtime (weekends?), but you continue to pay for storage.
• You have an admin user, similar to system, but you don’t have SYS and SYSTEM access. No conventional OS access either. It’s similar to RDS for Oracle in that sense.
• Provisioning time for a new instance is about 10-20 seconds.
• Once you have the system provisioned there is pretty much no additional configuration you can do.
• Access requires a wallet, similar to the Exadata Express Cloud Service, so you need to download the connection details from the Client Access tab. You get a zip with the relevant connection details.
• If you manually create users, you need to grant them a role called DWROLE. That is the only role needed to connect and manage objects in the schema.

Object Creation

• Tables are created with no constraints (except NOT NULL) and no additional features like partitions etc.
• Primary keys, unique keys and FKs are defined with RELY DISABLE NOVALIDATE, so the optimizer has the necessary metadata, but no physcial structures like indexes are created.

Loading Data

Privileged operations are done used the DBMS_CLOUD package. Some of the things we did during the hands-on lab include.

• DBMS_CLOUD.CREATE_CREDENTIAL – Creates a credential object to authenticate to the object store (Oracle or AWS S3). The credential is created once, and is used by default for all operations from then on. The object store is used as a source for data loads and external tables. On initial release the number of formats are limited, but it will eventually include additional source formats over time.
• DBMS_CLOUD.COPY_DATA – Copies data from the object store into a table. This is full load operation. There are a number of options including table_name, file_uri_list, format. The format defines how the file should be loaded.
• DBMS_CLOUD.CREATE_EXTERNAL_TABLE – Create an external table pointing to the files in the object store, rather than loading them into the database.

The USER_LOAD_OPERATIONS view displays information about load operations.

As with the existing database cloud services, if you need to transfer a large amount of data it can be done by shipping it to Oracle for them to seed it. I can’t remember the name of the specific service, but suffice to say you will not have to SCP your petabyte warehouse files to the service. 🙂

Scaling and Performance

• The service is essentially scaled by resetting the CPU_COUNT for the instance in the cloud screens or via a REST API, so it is using a variation on instance caging to control CPU. CPU is charged by the hour. You can scale down to 0 when you don’t need resource, but you will still be paying for storage.
• In the initial release the SGA and PGA sizes are tied to the CPU count, so adding an extra CPU increases the SGA and PGA allocated. Future releases may make these independent, but for now this is the way it works.
• Parallel Statement Queuing is enabled, and the cloud interface allows you to monitor the statement queue. The queue is understandably affected by CPU count.
• The Query Result Cache is enabled, so for small result sets a second run of the statement is super fast. 🙂
• You are responsible for the schema design and the SQL you write against it, but you will not be creating indexes and partitioning strategies to address performance issues. The service is responsible for tuning the workload, not you.

Thoughts

• The hands-on lab was obviously quite limited in terms of the scope, so I can’t give a comprehensive review, but from what I have seen so far it appears Oracle have delivered what they said they would. A fully managed service that removes the need for operational DBAs as creation, backups, patching and upgrades are not your business.
• It’s hard to know at this point how well the automated tuning works. As more people try it out with different workloads we will get a proper feel for what it can and can’t do. What we do know is you will not be adding indexes or partitioning stuff, so at least that aspect of tuning is out of your control.
• I don’t know if everyone got to see this, but Hermann scaled my service and it just worked, completely online.
• It’s fun to theorise how they have achieved some of the aspects of service using the existing features.
• I’ll be interested to get my hands on it once it goes live.
• I’ve mentioned a few times in other posts, this is the first generation of this service. We don’t know how it will evolve over the coming months.
• Remember, if you don’t like the lack of control you can alway pick DBaaS, Exadata Cloud Service or run on-prem. These will not have the option of being autonomous though.
• Overall my feeling is I like it. I know this might sound odd coming from a DBA, but I like the hands-off nature of it.

Thanks to Yasin Baskan and Hermann Baer for putting on the session. Hermann, please don’t tell anyone you had to help me connect to the database when my brain rebooted and I wasn’t even capable of doing something as simple as that. It will remain our secret right?

Cheers

Tim…

Author: Tim...

DBA, Developer, Author, Trainer. View all posts by Tim...