Search

Top 60 Oracle Blogs

Recent comments

Autonomous

Autonomous Transaction Processing – your slice of the pie

I grabbed the following screen shot from a slide deck I’ve been giving about Autonomous Transaction Processing (ATP). It shows the services that are made available to you once you create your database. At first glance, it looks like we have a simple tier, where the lower in the tier you are, the smaller the slice of the database resource “pie” you get.

image

Oracle ATP: MEDIUM and HIGH services are not for OLTP

The Autonomous Transaction Processing services HIGH and MEDIUM are forcing Parallel DML, which can lock the tables in eXclusive mode.

This may seem obvious that the TP and TPURGENT are for OLTP. But when you know that the service names are associated with Resource Manager consumer groups, you may think that high priority use cases should run on the HIGH service. However those LOW, MEDIUM, HIGH services were probably named when ADW was the only Autonomous Database and it is not directly obvious that they are there for reporting only, or maybe for some batch operations.

Start/Stop your Autonomous Databases

The ATLAS experiment in LEGO®

Here are two blog posts on the Databases at CERN blog:

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:

CaptureOLTPvsDSS

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.

ADWC – System and session settings (DWCS lockdown profile)

By Franck Pachot

.
The Autonomous Data Warehouse Cloud service is a PaaS managed service where we have a PDB and an ADMIN user which has most of the system privileges. For example, we have the privilege to change initialization parameters:
SQL> select * from dba_sys_privs where grantee=user and privilege like 'ALTER S%';
 
GRANTEE PRIVILEGE ADMIN_OPTION COMMON INHERITED
------- --------- ------------ ------ ---------
ADMIN ALTER SESSION YES NO NO
ADMIN ALTER SYSTEM YES NO NO

ADWC – connect from your premises

By Franck Pachot

.
In the previous post about the Autonomous Data Warehouse Service, I’ve run queries though the Machine Learning Notebooks. But you obviously want to connect to it from your premises, with SQL*Net.

ADWC – the hidden gem: Zepplin Notebook

By Franck Pachot

.
IMG_5339
In the previous blog posts I explained how to create, and stop/start the Autonomous Data Warehouse Cloud service. And I didn’t show yet how to connect to it. It is easy, from sqlplus or SQL Developer, or SQLcl.

But there’s something more exciting to run some SQL queries: the Oracle Machine Learning Notebooks based on Apache Zepplin. At first, I didn’t realize why the administration menu entry to create users in the ADWC service was named ‘Manage Oracle ML Users’, and didn’t realize that the ‘Autonomous Data Warehouse Cloud’ header was replaced by ‘Machine Learning’.

ADWC – a Docker container to start/stop Oracle Cloud services

By Franck Pachot

.
In the previous post, I’ve explained how to start and stop the Autonomous Data Warehouse Cloud service from PSM (PaaS Service Manager). There’s a setup phase, and a run phase starting with service-start and ending with service-stop. And the setup is specific to an Oracle Cloud account, storing information in the local user home. You may want to run different setups, and even provide an easy way to start/stop an Oracle Cloud service without knowing the user, password and tenant name.

A Docker container is perfect to isolate this.

ADWC: start/stop with PSM Command Line Interface

By Franck Pachot

.
In the previous post, I explained how to create an Autonomous Data Warehouse with PSM (PaaS Service Manager Command Line Interface). The most common operation you want to do with it is starting and stopping the service. This is the best way to save credits for hourly billed services. And PSM is the easiest: run from everywhere (it is Python 3) and no need to provide credentials each time. In the previous post, I explained how to setup PSM for the ADWC service.

Unfortunately, for starting and stopping the instance you may realize that:

ADWC: Creation of Autonomous Data Warehouse Cloud service

You want to try the Autonomous Data Warehouse Cloud Service? That’s easy. Here is a Step-by-Step.