Dontcheff

Archive for the ‘Autonomous’ Category

Oracle Autonomous Database: Dedicated vs Serverless

In Autonomous, Databases, DBA, Exadata, Oracle database, Oracle Engineered Systems on July 22, 2019 at 11:45

This blog post describes the 5 main differences between ATP-D & ATP-S and the 5 main ATP-D physical characteristics and constraints.

Autonomous Transaction Processing Dedicated (ATP-D) is a deployment choice that enables us to provision autonomous databases into dedicated Exadata cloud infrastructure instead of a shared infrastructure with other tenants.

ATP-D can be used for both OLTP or hybrid workloads for databases of any size. ATP-D is specifically good when you need highest governance, consistent performance and operational control.

For now, dedicated infrastructure means a Quarter Exadata Rack. Half and Full will be soon available too.

Besides complete physical storage isolation, ATP-D provides private IP networking, secure connections using transport layer security (TLS) credentials, and customization of software image lifecycle to align with application lifecycle.

The Fleet Administrator (think of a DBA for Autonomous Cloud) needs to create first the Exadata Infrastructure, then the CDB and only at the end the PDB. Recall that for ATP-S, you directly create the PDB.

These are the 5 main differences between ATP-D and ATP-S:

– Private IPs are not yet supported for serverless ADB deployments but they are on the short-term roadmap
– Private IPs are supported with ATP-Dedicated

– Serverless edition has no minimums or maximums for terms of usage
– Dedicated edition has a minimum term of one month and the minimum OCPU purchase is 1 OCPU per database node and up to the maximum number of OCPUs per rack: $26.88 per hours which means about $645 per day

– Loading data from object stores via DBMS_CLOUD is the recommended method for loading large data sets
– DBMS_CLOUD to load data is not applicable for ATP-D because DBMS_CLOUD is not available on ATP-D

– In ATP-D, the database version is 19c which is required for Auto-Indexing which is on by default
– Support for 19c / Auto-Indexing on ATP-S is on the roadmap

– ADB (serverless) does have auto-scaling – you can select auto scaling during provisioning or later using the Scale Up/Down button on the Oracle Cloud Infrastructure console
– ATP-D does not have auto-scaling support

Here are the 5 main ATP-D physical characteristics and constraints:

1. Quarter rack X7 Exadata Infrastructure:
– 2 severs: 92 OCPUs and 1.44TB RAM
– 3 Storage Servers: 76.8TB Flash and 107TB Disk

2. Cluster / Virtual Cloud Network:
– 1 Cluster per quarter rack

3. Autonomous Container Database:
– Maximum of 4 CDBs per Cluster
– The default data and temporary tablespaces for the database are configured automatically
– The name of the default data tablespace is DATA
– The database character set is Unicode AL32UTF8
– Compression is not enabled by default – use the table_compression clause if needed

4. Autonomous Database:
– High Availability SLA – Maximum 200 DBs
– Extreme Availability SLA – Maximum 25 DBs
– Placement Logic – Open on 1 server < 16 OCPU

5. Types of Users: Fleet Admin, Database Admin and Database User as Fleet Admin activities separated from DB Admin using IAM privileges

Note that now, there are 2 tabs for possible database connections – the serverless style DB connection and application connection:

Fleet Administrator: Fleet administrators create, monitor and manage Autonomous Exadata Infrastructure and Autonomous Container Database resources – a fleet administrator must be an Oracle Cloud user whose permissions permit the management of these resources and permit the use of the networking resources that need to be specified when creating these resources

Database Administrator: DBAs create, monitor and manage Autonomous Databases. Additionally, they create and manage Oracle Database users within these databases, and provide others the information necessary access the database – when creating an Autonomous Database resource, the DBA defines and gains access to the ADMIN administrative user account for the database

Database User: Database users are the developers who write applications that connect to and use an Autonomous Database to store and access the data. Database users do not need Oracle Cloud accounts: they gain network connectivity to and connection authorization information for the database from the database administrator

Few useful links:

A Using Oracle Database Features in Autonomous Transaction Processing
FAQs for Autonomous Transaction Processing – Dedicated
Data Center Regions for PaaS and IaaS
Oracle broadens the audience for Automated Transaction Database
2 Ways Oracle’s Autonomous Database Just Got More Useful

Bottom line: even with Autonomous, DBAs will be still needed!

Advertisements

Automatic Indexing in 19c

In Autonomous, Database tuning, Databases, DBA, Oracle database on February 18, 2019 at 17:38

One of the most impressive new features of Oracle Database 19c is Automatic Indexing. Arguably, this is the most interesting innovation in the database world for a rather long time.

I remember some years ago when a DBA asked me at an Oracle conference: “Julian, why are half of the presentations at Oracle database conferences only about performance tuning? Is the Oracle database performing that badly that people should tune it all the time?” Sigh…

With 19c and ADB (Oracle Autonomous Database), things look very different now, don’t they? Automatic Indexing provides what database systems need: continuous optimization of the database workload, stable & solid performance and almost no human interaction. Let me share some of my early experience with Automatic Indexing and where human interaction is needed.

For now (February 18th, 2019), Oracle 19c is only available on Exadata (Linux 7.4) and in order to enable Automatic Indexing you need to do the following:

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');

As you can guess from the picture above, the so called expert system of Automatic Indexing runs every 15th minute for as long as one hour. Note that I disabled the job from 4:43 till 5:56. The Resource Manager plan limits the task to 1 CPU only and the next run is skipped if the job has not been completed within the 15 minutes.

Here are details on how Automatic Indexing works but what is most important to remember is as follows:

– The auto index candidates are created as invisible auto indexes
– If the performance of SQL statements is not improved from the auto indexes, then the indexes are marked as unusable and the corresponding SQL statements are blacklisted
– Auto indexes cannot be used for any first time SQL run against the database
– Auto indexes are created as either single, concatenated indexes or function-based indexes and they all use advanced low compression
– The unused auto indexes are deleted after 373 days (can be changed)
– The unused non-auto indexes (manual indexes) are never deleted by the automatic indexing process but can be deleted automatically if needed

The Auto Indexing can be disabled at any time or can be set to set to reporting mode (new auto indexes as created asinvisible indexes, so that they cannot be used in SQL) with the following commands:

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');

 

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','REPORT ONLY');

Here is a way to ask Oracle to create new auto indexes in a separate tablespace called AUTO_INDEX_TS:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE','AUTO_INDEX_TS');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

You can easily check the configuration for Automatic Indexing for the root container and the PDBs from CDB_AUTO_INDEX_CONFIG;

If you need a report of what happened during the expert system activity (either last 3 days or during the last activity), here is a way to generate it:

set long 300000
select DBMS_AUTO_INDEX.REPORT_ACTIVITY(SYSTIMESTAMP-3,SYSTIMESTAMP,'TEXT','ALL','ALL') from dual;
select DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY('TEXT','ALL','ALL') from dual;

These are the most important views about Auto Indexing:

DBA_AUTO_INDEX_EXECUTIONS: history of execution of automatic indexing tasks
DBA_AUTO_INDEX_STATISTICS: statistics related to auto indexes
DBA_AUTO_INDEX_IND_ACTIONS: actions performed on auto indexes
DBA_AUTO_INDEX_SQL_ACTIONS: actions performed on SQL statements for verifying auto indexes
DBA_AUTO_INDEX_CONFIG: configuration settings related to auto indexes
DBA_AUTO_INDEX_VERIFICATIONS: stats about PLAN_HASH_VALUE, AUTO_INDEX_BUFFER_GETS, etc.

The new package DBMS_AUTO_INDEX can be used for 3 main things:

1. Configuration of the parameters related to Auto Indexing
2. Drop *all* the indexes except the ones used for constraints
3. Report the activity of the “expert system”:

Finally, here are some additional resources:

Automatic Indexing in Oracle Database 19c
Oracle Database 19c is now available!
Managing Auto Indexes

How to check if I have any auto indexes in my database: select auto, count(*) from dba_indexes group by auto;

Few interesting facts about Oracle ADB, Redshift and Snowflake

In Autonomous, Data Warehouse, Databases, DBA on January 14, 2019 at 16:17

Building a new data warehouse in the cloud or migrating an existing one to cloud requires careful consideration and the answer to the question “Which cloud should I use?” is often “It depends”.

An interesting comparison of system properties comparing Amazon Redshift vs. Oracle vs. Snowflake can be found on db-engines.com

There are several other options too: Azure SQL Data Warehouse, Presto, Google BigQuery, etc.

An interesting benchmark paper called “Data Warehouse Benchmark: Redshift, Snowflake, Azure, Presto and BigQuery” by Fivetran is worth reading!

Another comparison called Interactive Analytics: Redshift vs Snowflake vs BigQuery is already more than 2 years old but still interesting.

Recently, things have changed. Oracle’s Autonomous Data Warehouse Cloud has been in GA for almost 1 year (since March 2018). ADW is for enterprise loads and mission critical systems arguably the best solution right now.

Viscosity compared both Oracle Autonomous and Amazon Redshift. The result? Check it here: Amazon vs Oracle: Data Warehouse Services, How do They Compare?

In short, the conclusion of the research above is:

– Oracle’s ADW was able to achieve data retrieval at the lowest latencies, and achieved the highest volume of queries per hour. In terms of serial query execution and multi-user query throughput.
– Oracle’s ADW consistently outperformed Redshift by a factor of 4x in both sets of tests.

And do not ignore the db-engines ranking! Only one of the three is in the Top 10.

What is interesting to know on top of all papers above are these 10 differences or let us call them less known technical facts (in no order of importance) between Oracle Autonomous, Amazon Redshift and Snowflake:

1. Snowflake compute usage is billed on a per-second basis, with a minimum of 60 seconds. Amazon Redshift is based on PostgreSQL 8.0.2 and is built on top of technology from the MPP data warehousing company ParAccel. Oracle Autonomous Database is based on Exadata and 18c.

2. In Oracle Autonomous Cloud, you can provision up to 128 CPUs and 128TB directly from the cloud console but you can provision more if needed.

3. Snowflake manages all aspects of how data is stored in S3 including data organization, file sizes, structure, compression, and statistics.

4. The only things needed for BYOL in Oracle Autonomous Database are Multitenant and RAC (only when using more than sixteen OCPUs). The standby option (not yet available) will require Active Data Guard as well.

5. Snowflake does not disclose the information about processing power and memory. Oracle do disclose the information via internal views but you cannot directly define the SGA or PGA size.

6. Redshift is not built as a high-concurrency database with several concurrent running queries and AWS recommends that you execute no more than 15 queries at a time. The number of concurrent user connections that can be made to a cluster is 500.

7. Oracle ADW and ATP allow you to partition both indexes and tables. In Snowflake partitioning is handled internally. Amazon Redshift does not support tablespaces, table partitioning, inheritance, and even certain constraints. Amazon Redshift Spectrum supports table partitioning using the CREATE EXTERNAL TABLE command.

8. The maximum number of tables in Amazon Redshift is 9,900 for large and xlarge cluster node types and 20,000 for 8xlarge cluster node types. The limit includes temporary tables. An Oracle database does not have a limit for the number of tables.

9. Oracle automatically applies all security updates (and online!) to ensure data is not vulnerable to known attack vectors. Additional in-database features like Virtual Private Database and Data Redaction are also available.

10. There is no operation in Snowflake for collecting database statistics. It is handled by the engine. In Oracle, database statistics collection is allowed. Both Oracle Autonomous and Amazon Redshift monitor changes to your workload and automatically update statistics in the background.

Finally, here are official URLs of all three products:

Oracle Autonomous Database
Amazon Redshift
Snowflake Database

Amazon’s Aurora and Oracle’s Autonomous ATP

In Autonomous, Cloud, DBA, PostgreSQL on August 29, 2018 at 09:26

Databases are very much like wine, cheese and trees: they get better as they age.

Amazon Aurora exists since 2015. The word aurora comes Latin, means dawn. The name was borne by the Roman mythological goddess of dawn and by the princess in the fairy tale Sleeping Beauty.

Both Amazon’s “dawn” Aurora and Oracle’s ATP are typical cloud OLTP systems.

The question is: what are their differences, which one is better and meant exactly for my needs?

Oracle ATP is based on Oracle’s database and Exadata, here are all the innovations adopted from both systems:

Amazon’s Aurora has 2 flavors: Amazon Aurora MySQL and Amazon Aurora PostgreSQL.

Amazon Aurora MySQL is compatible with MySQL 5.6 using the InnoDB storage engine. Certain MySQL features like the MyISAM storage engine are not available with Amazon Aurora. Amazon Aurora PostgreSQL is compatible with PostgreSQL 9.6. The storage layer is virtualized and sits on a proprietary virtualized storage system backed up by SSD. And you pay $0.20 per 1 million IO requests.

Oracle’s Autonomous database comes also in 2 flavors: Oracle ADW and Oracle ATP. Check Franck Pachot’s article ATP vs ADW – the Autonomous Database lockdown profiles to see the differences of both cloud databases.

In general, one can compare Oracle ADW with Amazon Redshift and Oracle ATP with Amazon Aurora.

One way to compare is to look at the ranking provided by DB-Engines: Amazon Aurora vs. Oracle. No-brainer who the leader is: score of 1300 vs score of 5 in favor of Oracle.

Another interesting comparison comes from Amalgam Insights. Check how Oracle Autonomous Transaction Processing lowers barriers to entry for data-driven business. Check out the DBA labor cost involved: 5 times less in favor of Oracle ATP compared to Amazon! All the routine DBA tasks have been totally eliminated.

The message from them is very clear: “Oracle ATP could reduce the cost of cloud-based transactional database hosting by 65%. Companies seeking to build net-new transactional databases to support Internet of Things, messaging, and other new data-driven businesses should consider Oracle ATP and should do due diligence on Oracle Autonomous Database Cloud for reducing long-term Total Cost of Ownership.”

This month (August 2018), there was an interesting article by Den Howlett entitled Oracle introduces autonomous transaction processing database – pounds on AWS. Here are 2 interesting and probably correct statements/quotes from there:

1. It really is hard to get off an established database, even one that can be as expensive as Oracle can turn out to be.
2. Some of the very largest workloads will not go to the public cloud anytime soon. Maybe never which in internet years is after 2030.

As a kind of proof of how reliable and fast Oracle’s Autonomous Transaction Processing database is consider the following OLTP workload running non-stop in a balanced way without any major spikes and without a single queued statement!

No human labor, no human error, and no manual performance tuning!

Migrating Amazon Redshift to Autonomous Data Warehouse Cloud

In Autonomous, Data Warehouse, DBA, Exadata, PostgreSQL on July 4, 2018 at 18:34

“Big Data wins games but Data Warehousing wins championships” says Michael Jordan. Data Scientists create the algorithm, but as Todd Goldman says, if there is no data engineer to put it into production for use by the business, does it have any value?

If you google for Amazon Redshift vs Oracle, you will find lots of articles on how to migrate Oracle to Redshift. Is it worth it? Perhaps in some cases before Oracle Autonomous Data Warehouse Cloud existed.

Now, things look quite different. “Oracle Autonomous Data Warehouse processes data 8-14 times faster than AWS Redshift. In addition, Autonomous Data Warehouse Cloud costs 5 to 8x less than AWS Redshift. Oracle performs in an hour what Redshift does in 10 hours.” At least according to Oracle Autonomous Data Warehouse Cloud white paper. And I have nothing but great experiences with ADWC. For the past half an year or so.

But, what are the major issues and problems reported by Redshift users?

One of the most common complaints involves how Amazon Redshift handles large updates. In particular, the process of moving massive data sets across the internet requires substantial bandwidth. While Redshift is set up for high performance with large data sets, “there have been some reports of less than optimal performance,” for the largest data sets. An article by Alan R. Earls entitled Amazon Redshift review reveals quirks, frustrations claims that reviewers want more from the big data service. So:

Why to migrate from Amazon Redshift to Autonomous Data Warehouse Cloud?

1. Amazon Redshift is ranked 2nd in Cloud Data Warehouse with 14 reviews vs Oracle Exadata which is ranked 1st in Data Warehouse with 55 reviews.

The top reviewer of Amazon Redshift writes “It processes petabytes of data and supports many file formats. Restoring huge snapshots takes too long”. The top reviewer of Oracle Exadata writes “Thanks to smart scans, the amount of data transferred from storage to database nodes significantly decreases”.

2. Oracle Autonomous dominates in features and capabilities:

DB-engines shows an excellent system properties comparison of Amazon Redshift vs. Oracle.

In addition, reading through these thoughts on using Amazon Redshift as a replacement for an Oracle Data Warehouse can be worthwhile. It shows how Amazon Redshift compares with a more traditional DW approach. But Enterprises have some Redshift concerns, including:

– The difference between versions of PostgreSQL and the version Amazon uses with Redshift
– The scalability of very large data volume is limited and performance suffers
– The query interface is not modern, interface is a bit behind
– Redshift needs more flexibility to create user-defined functions
– Access to the underlying operating system and certain database functions and capabilities aren’t available
– Starting sizes may be too large for some use cases
– Redshift also resides in a single AWS availability zone

3. Amazon Redshift has several limitation: Limits in Amazon Redshift. On the other hand, you can hardly find a database feature not yet implemented by Oracle.

4. But the most important reason why to migrate to ADWC is that the Oracle Autonomous Database Cloud offers total automation based on machine learning and eliminates human labor, human error, and manual tuning.

How to migrate from Amazon Redshift to Autonomous Data Warehouse Cloud?

Use the SQL Developer Amazon Redshift Migration Assistant which is available with SQL Developer 17.4. It provides easy migration of Amazon Redshift environments on a per-schema basis.

Here are the 5 steps on how to migarte from Amazon Redshift to Autonomous Data Warehouse Cloud:

1. Connect to Amazon Redshift
2. Start the Cloud Migration Wizard
3. Review and Finish the Amazon Redshift Migration
4. Use the Generated Amazon Redshift Migration Scripts
5. Perform the Post Migration Tasks

Check out what Paul Way says about why Oracle thinks Autonomous IT can ultimately win the Cloud War.

Finally, here is what Amazon CTO Werner Vogels is saying: Our cloud offers any database you need. And I agree with him that a one size fits all database doesn’t fit anyone. But mission and business critical enterprise systems with huge requirements and resource needs deserve only the best.

The DBA profession beyond autonomous: a database without a DBA is like a tree without roots

In Autonomous, Cloud, Databases, DBA on May 30, 2018 at 19:41

“To make a vehicle autonomous, you need to gather massive streams of data from loads of sensors and cameras and process that data on the fly so that the car can ‘see’ what’s around it” Daniel Lyons

Let me add that the data must be stored somewhere, analyzed by some software, monitored and backed up by someone, and so on and so on…

Top 5 Industry Early Adopters Of Autonomous Systems are: (1) Information Technology: Oracle’s Autonomous Data Warehouse Cloud, (2) Automotive, (3) Manufacturing, (4) Retail and (5) Healthcare.

Being an early adopter of ADWC, I must say that it is probably the best product created by Oracle Corporation. For sure part of Top Five.

This month (May 2018), ComputerWeekly published an article quoting Oracle CEO Mark Hurd that the long-term future of database administrators could be at risk if every enterprise adopts the Oracle 18c autonomous database.

“Hurd said it could take almost a year to get on-premise databases patched, whereas patching was instant with the autonomous version. If everyone had the autonomous database, that would change to instantaneous.”

So where does that leave Oracle DBAs around the world? Possibly in the unemployment queue, at least according to Hurd.

“There are hundreds of thousands of DBAs managing Oracle databases. If all of that moved to the autonomous database, the number would change to zero,” Hurd said at an Oracle media event in Redwood Shores, California.

If you are interested in more detail on this subject, I suggest you read the following articles in the order below:

The Robots are coming by James Anthony: “But surely we’ve been here before? Indeed, a quick Google search brings up the following examples of white papers by Oracle with a reference to the database being self-managing all the way back to 2003.”

Oracle Autonomous Database and the Death of the DBA by Tim Hall: “Myself and many others have been talking about this for over a decade. ”

Death of the DBA, Long Live the DBA by Kellyn Pot’Vin-Gorman: “With DBAs that have been around a while, we know the idea that you don’t need a DBA has been around since Oracle 7, the self-healing database.”

No DBA Required? by Tim Hall: “It will be interesting to see what Oracle actually come up with at the end of all this…”

Self-Driving Databases are Coming: What Next for DBAs? by Maria Colgan: “It’s also important for DBAs to remember that the transition to an autonomous environment is not something that will occur overnight.”

Death of the Oracle DBA (again) by Johanthan Stuart: “Twenty years later I run Claremont’s Managed Services practice and the DBA group is our largest delivery team.”

Don’t Fall For The “Autonomous Database” Distraction by Greg McStravick: a totally different point of view on autonomous databases.

Now, “a picture is worth a thousand words”. Here are 5 screenshots from the Autonomous Data Warehouse Cloud documentation:

1. Who will be creating external tables using the DBMS_CLOUD package?

2. Who will run “alter database property set.. ” in order to create credentials for the Oracle Cloud Infrastructure?

3. Who will restore and recover the database in case of any type of failure? Or failures never happen, right?

4. Who will manage run away SQL with cs_resource_manager and run “alter system kill session”?

5. Who will manage the CBO statistics and add hints?

As of today, we have 4 Exadata choices with Autonomous being by far the best. For data warehouse loads for now. As explained by Alan Zeichick, Autonomous Capabilities Will Make Data Warehouses — And DBAs — More Valuable. “No need for a resume writer: DBAs will still have plenty of work to do.”

So still: a database without a DBA is like a tree without roots.

P.S. Check out the book Human + Machine: Reimagining Work in the Age of AI by Paul R. Daugherty and H. James (Jim) Wilson.