Dontcheff

Archive for 2018|Yearly archive page

DBA Internals of the Oracle Autonomous Database

In Cloud, DBA, Oracle database, Oracle internals on March 28, 2018 at 07:11

First things first: the word autonomous come from the Greek word autónomos which means “with laws of one’s own, independent”.

After starting using the Autonomous Data Warehouse Cloud, I must say I am pleasantly surprised to see something totally new, simple, uncomplicated and effortless, with no additional tuning or re-architecturing of the Oracle databases needed – the underlying Oracle Cloud Infrastructure is super fast and highly reliable.

1. You may connect to ADWC by either using the web interface as you can see above or as a client (I use SQL Developer 17.4) but for the client connection type choose Cloud PDB and not TNS. Your configuration file is a zip file and not a plain text file to what DBAs are used to.

2. You cannot create indexes on columns, you cannot partition tables, you cannot create materialized views, etc. Not even database links. You will get an error message: “ORA-00439: feature not enabled: Partitioning” or “ORA-01031: insufficient privileges”.

ADWC lets you create primary keys, unique keys and a foreign key constraints in RELY DISABLE NOVALIDATE mode which means that they are not enforced. These constraints can be created also in enforced mode, so technically you can create constraints as in a non-autonomous Oracle database.

Note that in execution plans primary keys and unique keys will only be used for single table lookups by the optimizer, they will not be used for joins.

But … you can run alter system kill session!

3. The Oracle Autonomous Data Warehouse interface contains all necessary capabilities for a non-professional database user to create its own data marts and run analytical reports on the data. You can even run AWR reports.

4. You do not have full DBA control as Oracle (in my opinion) uses lockdown profiles in order to make the database autonomous. As ADMIN user, you have 25 roles including the new DWROLE which you would normally grant to all ADWC users created by you. Among those 25 roles, you have GATHER_SYSTEM_STATISTICS, SELECT_CATALOG_ROLE, CONSOLE_ADMIN, etc. You have access to most DBA_ and GV_$ views. Not to mention the 211 system privileges.

5. ADWC configures the database initialization parameters based on the compute and storage capacity you provision. ADWC runs on dozens of non-default init.ora parameters. For example:

parallel_degree_policy = AUTO
optimizer_ignore_parallel_hints = TRUE
result_cache_mode = FORCE
inmemory_size = 1G

You are allowed to change almost no init.ora parameters except few NLS_ and PLSQL_ parameters.

And the DB block size is 8K!

6. I can see 31 underscore parameters which are not having default values, here are few:

_max_io_size = 33554432 (default is 1048576)
_sqlmon_max_plan = 4000 (default is 0)
_enable_parallel_dml = TRUE (default is FALSE)
_optimizer_answering_query_using_stats = TRUE (default is FALSE)

One of the few alter session commands you can run is “alter session disable parallel dml;”

7. Monitoring SQL is easy:

But there is no Oracle Tuning Pack: you did not expect to have that in an autonomous database, did you? There is no RAT, Data Masking and Subsetting Pack, Cloud Management Pack, Text, Java in DB, Oracle XML DB, APEX, Multimedia, etc.

8. Note that this is (for now) a data warehousing platform. However, DML is surprisingly fast too. I managed to insert more than half a billion records in just about 3 minutes:

Do not try to create nested tables, media or spatial types, or use LONG datatype: not supported. Compression is enabled by default. ADWC uses HCC for all tables by default, changing the compression method is not allowed.

9. The new Machine Learning interface is easy and simple:


You can create Notebooks where you have place for data discovery and analytics. Commands are run in a SQL Query Scratchpad.

10. Users of Oracle Autonomous database are allowed to analyze the tables and thus influence on the Cost Based Optimizer and hence on performance – I think end users should not be able to influence on the laws (“νόμος, nomos”) of the database.

Conclusion: The Autonomous Database is one of the best things Oracle have ever made. And they have quite a portfolio of products….

Finally, here is a live demo of the Oracle Autonomous Data Warehouse Cloud:

Advertisements

2018, the year of the Cloud underdog Oracle?

In Cloud, DBA, Oracle database on January 8, 2018 at 10:46

“Without data you’re just another person with an opinion.” – W. Edwards Deming

Let us see, based on data, why the Cloud underdog Oracle can be the winner of 2018 and beyond. Especially, for databases in the Cloud!

Let us check out the most recent data coming from Forrester, Gartner, Forbes and Accenture:

1. Enterprise Workloads Meet the Cloud (Accenture)

“Simply put, an enterprise system consists of an application and the underlying database and infrastructure. Regardless of whether the solution in on-premises or delivered ‘as a service’ the application relies on those two components. Thus, the performance, uptime and security of an application will depend on how well the infrastructure and databases support those attributes.”

Both Figure 1 and Figure 2 show impressive results: the Oracle Cloud Infrastructure allows more than 3000 transactions per second while the leading cloud provider cannot even reach 400. Even the old Oracle Cloud Infrastructure Classic is at 1300 transactions per second.

The Oracle Cloud Infrastructure latency averages at 0.168ms while the leading cloud providers have about 6 times higher latency in average: 0.962ms.

“Armed with these insights, companies should be ready to consider moving their Oracle mission critical workloads to the Oracle Cloud—and reaping the benefits of greater flexibility and more manageable costs.”

2. The Total Economic Impact Of Oracle Java Cloud Service (Forrester)

Let us move to the Java Cloud Service and check the new Forrester Reserch

The costs and benefits for a composite organization with 30 Java developers, based on customer interviews, are:
– Investment costs: $827,384.
– Total benefits: $3,360,871.
– Net cost savings and benefits: $2,533,488.

The composite organization analysis points to benefits of $1,120,290 per year versus investment costs of $275,794, adding up to a net present value (NPV) of $2,533,488 over three years. With Java Cloud Service, developers gained valuable time with near instant development instances and were finally able to provide continuous delivery with applications and functionality for the organization.

3. Market Share Analysis: Public Cloud Services, Worldwide (Gartner)

Table 2, PaaS Public Cloud Service Market Share, 2015-2016 (Millions of U.S. Dollars), ranking by Annual Growth Rate 2016:

1. Oracle 166.9%
2. Amazon 109.1%
3. Alibaba 99.0%
4. Microsoft 46.4%
5. Salesforce 40.2%

Table 3. SaaS Public Cloud Service Market Share, 2015-2016 (Millions of U.S. Dollars), ranking by Annual Growth Rate 2016 (Forrester):

1. Oracle 71.6%
2. Workday 38.8%
3. Dropbox 38.0%
4. Google 37.9%
5. Microsoft 32.6%

4. Oracle And Its Cloud Business Are In Great Shape–And Here Are 10 Reasons Why (Forbes)

For its fiscal Q2 ending Nov. 30, Oracle reported total cloud revenue of $1.5 billion, up 44%, including SaaS revenue of $1.1 billion, up 55%. The combined revenue for cloud and on-premise software was up 9% to $7.8 billion.

Oracle’s Q3 guidance offered growth rates extremely close to those recently posted by salesforce.com: when you add in the highly nontrivial fact that that same company with the $6-billion cloud business also has a $33-billion on-premises business and has rewritten every single bit of that IP for the cloud, with complete compatibility for customers taking the hybrid approach—and the percentage of customers taking the hybrid approach will be somewhere between 98.4% and 100%.

5. Oracle’s Larry Ellison Challenges Amazon, Salesforce And Workday On The Future Of The Cloud (Forbes):

While Salesforce.com’s current SaaS revenue of more than $10 billion is much larger than Oracle’s current SaaS revenue—for the three months ended Aug. 31, Oracle posted SaaS revenue of $1.1 billion—Oracle’s bringing in new SaaS customers and revenue much faster than Salesforce.

The following quote is rather interesting: “Since Larry Ellison has spent the past 40 years competing brashly against and beating rivals large and small, it wasn’t a huge shock to hear him recently rail about how cloud archrival Amazon “has no expertise in database.” But it was a shocker to hear Ellison go on to say that “Amazon runs their entire operation on Oracle [Database]…. They paid us $60 million last year in [database] support and license! And you know who’s not on Amazon? Amazon is not on Amazon.

And finally, the topic of In-Memory databases is quite hot. Several database brands have their IMDB. A picture is worth a thousand words: