Skip to Main Content
  • Questions
  • Migration from 11g to 12c change execution plan(Adaptative plan)

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Pablo.

Asked: November 15, 2018 - 2:16 pm UTC

Last updated: November 16, 2018 - 4:25 pm UTC

Version: 12.2.0.1

Viewed 1000+ times

You Asked

Hi, we are working on a PeopleSoft Migration and Database too.
We're migrating Oracle 11.2.0.3 to 12.2.0.1, so we have an issiue with a PeopleSoft Query.

The query on actual database enviroment(11.2.0.3), have a excecution plan with minimal cost and return results on 9 secs., is a select count(*) from a standard view, so, the same query on new enviroment (with same data) generate another execution plan with high cost and use Adaptative Plan because use statistics collector in one of its steps, then, return results on 19 Min!!.
The first problem is that this query using literals, but i need to know how works Adaptative plan and statistics collector, because all the tables including on a view have updated statistics.

Is Adaptative Plan a good solution? or in some cases generates performance problems???

Note: i' think that the problem is related to Adaptative Plan but i'm not 100% sure

Update: i've disables Adaptative features at session level:

ALTER SESSION SET OPTIMIZER_ADAPTIVE_FEATURES = FALSE;
ALTER SESSION SET OPTIMIZER_ADAPTIVE_PLANS = FALSE;

But i still with the bad execution plan....so the issue is elsewhere.

Update2: I've used a hint to test:/*+ opt_param('optimizer_features_enable' '11.2.0.4') */ and the execution plan changed and is better, so the query responds on only 9 secs (the same time as in the current production environment).
The problem is that i can't use hint because the query has a literals....a big problem.....

and Chris said...

The statistics collector step in adaptive plans allows the optimizer to choose between nested loops and a hash join at runtime.

Displaying the adaptive steps does lead to plans that look very different. But that doesn't mean this is the cause of the slowdown.

To investigate, get the plans for the fast and slow versions of the queries. Without the adaptive options. You can remove these with the -ADAPTIVE option.

For example:

set serveroutput off
alter session set statistics_level = all;

select <your_query>

select * 
from   table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST -ADAPTIVE'));


Show us the plans you got by doing this.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

We've noticed the same thing...

Mitchell Ryan, November 21, 2018 - 9:10 pm UTC

We've notices the same thing about the queries running much faster with the optimizer set back to 11.

There is some question about the appropriateness of doing this based on:

https://blogs.oracle.com/upgrade/why-you-shouldnt-set-optimizerfeaturesenable

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.