Hi,
Read all about it! I’ll review many of the new Oracle18c features., including (but not limited to) product availability, installation, administration, development, security, performance, high availability, data warehousing, spatial and graph, and distributed data.
Installation and Upgrade
As of this writing, Oracle Corp has Oracle18c available in the cloud, Windows, Linux, and Solaris.
Click here to jump to the database download page.
There is a plethora of documentation available including licensing information, upgrade guide, and release notes. This page also includes the installation guides for each of the above-available platform products. Also on this page are various documents that cover upgrade and migration scenarios. Click here to see this available documentation.
Application Development
Oracle releases Application Express 5.1 with this release. APEX 5.1 now includes an Interactive Grid, Icon Library, Page Designer Enhancements, Calendar Enhancements and Oracle JET Charts.
Oracle18 now has private temporary tables. This feature allows the scope of a temporary table to a session or a transaction.
Oracle18 JDBC now supports Key Store Service and Wallet Support in JDBC for Database Cloud Services.
There are SQL Enhancements for JSON, such as: A SQL statement can return JSON data using the syntax TREAT (… AS JSON). SQL/JSON can now return results as LOB data. Oracle18 allows for data-dictionary views to extract JSON field path and type information.
Oracle18 has additional features that allow PL/SQL and C programs to interact with SODA document collections.
JSON Path expressions can now contain JSON key names up to 255 characters.
PL/SQL has enhancements to the Hierarchical Profiler that allows for the profiler-run information to be stored into database tables.
Spatial database types can now participate in Oracle Sharding.
Enhancements to Text allow for automatic background defragmenting of an index while update staging to the main index. Frequent DML operations will no longer cause fragmentation issues with the main index. This will improve Text query performance.
Text also allows summary information to be displayed with the search results. This feature enables the user to drill down into query results.
Text also has a new word list, WILDCARD_INDEX, that replaces the current SUBSGTRING_INDEX, PRFEFIX_INDEX, and REVERSE_INDEX syntax.
Availability
Oracle18 has additional enhancements called ‘Server Draining’ when relocating or stopping PDB services.
Connection pools get Request Boundaries. This allows applications and application servers to use and return connections from these connection pools.
Transparent Application Continuity tracks and records sessions and traction states so recoverable outages are hidden from the database users. This feature is fully automatic and is achieved by using a new state-tracking infrastructure that tracks session state usage as applications issue user calls.
Data Guard can now use RMAN block change tracking file along with incremental backup to maintain the Oracle Active Data Guard.
Data Guard has two new nologging modes: Standby Nologging for Load Performance and Standby Nologging for Data Availability.
PDBs can be duplicated between encrypted and non-encrypted CDBs.
RMAN Recover of a standby database is simplified.
PDB backups can be plugged into a new CDB.
Backups from non-container database are useable after migration to a CDB.
RMAN can be used to duplicate a PDB into an existing CDB.
Sharding now includes support for PDBs as Shards, user-defined sharding, support for JSON, LOBs, and Spatial Objects. There are now consistency levels for Multi-Shard queries. Query explain plans now display information on all shards participating in the query. GoldenGate allows for the automatic configuration allowing replication to/from shards.
RAC also now supports sharding.
Big Data and Data Warehousing
Analytic Views now have new syntax: FILTER FACT (filters data accessed by the analytic view prior to aggregation), and ADD MEASURES allows for calculated measures to be calculated.
New analytic views supports a range of new functions that include RANK_*, PERCENTILE_*, STATS_*, COVAR_*, HIER_DEPTH, HIER_LEVEL, HIER_MEMBER_NAME, HIER_MEMBER_UNIQUE_NAME, HIER_CAPTION, and HIER_DESCRIPTION.
The Algorithm Meta Data Registration now has additional support for the R framework.
The new PQ_TIMEOUT_ACTION resource manager directive allows the DBA to specify action to take on a parallel SQL statement that has timed out.
The DBA can also cancel a SQL consuming excessive resources. The syntax is similar to: ALTER SYSTEM CANCEL SQL.
Oracle18 allows for inline external tables to be used as part of a SQL statement. This feature removes the need to create external tables before the data can be accessed.
Approximate technology, available starting with Oracle12 release 1, has been extended to Top-N style queries.
Oracle18 introduces Polymorphic Table Functions. This new type of table function extends analytical capabilities. This feature allows for arbitrary input tables or queries.
Database Overall
Oracle18 Connection Manager can now be configured in Traffic Director Mode.
Oracle18 has a number of new administration features for the Container Database (CDB).
You can now copy a PDB in an Oracle Data Guard Environment.
The Database Configuration Assistant can perform the mass creation of identical PDBs for test and development purposes.
Oracle18 also allows for PDB lockdown profiles at the PDB level. Oracle12 allowed for these only at the container level.
Refreshable PDBs can be switched with their master PDB. This feature is useful for load balancing and/or failover protection.
Oracle18 introduces a SQL interface for cloud-scale database monitoring and management across a variety of CDBs and their related PDBs.
Oracle18 introduces PDB snapshots, a copy of a PDB at a specific time. This feature is called a Snapshot Carousel and supports up to eight snapshots.
National Language Support now supports Unicode 9.0 (data files for AL32UTF8 and AL16UTF16).
Data Pump now has a new option CONTINUE_LOAD_ON_FORMAT_ERROR that allows Data Pump to continue to load when a data format error is encountered.
Partitioning
Partition-wise SQL operations now includes SELECT DISTINCT and Window Functions.
The ONLINE keyword can be used with ALTER TGABLE MERGE PARTITION and SUBPARTITION SQL statements.
A regular (non-partitioned) table can be modified to being a partitioned object using the ONLINE operation. This allows for regular tables to be changed to partitioned tables without recreating the tables or affecting the applications (no application downtime).
Performance
The SQL Tuning Adviser for Exadata now has new algorithms to better tune SQL for this environment.
The new DBMS_SQLSET allows for SQL tuning sets to be manipulated.
Concurrent SQL Execution with SQL Performance Analyzer now allows for concurrent SQL execution. This is useful for when working with very large SQL Tuning Sets.
New features here include some updates to the In-Memory database features. Some general performance enhancements include new scalable sequences and probably the greatest performance enhancement is the new MemOptimized RowStore.
In-Memory enhancements include Dynamic Capture Window for In-Memory Expressions. Prior releases allowed for only specific interval captures for the prior 24 hours. This enhancement allows for greater flexibility to define a time window to capture these expressions.
In-Memory Column Store now uses heat map data to help manage the column store available memory for the data being frequently accessed.
In-Memory Column Store now supports data from external tables.
In_Memory Optimized Arithmetic can be used to optimize calculations using number data. This feature has some increased overhead associated with it, as the column store must store Oracle numbers in their native format. The Performance engineers have observed consistent gains of 3 to 9 times faster on both simple and GROUP BY aggregations.
Sequence generators now have a SCALE clause that allows the generation of unordered primary (unique keys) for data ingestion workload having a high level of concurrency. This feature reduces the sequence and index bloc contention providing better data load scalability. I cover the locking issue usually associated with a primary key generator. You can use reverse-key indexes but this feature seems to perform the same kind of benefit allowing the index to be a regular b-tree structure (that is also available for range-scans).
The best performance enhancement is probably the MemOptimized Rowstore. This new feature allows for the fast lookup of data for tables that are queried based on primary key columns.
The MEMOPTIMIZE_POOL_SIZE needs to be set; a minimum of 100M is recommended. Recycle the database to create the pool. Alter or create the table that is to use this pool using they syntax ‘MEMOPTIMIZE FOR READ’. You then use the DBMS_MEMOPTIMIZE.POPULATE procedure to populate the pool for fast lookup of that table’s data.
There is a new buffer cache view that should help correctly size the buffer cache. The V$DB_CACHE_ADVICE shows hit ratios and predicts physical read factors. This feature also uses the additional buffer caches KEEP and RECYCLE! I have long advocated using these additional caches to separate table and possibly index activity from the general activity of the default buffer cache. This feature was to be de-allocated in Oracle12 but it either wasn’t or was re-allocated to Oracle18.
RAC and Grid
Improvements here include ASM database cloning. Oracle18 has a new ‘Cascade Option’ when dropping Oracle ASM File Groups. You can now convert conventional disk groups to ASM flex disk group without the MOUNTED RESTRICTED option. There is also a Cluster Health Advisor. This advisor also assists with Cross Cluster Analysis Support.
Security
New Oracle18 security features include the ability to create a User-Defined Master Encryption Key for use with Oracle Advanced Security.
Database link passwords are now encrypted. This feature allows Data Pump to export/import over these links using encrypted passwords.
Each pluggable database (PDB) can have its own password as well as having its own master encryption key. This allows for each PDB to have its own keystore. Oracle12 has only one keystore at the container level.
Also, sensitive data stored in the dictionary tables SYS.LINK$ and SYS.SCHEDULER$_CREDENTIAL can be encrypted.
Oracle18 now can authenticate and authorize users by using Microsoft Active Directory.
Oracle18 has ‘Schema Only’ accounts that allow for object ownership without having the ability for this schema owner to log into the database.
Database Replay (introduced in Oracle11) allows for replay using Database Vault.
Summary
I hope this article helps you understand some of the various new features of the Oracle18c database. I also hope you find this information useful when moving forward with your Oracle-based applications.
I reviewed the Oracle Database 18c New Features web page, click here to review additional details about this new release from Oracle.
Dan Hotka
Author/Instructor/Oracle Expert
Start the discussion at forums.toadworld.com