Oracle Database 19c is now available!

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 a 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 favorites 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 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 number of executions attempted after 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 behavior 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 stmts), 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 number of JSON enhancements in 19c but one of my favorites 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.

Or if you prefer you can check out the 19c video series on the Oracle Database Product Managers YouTube channel.

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 latest schedule.

You should also check out the cool new Database Features App which give you details on exactly what release a specific feature is available in. You can search by release, focus area or feature name.

This entry was posted in New Features, Oracle Database 19c and tagged , , . Bookmark the permalink.

2 Responses to Oracle Database 19c is now available!

  1. Mikhail Velikikh says:

    Hi Maria,

    Per “Database Licensing Information User Manual”:
    https://docs.oracle.com/en/database/oracle/oracle-database/19/dblic/Licensing-Information.html#GUID-0F9EB85D-4610-4EDF-89C2-4916A0E7AC87

    Most performance related features, including two of those that you mentioned, are not available in EE:
    Automatic Indexing
    SQL Quarantine
    Real-Time Statistics
    High-Frequency Automatic Optimizer Statistics Collection

    They are available in EE-ES and Oracle Cloud offerings.
    Oracle does not seem to care much about their pure-EE customers nowadays.

  2. Pingback: Oracle 19c New Features | It Was Like That When I Got Here

Leave a Reply

Your email address will not be published. Required fields are marked *