The state of the flagship as per Oracle OpenWorld 2016 – Oracle Database 12c Release 2, Exadata Express Cloud Service, SQL and PL/SQL

The new 12cR2 release of Oracle Database had already been announced and discussed at length over the past year. And now it is finally available.

And only as cloud service for now. Initially through the Exadata Express Cloud Service – which consists of a single PDB in a multitenant CDB, Enterprise Edition plus all relevant database options, and with several shapes, starting at $175/month for up to 20GB of data, one core, 6GB Memory for SGA and RAM, 120 GB/month Data Transfer and a maximum of 30 sessions . See this blog article for some more details on this offering.

In contrast to the preexisting Database as a Service offering, Exadata Express is fully managed by Oracle and does not require (nor allow) subscribers to perform their own DBA activities. A number of operations and functions are not accessible to subscribers to Exadata Express. An Exadata Express instance is provisioned very rapidly – nothing more than a clone PDB is required internally.

The full blown Enterprise Database as a Service offering based on this new 12cR2 release is expected in the Fall or Winter of 2016 as is the Exadata as a Service.

The date for availability of Oracle Database 12c Release 2 on premises is not yet given; in fact even the date at which that date will be given has not yet been announced. Oracle clearly uses this tactic to try to entice customers to the cloud – there does not seem to be any real technical reason why customers could not start with 12c Release 2 on premises. This of course somewhat undermines Oracle’s co-existence story that touts full compatibility between cloud and on premises and a lift and shift of workloads in both directions. Either customers cannot use 12cR2 functionality in their application or they lose the ability to lift and shift.

The improvements in 12cR2 fall into two broad categories – as always: new and improved functionality for database development and improved management options and run time behavior.

Database Development

In terms of functionality, the Oracle Database has become yet again a richer development platform, allowing more work in application architectures to be performed in the database. Generation and processing of JSON documents in PL/SQL is an example and so are Analytical Views.

These views can accessed by tools, applications and in reports using very simple SQL statements and in turn represent complex multi-dimensional and hierarchical data structures. The complexity of the SQL and of operations such as rolling up and drilling down is encapsulated inside the Analytical View.

Nice PL/SQL improvements included the deprecated pragma for program units that should no longer be used (see this article for details) and the dbms_plsql_code_coverage package that can be used to analyze the PL/SQL code executed during specific scenarios and during unit tests. Review this slide deck by Bryn Llewellyn as presented during OOW 2016.

Materialized views can now be refreshed at statement level – allowing long running transactions to benefit from query rewrite that include results from data manipulations that have taken place during the current transaction. Real Time Materialized Views are MVs that – while stale – can still be used for query rewrites by doing an line refresh (compose fresh data set using materialized view log) during the query.

Developers should also look at the still fairly new SQLcl command line tool (download from OTN), bringing some rich and productive features from SQL Developer to a command line that still also does everything SQL*Plus does. See slide deck by Galo Balda.

The latest release of SQL Developer offers valuable support around debugging PL/SQL – such as the ability to start debugging a database session from outside that session and the option to inspect the runtime context of a database session at a debug breakpoint.

SQL Developer has evolved into a very rich database development platform with support a wide range of database technologies and tools, including ORDS (Oracle Rest Data Services – a mechanism to expose database tables and PL/SQL packages as REST APIs), management of PDBs across on premises and cloud, APEX, PL/SQL debugging, Oracle NoSQL Database management, JSON and much more.

An huge performance improvement (order of magnitude or 10x) can be achieved with approximate analytics in cases where an approximate answer – about 50,000 – instead of the exact result (precisely 52,839) from an aggregate operation such as count or sum is good enough, which It frequently is. Oracle Database 12cR2 extends from the initial support for approximate analytics in the first release. Approximate analytics are based on mathematical algorithms, do not involve sampling, and have a known error margin.

For more details on application development with Forms and APEX, please check out my previous blog article: Oracle’s portfolio for Custom Application Development – snapshot taken at Oracle OpenWorld 2016.

Database Administration

For database management and run time performance, the biggest new feature in this new release is Sharding.

Sharding can be described as distributed partitioning – where subsets of data collections that logically belong together are physically stored in separate database instances – shards – that potentially are located in far removed physical locations. Transactions are executed against specific shards – ideally the shard that is close to the source of the transaction – and queries can be executed against the logical shard master that will translate the query into queries running on each of the relevant shards and combining the results into a single result set.

Applications communicate with a shard director using regular SQL statements. Based on session characteristics and perhaps the nature of the query of DML operation, the shard director will either direct the statement to one particular shard or to multiple shards – perhaps rewriting the statement for each individual shard. In case of distributed execution, the shard director has to gather the responses from all shards involved and construct the combined answer that is handed back to the application. The shard structure is transparent to applications.

This picture (from Oracle 12.2 sharded database management on SlideShare by Leyi Zhang) shows four tables; three of these are sharded – the physical data for these tables is held in three different locations that together form the logical data set. One table is duplicated across all physical instances – and is synchronized by GoldenGate and/or Active Data Guard. Queries that can be handled in one shard are executed against one shard – as to be determined by the ‘shard director’.

It seems logical to distribute the data across the shards based on geographical attributes – such as customers and orders for US customers go into the US shard and European customers have their data recorded in the European shard. Such a distribution will reduce latency challenges for most transactions and queries, while global queries and transactions are still supported as well, benefiting from the additional parallel query capabilities at each of the regional shards.

With sharding, Oracle Database breaks through several barriers. Horizontal scalability is within reach – depending especially on the capabilities of the query coordinator to run distributed queries and assemble the results. Assuming that works as promised, then linear scalability to very large data sets and high volumes of distributed users seems feasible. Because queries can succeed even in the absence of some shards, fault tolerance of the database architecture is increased. For high availability, shards – the distributed individual databases – can easily make use of each other as stand-by database, to allow fast failover.

Because each shard runs on an independent system – local, unshared files, memory and CPUs – the 12cR2 sharded database architecture offers linear scalability – if the database grows with more data in additional shards, the performance stays the same – with complete fault isolation for OLTP workloads [against different shards]. Note that more shards do have license consequences – another type of linear scalability I am afraid.

This slide deck tells the story of Sharding with Oracle Database 12cR2 at Paypal. Also read my article from OOW 2015 with more details about Sharding (and other Database 12cR2 features).

New in R2 is the ability to run the In-Memory database option on an Active Data Guard Standby database. The transactions flowing into the standby database are not only pushed to the data files but also used to synchronize the in memory data structures.

Another example of CQRS – with the standby database supporting very fast reporting and analysis without any impact on the master database instance doing the transaction processing.

Also new in R2 are the option to dynamically increase the memory size available for In-Memory data and a fast-start option that saves and retrieves In-Memory data to and from an LOB.

Important new capabilities around Pluggable Databases in 12cR2 include the ability to perform a hot clone – creating for example a fix or test environment based on a production system without incurring any downtime on the production system – and a refresh (to bring a PDB that started life as a clone again in synch with the original PDB).

Even a hot relocate of a PDB is supported – moving a PDB from one CDB to another, potentially moving it from on premises to the database cloud. PDBs can have local instead of shared undo data and can be flashed back individually – without affecting other PDBs. At the container database level, there are options to set maximum levels of resource usage – such as IOPS, memory and CPU – per PDB.

Interesting is a mechanism which I though already existed inside the database: in complex queries, the same sub-query might be executed many times (query in a “with” clause for example). In 12.2, the optimizer can create a temporary table on the fly for a specific cursor, only for the life of that cursor. It will then load data into this table and use it as many times as needed, instead of running the base query several times.

Also new are “band joins” : Join condition that involves a range of values rather than exact match; until now band join is evaluated either through nested loop or sort-merge join. The solution: the new truncating hash band join algorithm. It divides the join domain into small parts where each “small” part is size/range of the band; it then maps all the join values in same band into one single value

Download the AMIS OOW16 Highlights for an overview of announcements at OOW16.