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?
More“Adaptive Query Optimization: SQL Plan Directives Causing High CPU after 12c Upgrade”