By Laura Ramsey-Oracle on Jul 24, 2014
...with the long awaited In-Memory option, plus 21 new features. Oracle Database 12c Release 220.127.116.11 supports Linux and Oracle Solaris (SPARC and x86 64 bit).
And for those of us who just LOVE *sparknotes, here's a quick index of those new features with quick links from good friends and scribes in the Oracle Doc group. But first, a cool picture.
Sea Dragon photo taken at the Birch Aquarium in San Diego by my friend Joel Broude, a veteran of the Sun Microsystems documentation team.
Oracle Database 12c Release 18.104.22.168 New Features
Advanced Index Compression
Advanced Index Compression works well on all supported indexes, including those indexes that are not good candidates for the existing prefix compression feature; including indexes with no, or few, duplicate values in the leading columns of the index.
Advanced Index Compression improves the compression ratios significantly while still providing efficient access to the index.
Approximate Count Distinct
The new and optimized SQL function,
APPROX_COUNT_DISTINCT(), provides approximate count distinct aggregation. Processing of large volumes of data is significantly faster than the exact aggregation, especially for data sets with a large number of distinct values, with negligible deviation from the exact result.
The need to count distinct values is a common operation in today's data analysis. Optimizing the processing time and resource consumption by orders of magnitude while providing almost exact results speeds up any existing processing and enables new levels of analytical insight.
See: Oracle Database SQL Language Reference for details.
Attribute clustering is a table-level directive that clusters data in close physical proximity based on the content of certain columns. This directive applies to any kind of direct path operation, such as a bulk insert or a move operation.
Storing data that logically belongs together in close physical proximity can greatly reduce the amount of data to be processed and can lead to better compression ratios.
See: Oracle Database Data Warehousing Guide for details.
Automatic Big Table Caching
In previous releases, in-memory parallel query did not work well when multiple scans contended for cache memory. This feature implements a new cache called big table cache for table scan workload.
This big table cache provides significant performance improvements for full table scans on tables that do not fit entirely into the buffer cache.
See: Oracle Database VLDB and Partitioning Guide for details.
FDA Support for CDBs
Flashback Data Archive (FDA) is supported for multitenant container databases (CDBs) in this release.
Customers can now use Flashback Data Archive in databases that they are consolidating using Oracle Multitenant, providing the benefits of easy history tracking to applications using pluggable databases (PDB) in a multitenant container database.
See: Oracle Database Development Guide for details.
Full Database Caching
Full database caching can be used to cache the entire database in memory. It should be used when the buffer cache size of the database instance is greater than the whole database size. In Oracle RAC systems, for well-partitioned applications, this feature can be used when the combined buffer caches of all instances, with some extra space to handle duplicate cached blocks between instances, is greater than the database size.
Caching the entire database provides significant performance benefits, especially for workloads that were previously limited by I/O throughput or response time. More specifically, this feature improves the performance of full table scans by forcing all tables to be cached. This is a change from the default behavior in which larger tables are not kept in the buffer cache for full table scans.
See: Oracle Database Performance Tuning Guide for details.
In-Memory Aggregation optimizes queries that join dimension tables to fact tables and aggregate data (for example, star queries) using CPU and memory efficient
KEY VECTOR and
VECTOR GROUP BY aggregation operations. These operations may be automatically chosen by the SQL optimizer based on cost estimates.
In-Memory Aggregation improves performance of star queries and reduces CPU usage, providing faster and more consistent query performance and supporting a larger number of concurrent users. As compared to alternative SQL execution plans, performance improvements are significant. Greater improvements are seen in queries that include more dimensions and aggregate more rows from the fact table. In-Memory Aggregation eliminates the need for summary tables in most cases, thus simplifying maintenance of the star schema and allowing access to real-time data.
See: Oracle Database SQL Tuning Guide for details.
In-Memory Column Store
In-Memory Column Store enables objects (tables or partitions) to be stored in memory in a columnar format. The columnar format enables scans, joins and aggregates to perform much faster than the traditional on-disk formats for analytical style queries. The in-memory columnar format does not replace the on-disk or buffer cache format, but is an additional, transaction-consistent copy of the object. Because the column store has been seamlessly integrated into the database, applications can use this feature transparently without any changes. A DBA simply has to allocate memory to In-Memory Column Store. The optimizer is aware of In-Memory Column Store, so whenever a query accesses objects that reside in the column store and would benefit from its columnar format, they are sent there directly. The improved performance also allows more ad-hoc analytic queries to be run directly on the real-time transaction data without impacting the existing workload.
The last few years have witnessed a surge in the concept of in-memory database objects to achieve improved query response times. In-Memory Column Store allows seamless integration of in-memory objects into an existing environment without having to change any application code. By allocating memory to In-Memory Column Store, you can instantly improve the performance of their existing analytic workload and enable interactive ad-hoc data extrapolation.
PATH based notation and adds new operators that allow JSON
PATH based queries to be integrated into SQL operations.
Companies are adopting JSON as a way of storing unstructured and semi-structured data. As the volume of JSON data increases, it becomes necessary to be able to store and query this data in a way that provides similar levels of security, reliability and availability as are provided for relational data. This feature allows information represented as JSON data to be treated inside the Oracle database.
See: Oracle XML DB Developer's Guide for details.
New FIPS 140 Parameter for Encryption
The new database parameter,
DBFIPS_140, provides the ability to turn on and off the Federal Information Processing Standards (FIPS) 140 cryptographic processing mode inside the Oracle database.
Use of FIPS 140 validated cryptographic modules are increasingly required by government agencies and other industries around the world. Customers who have FIPS 140 requirements can turn on the
See: Oracle Database Security Guide for details.
PDB CONTAINERS Clause
CONTAINERS clause is a new way of looking at multitenant container databases (CDBs). With this clause, data can be aggregated from a single identical table or view across many pluggable databases (PDBs) from the root container. The CONTAINERS clause accepts a table or view name as an input parameter that is expected to exist in all PDBs in that container. Data from a single PDB or a set of PDBs can be included with the use of
CON_ID in the
WHERE clause. For example:
SELECT ename FROM CONTAINERS(scott.emp) WHERE CON_ID IN (45, 49);
This feature enables an innovative way to aggregate user-created data in a multitenant container database. Reports that require aggregation of data across many regions or other attributes can leverage the CONTAINERS clause and get data from one single place.
PDB File Placement in OMF
The new parameter,
CREATE_FILE_DEST, allows administrators to set a default location for Oracle Managed Files (OMF) data files in the pluggable database (PDB). When not set, the PDB inherits the value from the root container.
If a file system directory is specified as the default location, then the directory must already exist; Oracle does not create it. The directory must have appropriate permissions that allow Oracle to create files in it. Oracle generates unique names for the files. A file created in this manner is an Oracle-managed file.
The CREATE_FILE_DEST parameter allows administrators to structure the PDB files independently of the multitenant container database (CDB) file destination. This feature helps administrators to plug or to unplug databases from one container to another in a shared storage environment.
PDB Logging Clause
NOLOGGING clause can be specified in a
ALTER PLUGGABLE DATABASE statement to set or modify the logging attribute of the pluggable database (PDB). This attribute is used to establish the logging attribute of tablespaces created within the PDB if the
LOGGING clause was not specified in the
CREATE TABLESPACE statement.
If a PDB
LOGGING clause is not specified in the
CREATE PLUGGABLE DATABASE statement, the logging attribute of the PDB defaults to
This new clause improves the manageability of PDBs in a multitenant container database (CDB).
PDB Metadata Clone
An administrator can now create a clone of a pluggable database only with the data model definition. The dictionary data in the source is copied as is but all user-created table and index data from the source is discarded.
This feature enhances cloning functionality and facilitates rapid provisioning of development environments.
PDB Remote Clone
The new release of Oracle Multitenant fully supports remote full and snapshot clones over a database link. A non-multitenant container database (CDB) can be adopted as a pluggable database (PDB) simply by cloning it over a database link. Remote snapshot cloning is also supported across two CDBs sharing the same storage.
This feature further improves rapid provisioning of pluggable databases. Administrators can spend less time on provisioning and focus more on other innovative operations.
PDB Snapshot Cloning Additional Platform Support
With the initialization parameter
CLONEDB set to
true, snapshot clones of a pluggable database are supported on any local, Network File Storage (NFS) or clustered file systems with Oracle Direct NFS (dNFS) enabled. The source of the clone must remain read-only while the target needs to be on a file system that supports sparseness.
Snapshot cloning support is now extended to other third party vendor systems.
This feature eases the requirement of specific file systems for snapshot clones of pluggable databases. With file system agnostic snapshot clones, pluggable databases can be provisioned even faster than before.
PDB STANDBYS Clause
STANDBYS clause allows a user to specify whether a pluggable database (PDB) needs to be a part of the existing standby databases. The
STANDBYS clause takes two values:
NONE. While ALL is the default value, when a PDB is created with
STANDBYS=NONE, the PDB's data files are offlined and marked as
UNNAMED on all of the standby databases. Any of the new standby databases instantiated after the PDB has been created needs to explicitly disable the PDB for recovery to exclude it from the standby database. However, if a PDB needs to be enabled on a standby database after it was excluded on that standby database, PDB data files need to be copied to the standby database from the primary database and the control file needs to be updated to reflect their paths after which a user needs to connect to the PDB on that standby database and issue
ALTER PLUGGABLE DATABASE ENABLE RECOVERY which automatically onlines all of the data files belonging to the PDB.
This feature increases consolidation density by supporting different service-level agreements (SLAs) in the same multitenant container database (CDB).
PDB State Management Across CDB Restart
SAVE STATE clause and
DISCARD STATE clause are now available with the
ALTER PLUGGABLE DATABASE SQL statement to preserve the open mode of a pluggable database (PDB) across multitenant container database (CDB) restarts.
If SAVE STATE is specified, open mode of specified PDB is preserved across CDB restart on instances specified in the
INSTANCES clause. Similarly, with the DISCARD STATE clause, the open mode of specified PDB is no longer preserved.
These new SQL clauses provide the flexibility to choose the automatic startup of application PDBs when a CDB undergoes a restart. This feature enhances granular control and effectively reduces downtime of an application in planned or unplanned outages.
PDB Subset Cloning
USER_TABLESPACES clause allows a user to specify which tablespaces need to be available in the new pluggable database (PDB). An example of the application of this clause is a case where a customer is migrating from a non-multitenant container database (CDB) where schema-based consolidation was used to separate data belonging to multiple tenants to a CDB where data belonging to each tenant is kept in a separate PDB. The
USER_TABLESPACES clause helps to create one PDB for each schema in the non-CDB.
This powerful clause helps convert cumbersome schema-based consolidations to more agile and efficient pluggable databases.
Rapid Home Provisioning
Rapid Home Provisioning allows deploying of Oracle homes based on gold images stored in a catalog of pre-created homes.
Provisioning time for Oracle Database is significantly improved through centralized management while the updating of homes is simplified to linkage. Oracle snapshot technology is used internally to further improve the sharing of homes across clusters and to reduce storage space.
See: Oracle Clusterware Administration and Deployment Guide for details.
For full table access, zone maps allow I/O pruning of data based on the physical location of the data on disk, acting like an anti-index.
Accessing only the relevant data optimizes the I/O necessary to satisfy a query, increasing the performance significantly and reducing the resource consumption.
See: Oracle Database Data Warehousing Guide for details.
Ciao for now!