As DBAs we are always being recommended by Oracle and also recommending to clients to update their databases, but we have to be aware about new features and their effects. This is the case of Adaptive Query Optimization and in this particular case on SQL Plan Directives.
SQL Plan Directives are one of the functionalities that compose the Adaptive Query Optimization in Oracle Database 12c. The basic idea is pretty interesting: The SQL Optimizer keeps reviewing bad (“suboptimal”, as they like to say) plans, tipically incorrect cardinality estimations and generates SQL plan directives, like for missing histograms or extended statistics.
In my case, just after the upgrade to 12c (made on Jan 27th), the CPU usage increased for the same report always ran in the database:
Ok, how to check it?
Ok, now it’s everyone upgrading to 12c, right? Thanks God, this version was released in 2013!
But there is some things to be aware when planning an upgrade, specially regarding old applications and legacy. But pay attention! Not all of the requirements are necessary inside database. It’s the case os JDBC version requirement.
The database 12c documentation explicit mentions that JDBC versions 11.1.x and below are not supported anymore. It doesn’t mean that they don’t work, it’s only unsupported and you’ll have no assistance from MOS if you need. It’s better to avoid, right?
Anyway, if you check the JDBC support matrix, if you are in version 11.2 or below you are not supported since August/2015. So the Database 12c is helping you, that don’t have patching policy, to keep on right way. Thanks to Database 12c!
If this is your situation, I highly recommend you to upgrade the directly to JDBC version 7, the last available by now. See JDBC matrix version as:
But test! Test in you dev/test/QA environments before upgrading in Production environment!
Why? Because JDBC also have his compatibility matrix. JDBC 7, for example, demands your JDK to be at least in version 7 (released in 2011!). So, it’s needed to be at least in JDK version 6, as you can see below.
(Click in the image to access the link)
Some interesting links for you:
Verifying a JDBC Client Installation
What are the various supported Oracle database version vs JDBC compliant versions vs JDK version supported?
Checking the Oracle JDBC Driver Version on a Weblogic Server (by Cristóbal Soto)
A little old, but I felt the pain in my heart… You will feel it too, I think…
We already have faced situations like this to big envs, specially this migration from 18.104.22.168 to 11gR2… Latch mutex including…
It’s long, but it’s good.