What’s new in Oracle Database 19c and other #OOW18 feedback

Back from Oracle Open World #OOW18 and here are quick notes I’ve taken about what’s new. I focus on what was publicly announced, but with the ‘Safe Harbor’ statement: all this can be true, wrong or different (three-valued logic;) when 19c will be in General Availability. Rushing to publish those notes before entering the beta program as it will be hard to remember what was public or not.

Disclaimer: this was written before getting details on 19c, from content under ‘safe harbor statement’. This does not describe what will be actually implemented when 19c will be in general availability.

Long Term Support and Beta Program

19c is confirmed as Long Term Support version, with primary support at least to 2022 and extended support to 2025. Note that in Oracle Cloud, extended support is without additional fees.

If you are still in 11.2.0.4 because you don’t want frequent upgrades, 19c will be the release to go (but keep it up to date with Release Updates). If you want to be sure to do this upgrade as soon as 19c is available on premises, then better to test it as soon as possible and report any bug you encounter. This means:

• enter the beta program. Having a beta program for a release focused more on stability than new features is an awesome decision from Oracle Development. Sign-up for beta: https://pdpm.oracle.com
• test 19c in Oracle Cloud as soon as available. You may use more options than what you have on-premises for those tests, such as the Performance Analyzer.

Oracle Code One, Ground Breakers and XE

The big new thing, in my opinion, is that the Java One conference has been merged to Oracle Open World conference, and renamed to Oracle Code One. Interleaving Dev and Ops sessions is a really good idea. The Oracle Technology Network (OTN) which became Oracle Developer Community (ODC) is now becoming the Oracle Groundbreakers. What I like here is that there is no mention of technology or development. We are all in the same project: improve the user experience with all the application components, from system to code.

#codeone

This was also the occasion to give a talk more focused at developers than at DBA. At the time of Microservices, I wanted to share my experience in Dev and Ops teams, on monolithic, client/server, 3-tiers,… architectures. Because segregation of responsibility is great at logical level, but the way to performance and scalability must often go through colocation in same server, process, engine…

Presenting at a developer event was new to me, and it seems that the goal was reached. This is the kind of feedback I got which I really appreciate:

Oracle XE 18c is out (18.4)

In this talk, I mentioned Oracle XE which can be embedded for free. The 18c version of XE was released Saturday during the ACED Briefing. A lot larger than the 11g but with more options. Compression is an important one because the storage is limited to 12 GB.

19c new Optimizer features

The best you can do for the optimizer is providing accurate statistics. Daily gathering on stale tables is not sufficient, and dynamic statistics overload parse activity too much. We will have two new features:

• High Frequency Automatic Statistics Task: some statistics gathering will be done during the day, when the system is idle, controlled by the Resource Manager.
• Real-Time Statistics: some statistics (like high/low value) are maintained during DML. This can be great to avoid Out-of-Range problems with linear decay from obsolete statistics.

We should not rely on hints, but sometimes, we need. And we want to be sure that they are used (because no error is raised when not).

• Hint Usage Reporting: dbms_xplan reports the hints per operation and alias name, with an indication for those that were not used (and reason)

Talking about DBMS_XPLAN, the comparison of plans have improved:

• Compare SQL Execution Plans: shows directly where the plan diverges

At execution time, when a query never ends the worst that can happen is that the impatient run it again. That’s a good idea, but we all do that on Web applications: no answer -> hit F5 several times. The query will not run faster and this only increases the contention. Then, better to cancel the statement (resource manager can do that) and quarantine the bad execution plan:

• Automatic Diagnostic and Repair and Quarantine for Runaway Statements

Of course, the big feature for the 19c Autonomous Transaction Processing cloud service is Automatic Indexing. This deserves a longer description. The short idea is that a background job will capture and analyze the workload on short-term (last 15 minutes) to add better indexes, removes them, or blacklist them (SQL Patch) for some queries.

And the feature I like the most:

• Automatic SQL Plan Management: the capture will be automatic

Enabling the capture for all statements executed two times is not easy, even with the new filters introduced in the latest release. Here, the capture relies on AWR. When a query is long, the execution plan will be compared with past AWR snapshots. And if a better plan was used in the past, then this one will be the accepted SQL Plan baseline. This is what we do manually, isn’t it?

Other Database new features

• Fast ingest support for IoT: in conjunction with middle tier to stream data continuously to the database. The inserts are buffered in memory to benefit from asynchronous bulk inserts.
• Partitioned Hybrid Tables: the idea is to accept different organization (Heap/External) for partitions. Maybe have some old partitions in a read-only tablespace on Object Store?
• The multitenant main focus is bug fixes as the goal is to abandon non-CDB for the next main release (20c). 19c should allow RAT and ADDM at PDB level.
• Data Guard: the re-creation of broker configuration is a long-awaited feature. FSFO will be able to allow data loss, and dynamically change the target

One problem when offloading read-only application to Active Data Gard standby is that those applications may not run in a read-only instance. An example is updating a table when the user logs-in. Some DML will be possible:

• Data Guard: DML redirect on ADG for read-mostly applications

RAC is a key component for Oracle Cloud Autonomous Database, because of online patching:

• RAC: Zero downtime Infrastructure Patching (including JVM) and Read-Only instances
• ASM is not required anymore for Voting disks and OCR so we can get rid if the ASM instance when using dNFS

And talking about Cloud, after the public cloud in the private cloud, now here is the private cloud in the public cloud:

• dedicated ATP (Autonomous Transaction Processing): a quarter rack Exadata with limited access to CDB dedicated to you and where you can create multiple PDBs.