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:
- 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.
- Again, restore and recover this aux instance to the time specified above in UNTIL_TIME clause.
- 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.
- 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.
- Shut down the auxilliary instance.
- Perform an IMPDP of the TEST_TABLE to our main instance.
- 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
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.
Suddhasatwa's 