Today, April 25th, Oracle Database 19c became available to downloaded from Oracle.com.
Oracle Database 19c is the final member of the 12.2 family a.k.a 12.2.0.3 and is, therefore, the ‘long term support’ release. This means it will come with 4 years of premium support and 3 years of extended support. Making this release the version of the database that most folks are going to upgrade to next.
So, what can you expect?
There are hundreds of useful enhancements in Oracle Database 19c as well as several new features. Dom Giles‘s latest post on the Oracle Database Insider blog has all the details on the new release, while I’ve listed just a couple of my personal favourites below.
Quarantine SQL Plans
Oracle Resource Manager has always allowed you to specify criteria to define a runaway query. Any SQL statement that exceeded the specified limits would be automatically be terminated. However, nothing prevented an end-user from continually issuing such statements, wasting valuable system resources. Starting in 19c, runaway SQL plans terminated by the Resource Manager due to excessive consumption of CPU or I/O resources will be automatically quarantined. This will prevent these plans from executing again.
A new column in V$SQL called QUARANTINED indicates which SQL statement’s plan has been placed in quarantine. While the AVOIDED_EXECUTION column indicates the number of executions attempted after the plan was quarantined. You can control the quarantining of SQL plans using either the new DBMS_SQLQ package or DBMS_RESOUCE_MANAGER. You can also view the current configuration using DBA_SQL_QUARANTINE.
But what can you do about these plans that have been quarantined?
You can try and manually tune the statement so that a new plan is generated. If the statement has a new plan it will be allowed to execute again and will only go back into quarantine if the new plan exceeds the specified limits. Or you could take advantage of Automatic Indexing.
Automatic Indexing
Auto Indexing is an expert system that emulates the behaviour of a performance engineer. It monitors the workload on the database and determines if there are any indexes that might help improve it. Unlike are previous Index Advisor, Auto Indexing doesn’t just make recommendations. It validates if the recommended indexes will actually help(builds indexes and test executes statements), identifies any statements that may regress with the introduction of such an index and put a SQL plan baseline in place to prevent a regression before publishing the index to the application.
As it works through the 5-step process shown above on a periodic basis, it also learns from its own mistakes (reinforcement learning).
Given the overhead of having to maintain indexes for all DML operations, Auto Indexing also removes any of its indexes (names always start with SYS_AI) that are no longer in use / needed.
You can control the use of Auto Indexing and generate reports to see what it has been up to via the new package DBMS_AUTO_INDEX.
JSON Enhancements
There are several great JSON enhancements in 19c but one of my favourites is the simplification of generating JSON data from the Oracle Database. You’ve been able to generate JSON data using one of the following four SQL operators JSON_OBJECT, JSON_OBJECTAGG, JSON_ARRAY, and JSON_ARRAYAGG since 12.2, but now you no longer have to specify all of the key names and values if you simply want all of the columns from a table in your JSON documents.
In the example below I want to create a separate JSON document for each row in the scott.emp table.
12.2 Version
SELECT JSON_OBJECT('EMPNO' VALUE empno, 'ENAME' VALUE ename, 'JOB' VALUE job, 'MGR' VAULE mgr,'HIREDATE' VALUE hiredate, 'SAL' VALUE sal, 'COMM' VALUE com, 'DEPTNO' VALUE deptno ) FROM scott.emp; {"EMPNO":7839,"ENAME":"KING","JOB":"PRESIDENT","MGR":NULL, "HIREDATE":"1981-11-17T00:00:00","SAL":5000,"COMM":NULL, "DEPTNO":10} {"EMPNO":7698,"ENAME":"BLAKE","JOB":"MANAGER","MGR":7839, "HIREDATE":"1981-05-01T00:00:00","SAL":2850,"COMM":NULL, "DEPTNO":30} {"EMPNO":7782,"ENAME":"CLARK","JOB":"MANAGER","MGR":7839, "HIREDATE":"1981-06-09T00:00:00","SAL":2450,"COMM":NULL, "DEPTNO":10}
19c Version
SELECT JSON_OBJECT(*) FROM scott.emp; {"EMPNO":7839,"ENAME":"KING","JOB":"PRESIDENT","MGR":NULL, "HIREDATE":"1981-11-17T00:00:00","SAL":5000,"COMM":NULL, "DEPTNO":10} {"EMPNO":7698,"ENAME":"BLAKE","JOB":"MANAGER","MGR":7839, "HIREDATE":"1981-05-01T00:00:00","SAL":2850,"COMM":NULL, "DEPTNO":30} {"EMPNO":7782,"ENAME":"CLARK","JOB":"MANAGER","MGR":7839, "HIREDATE":"1981-06-09T00:00:00","SAL":2450,"COMM":NULL, "DEPTNO":10}
Don’t forget you can get familiar with 19c by checking out the 19c documentation and taking advantage of LiveSQL.oracle.com which has been running Oracle Database 19c since Jan 16th.
For more information on what you can expect from Oracle Database 19c Check out the following presentation
For more information on when 19c will be available on our Cloud services and on other on-premises for platforms, please check MOS note 742060.1 for the latest schedule.
You should also check out the cool new Database Features App which gives you details on exactly what release a specific feature is available in. You can search by release, focus area or feature name.