Oracle :: 12C … The new horizon!

Prologue …

Oracle 12C was officially released on 25th June 2013, and is now available for downloads at OTN. The documentation cab be found here.

Note: You can download it entirely from here.

Here’s a brief of what I have learnt about it from various reviews and experiences of some of the best DBA’s around and also from those wonderful people from Oracle who are regular bloggers in blogs.oracle.com.

Note: I would be writing more on 12c as I slowly begin exploring various areas of it. By the time I complete reading the fine manual, I believe I would have more blog posts on 12c and its pros and cons.

Before I even start,

  • Oracle has provided us with its interactive Learning Library  for Oracle 12c, which is accessible here.
  • Tim Hall is already onto it, and he has written handful of very very information and detailed articles on 12c installation and other areas of it, including the Enterprise Manager. You can find them here. For a quick start installation procedure on Linux 5, look here.
  • Video: Tom Kyte speaks about 12c with Chet Justice a.k.a Oracle Nerd here.
    • Or, watch it below ..!

 

Getting in …

Features that caught my attention:

1. Optimizer Enhancements:

1.a. Adaptive Query optimisation:

By far the biggest change to the optimizer in Oracle Database 12c is Adaptive Query Optimization. Adaptive Query Optimization is a set of capabilities that enable the optimizer to make run-time adjustments to execution plans and discover additional information that can lead to better statistics.

1.b. Adaptive Statistics:

The quality of the execution plans determined by the optimizer depends on the quality of the statistics available. However, some query predicates become too complex to rely on base table statistics alone and the optimizer can now augment these statistics with adaptive statistics.

In Oracle Database 12c dynamic sampling has been enhanced to become dynamic statistics. Dynamic statistics allow the optimizer to augment existing statistics to get more accurate cardinality estimates for not only single table accesses but also joins and group-by predicates.

The parameter to look for in this case is: OPTIMIZER_DYNAMIC_SAMPLING.

1.c. Statistics Feedback, a.k.a. Cardinality Feedback:

Statistics feedback (formally known as cardinality feedback) is one form of reoptimization that automatically improves plans for repeated queries that have cardinality misestimates. During the first execution of a SQL statement, the optimizer generates an execution plan and decides if it should enable statistics feedback monitoring for the cursor. Statistics feedback is enabled in the following cases: tables with no statistics, multiple conjunctive or disjunctive filter predicates on a table, and predicates containing complex operators for which the optimizer cannot accurately compute cardinality estimates.

[ Something I was terribly missing in Oracle 10.2.0.5 below a PeopleSoft 9 release! ]

1.d. New types of histograms –> Top-Frequency & Hybrid:

Histograms tell the optimizer about the distribution of data within a column. By default, the optimizer assumes a uniform distribution of rows across the distinct values in a column and will calculate the cardinality for a query with an equality predicate by dividing the total number of rows in the table by
the number of distinct values in the column used in the equality predicate. The presence of a histogram changes the formula used by the optimizer to determine the cardinality estimate, and allows it to generate a more accurate estimate. Prior to Oracle Database 12c, there were two types of histograms,
frequency and height balance. Two additional types of histogram are now available, namely, topfrequency and hybrid histograms.

1.e. Online Statistics Gathering:

When an index is created, Oracle automatically gathers optimizer statistics as part of the index creation by piggybacking the statistics gather on the full data scan necessary for the index creation. The same technique is now being applied for direct path operations such as, create table as select (CTAS) and
insert as select (IAS) operations. Piggybacking the statistics gather as part of the data loading operation, means no additional full data scan is required to have statistics available immediately after the data is loaded.

1.f. New reporting subprograms in DBMS_STATS:

In Oracle Database 12c, new reporting subprograms have been added to the DBMS_STATS package to make it easier to monitor what statistics gathering activities are currently going on and what impact changes to the parameter settings of these operations will have.

These are:

REPORT_STATS_OPERATIONS
REPORT_GATHER_*_STATS

What about a complete PDF with more details on the above Optimiser enhancements and sample queries? Find it here … and here again!

2. Pluggable databases –> Now we’re talking CLOUD!! [ This is a optional and separately buy-able feature!! ]

Consolidation is an important business strategy to reduce the costs of infrastructure and operational expenses. In many production database servers, a big portion of CPU cycles go unused. By consolidating many databases into fewer database servers, both the hardware and operational staff can be more effectively utilized.

Oracle’s new pluggable database feature reduces the risk of consolidation because the DBA can easily plug or unplug an existing database to or from a container database. There is no need to change any code in the application. When the user connects to a plugged database, the database environment looks exactly as if the user had connected to a traditional database.

Further,

a. pluggable databases do lower resource consumption.
b. Memory and processes are owned by the container database and shared by all pluggable databases, improving resource usage overall.
c. It is also easy to unplug a database and convert the pluggable database to a traditional database if required.
d. In addition, you can back up and recover pluggable databases independently of the container database; you can also perform a point-in-time recovery of a pluggable database.
e. Further, Resource Manager can be used to control resources consumed by a pluggable database.
3. Concurrent execution of union [Okay, we are back again to the optimizer — but this is interesting !!]

Traditionally, queries with union or union all branches execute one after another, meaning that one branch of the union or union all is executed, followed by the next branch, and so on. Version 12c introduces concurrent execution of union branches, meaning that one set of parallel servers will be executing one branch, a second set of parallel servers will be executing a different union all branch, and so on, all at the same time.

This concurrent execution feature will be very useful if the majority of the query execution time is spent outside of the database, such as when waiting for a SQL*Net message from a remote database link or for an SOA call response. The effective use of this new feature could reduce wait time dramatically, improving SQL elapsed time. (Incidentally, with Version 12c, SQL*Net packets can be compressed for database traffic, helping to reduce latency in a WAN environment.)
4. Restore a TABLE easily !! [Wait – there are some cons as well!]

Traditionally, restoring a single table is a cumbersome process involving a tablespace restore, exporting the restored table from the restored tablespace, and then importing to the production database. The new restore table command in Recovery Manager (RMAN) simplifies this task.

A command, like the below one, can be used in such cases:

RECOVER TABLE TEST_TABLE
UNTIL TIME “to_date(‘2013-06-26:14:45:00′,’YYYY-MM-DD:HH24:MI:SS’)”
AUXILIARY DESTINATION ‘/tmp’
DATAPUMP DESTINATION ‘/tmp’
DUMP FILE ‘tst_dump2.dmp';

From the log files, it seems it processes the above command in the below manner:

  1. Create an auxilliary instance and load it (restore and recover) with the most recent backup of the database from where we have “lost” the TEST_TABLE.
  2. Again, restore and recover this aux instance to the time specified above in UNTIL_TIME clause.
  3. Opening the auxilliary database after restore completes above. All files of this auxilliary database is kept in /tmp, as mentioned in the option AUXILLIARY DESTINATION in the RMAN command above.
  4. Take a datapump export (EXPDP) of the lost table TEST_TABLE in /tmp, as mentioned in the DATAPUMP_DESTINATION option with dump file name equivalent to DUMP FILE option, both specified in the above RMAN command.
  5. Shut down the auxilliary instance.
  6. Perform an IMPDP of the TEST_TABLE to our main instance.
  7. Finally, remove the auxilliary instance and all its files from /tmp.

Note: I am yet to go through the documentation of 12c entirely, but this looks to be a bit time and space consuming. I’d dig into this further. However, this is quite an easy method of Table restoration in 12c.

Note again: Steps 1 and 2 are restoring + recovering the mandatory tablespaces (SYSTEM,UNDO, etc) and only the tablespace from where the table TEST_TABLE is lost, and not the entire database!
5. Parallel (Rolling) Upgrades:

In highly available environments, keeping the database down for a database upgrade, even for a few hours, is cost-prohibitive. Rolling upgrades are not always possible for major database software upgrades, and it is always a risky adventure to tune Database Upgrade itself. Version 12c uses parallelism to improve the database upgrade to reduce upgrade-related downtime.

The meta-list of information!

Taken from Steve Karam, here is as of now one of the most comprehensive list of what has already been discovered in Oracle 12C in the Oracle Guru’s zone!

Official Oracle Links

Oracle 12c Articles from the Community

Category Article Author
New Features 12c New Features from Oracle Base Tim Hall
New Features Pluggable Databases — An extra charge option Chris Kanaracus
Performance Optimizer Details from Oracle Optimizer Maria Colgan
Installation Upgrade, Migrate, and Consolidate to Oracle 12c slide deck Mike Dietrich
Installation Oracle 12c Silent Installation Daniel Westermann
Internals 12c Parameters Marco Gralike
New Features DB Express, the 12c replacement for DB Console Marco Gralike
New Features Getting Started with Oracle 12c DB Express Marco Gralike
Installation OPatch in Oracle 12c Bobby Curtis
Internals Changing VARCHAR2 to 32k Bobby Curtis
New Features The death of Advanced Replication and Streams Bobby Curtis
New Features 12c licensing first look Paul Bullen
New Features New features in EXPDP Osama Mustafa
Internals Moving datafiles online Osama Mustafa
Installation Queryable Patch Inventory (QOPatch) Osama Mustafa
New Features Security Features of Oracle 12c Osama Mustafa
New Features Enable DB Express Osama Mustafa
Internals Generated as Identity Columns (Sequence Replacement) Osama Mustafa
New Features Restore a single table with RMAN Raheel Syed
New Features RMAN now supports SELECT statements Wissem El Khlifi
Tools More details on single table restores in 12c RMAN Wissem El Khlifi
Tools RMAN Multisection Image Copies Wissem El Khlifi
Tools New ASMCMD commands Wissem El Khlifi
Internals Invisible Columns Wissem El Khlifi
Video @oraclenerd interviews Tom Kyte about 12c at KScope13 Chet Justice
Video @oraclenerd interviews @leight0nn (Leighton L. Nelson) about his gutsy 0-day 12c demo Chet Justice
New Features Collaborate 2013 Oracle 12c Whitepapers IOUG
New Features 5 reasons to upgrade to Oracle 12c Samer Forzley
New Features Top 10 New Features Paulo Portugal
Installation Oracle 12c on Solaris Mike Gerdts
New Features Some goodies to start with Aman Sharma
New Features Capture privilege usage Biju Thomas
New Features In-Line PL/SQL functions in SQL queries Lucas Jellema
Tools Multitenant Options and Application Express Jason Straub
Installation Make your own VM training/lab environment for $900 Steven Karam
New Features How long can I remain on Oracle 11g? Steven Karam
Installation Upgrade Oracle Grid Infrastructure (GI) from 11.2.0.3 to 12.1.0.1 online Ronald Rood
Installation Exadata Patching in Oracle 12c Robert Freeman
Tools XMLDB is mandatory in Oracle 12c Marco Gralike
Installation Oracle 12c RAC on your laptop Step by Step Yury Velikanov
New Features SQL developer and Multitenant (Pluggable DB) Jeff Smith
New Features Oracle Database 12c New Features Part 1 Syed Jaffer Hussain
More to come …

I’d dig further in, and see if I can find some more interesting features. This post would have their siblings in near future.

This entry was posted in Computers, database, experience, Oracle, Oracle DBA, technology and tagged , , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s