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