12c database New features list

The below is Gathered from various sources and listed out here, thanks to all who shared those in their respective blogs, Optimizer Blog, Tim hall, Ask Tom etc.

Core Architecture Changes:-

• Container & Pluggable Databases – 
• New parameter PGA_AGGREGATE_LIMIT, to limit the pga memory utilization
• New role CDB Administrator has been introduced
• Multi threaded database with parameter threaded_executions
• Multiple LGWR processes for each PDB’s and can share master container LGWR process.

Few SQL/PLSQL Changes:-

• Duplicate indexes, One can create duplicate indexes, earlier we use to get error if we are creating a index on same columns which already had index, now you can create index on same columns but with different leading column
• Increase in the datatype limit to 32K for varchar2,nvarchar2, RAW
• Invisible columns, you can make columns invisible
• Identity columns, (same as like sequences but inline with table definition, no need of seperate sequence object and trigger to increment it)

Some Administrative changes:-

• No bounce for modifying database to archivelog mode
• No redo for datapump import
• Temporary global tables will not generate redo
• Centralized patching.
• Datafile maintenance is online, no offline required to modify/move the datafile
• Truncate command enhanced to truncate the child rows as well
• UNDO for temporary tables can now be managed in TEMP, rather than the regular UNDO tablespace.
• Reduces contents of regular UNDO, allowing better flashback operations.
• Reduces the size of redo associated with recovering the regular UNDO tablespace.

Partitioning Enhancements:- (Tim hall)

• Asynchronous Global Index maintenance for DROP and TRUNCATE. Command returns instantly, but index cleanup happens later.
• Cascade for TRUNCATE and EXCHANGE partition.
• Multiple partition operations in a single DDL
• Online move of a partition(without DBMS_REDEFINTIION).
• Interval + Reference Partitioning.

Security Enhancements:-

• Monitor the privilege assignments easy now with DBMS_PRIVILEGE_CAPTURE
• Separation of Duties:
  - SYSDBA – Super user
  - SYSOPER – More limited than SYSDBA, but still very powerful.
  - SYSBACKUP – Just enough to do a backup.
  - SYSDG – Just enough for data guard administration.
  - SYSKM – Just enough to perform basic key management tasks.
  - Roles for audit management

Notable changes in Compression:- (Tim hall)

• Advanced Row compression (for Hot Data).
• Columnar Query compression (for Warm Data).
• Columnar Archive compression (for Archive Data).

New things in Data Guard 12c: (Tim hall)

• Oracle Database 12c introduces a new redo transportation method which omits the acknowledgement (to primary) of the transaction on the standby. This feature is called "Fast Sync" redo transport.
• Creating a new type of redo destination called "Far Sync Standby". A "Far Sync Standby" is composed only of the standby control files, the standby redo logs and some disk space for archive logs which shall be sent to the Standby database. Failover & Switchover operations are totally transparent as the "Far Sync Standby" cannot be used as the target.
• Global temporary tables can be possible in dataguard environment

Statistics Enhancements:-

• CTAS and INSERT … SELECT automatically compute stats.
• Global temporary tables can have session level statistics
• Some dynamic sampling operations are persistent, so they are not lost when the SQL is aged out.
• Hybrid histograms. When the number of distinct values is greater than 254, “almost popular” values can get “lost” in the mix. A single bucket can now store the popularity of than value, effectively increasing the number of buckets, without actually increasing it.Possible the max number of buckets can be increased based on a parameter. 

  Optimizer Enhancements:- Source optimizer blog

Oracle database 12c is the first step on the way to making an adaptive, or self-learning optimiser.

Alternative subplans are precomputed and stored in the cursor, so no new hard parsing will be needed as part of the adaption of an already executing plan. Statistics collectors are included in the plan execution. If the collectors cross a threshold, the plan might switch during execution from a nested loops to a hash join.

You can see information about the adaptive actions that have occurred using the DBMS_XPLAN package, with the format of “+all_dyn_plan +adaptive”. If a plan has been adapted, you will see it indicated in the v$sql.is_resolved_dynamic_plan column.

If this functionality scares you, you can turn it off using the OPTIMIZER_APADPTIVE_REPORTING_ONLY parameter. Same work is done, but no actual adaptive action is taken.

During parallel execution, collectors can influence the distribution method (HASH > Distribution). Shown in the plan as the HYBRID HASH operations.

Dynamic statistics replace dynamic sampling. The resulting stats are cached as SHARED DYNAMIC STATS specific for the statement, including the bind values. This information is used for any session using the same statement.

Cardinality feedback can be used to re-optimize subsequent operations. Join statistics are monitored. Works with adaptive cursor sharing. Persisted on disk. New column v$sql.is_reoptimizable shows that a subsequent run will take this into consideration. Collectors are kept, even if the SQL statement is killed part way through. The plan shows that cardinality feedback is used.

SQL Plan Directives are based on a SQL phrase (a specific join) rather than the whole statement. Cached in the directive cache, but persisted in the SYSAUX tablespace. Managed using the DBMS_SPD package.

Information gathered by the optimizer, may prompt automatic creation of column groups, so next time stats are gathered, the extended stats will be gathered.