Thanks for the question, Fredrik.
Asked: April 28, 2022 - 1:39 pm UTC
Last updated: November 29, 2024 - 12:05 am UTC
Version: 19.14
Viewed 10K+ times! This question is 
You Asked
Hi, we have run into performance issues after upgrading to Oracle 19c (19.14). For some SQL query’s the optimizer will no longer create execution plans similar to them in Oracle 11g (11.2.0.4) resulting in extreme slow performance (up to a factor 1000 times).
The update was done by performing new installation of ora19c (19.3), then moving data with expdp/impdp from Ora11g and last installation of the patchset 19.14.
We run statistics in the database regularly as well as dictionary stats
One thing we noticed in the SQL query’s with these performance issues, is that the optimizer now in 19c choose "HASH JOIN" instead of "TABLE ACCESS BY INDEX ROWID" and
"HASH GROUP BY" instead of "INDEX RANGE SCAN".
We tried experimenting with the below settings to make it less happy with hash join, but with no luck.
*._gby_hash_aggregation_enabled
*._hash_join_enabled
*._optimizer_nlj_hj_adaptive_join
*._optimizer_sortmerge_join_enabled
We also tried activating optimizer patches after new RU installation.
exec dbms_optim_bundle.enable_optim_fixes('ON','BOTH','NO');
We have seen the optimises, perhaps, one time after each shutdown/restart, pick the same execution plan as in ora11g resulting in same performance as ora11g, but next runs and the rests are with the not optimal execution plan using HASH JOIN.
Most of our users experience faster performance or same performance after upgrade. It is only a couple of advanced SQL views, as we know so far, that have these problems.
We believe we have enough hardware, more than in the old server and we cannot see any problem with the read/write on disk, and as mentioned above, it have picked the “optimal” plan a couple of times.
Best Regards Fredrik
and Connor said...
The big challenge here is that you did export/import. So you've basically reorganised all of the data structures in the database. All the table rows are no longer where they used to be, all the indexes are no longer how they used to be. It is now essentially a totally different database. Even if you had gone from 11.2 to 11.2, you most likely would have seen changes (some better, some worse) in performance.
If you still have the old database, you can grab plans from that database via SQL Plan Management (SPM) and import them into the new database to fix those plans in place. If not, then you'll need to do some good old fashioned SQL tuning and once you have a good plan, use SPM to lock those plans as well.
One thing I would strongly recommend is - revert your parameters to defaults and then tune from there. The moment you start messing with parameters, the problems become far harder to diagnose and resolve, because now you have a "custom" implementation of the database. Even things like the *gathering* of stats - those APIs now have new defaults which improve the quality of the stats - if you've tinkered with the parameters back in the 11g days, make sure you use the default calls from 19c. Otherwise you can end up with old-style histograms etc, which can make a mess of optimiser plans.
Finally, with reference to: "pick the same execution plan as in ora11g resulting in same performance as ora11g, but next runs and the rests", that sounds like you might have full adaptive optimizer enabled which also is *not* the default. The defaults are below:
SQL> show parameters optimizer_adaptive
NAME TYPE VALUE
------------------------------------ ----------- ----------
optimizer_adaptive_plans boolean TRUE
optimizer_adaptive_reporting_only boolean FALSE
optimizer_adaptive_statistics boolean FALSE
Rating
(3 ratings)
We're not taking comments currently, so please try again later if you want to add a comment.