Skip to Main Content
  • Questions
  • Performance issues after upgrade to 19c

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Fredrik.

Asked: April 28, 2022 - 1:39 pm UTC

Last updated: May 11, 2022 - 5:33 am UTC

Version: 19.14

Viewed 100+ times

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

  (2 ratings)

Comments

Performance

Fredrik Björklund, May 03, 2022 - 6:39 am UTC

Thank you
We did as you said, made reset on all parameter we experimented with.

After that we used SQLT and cud see that the first time the query was running it used adaptive_plan and next and rest times it used cardinality_feedback.

So now we have set the parameter _optimizer_use_feedback = false and we see executions plans more similar to oracle11c and the problem described in our question is solved. However, we still have some other SQLs that is running slow so we still have some more tuning to do but we will look into the advice you gave above.

Best Regards Fredrik Björklund

Connor McDonald
May 11, 2022 - 5:33 am UTC

Glad this worked out and thanks for getting back to us

Mahesh, May 18, 2022 - 4:04 am UTC

Hi, were also in similar situation where 19c is queries are running longer compared to 11g.

Is the issue with how data is moved(export/import)?if yes then can you please guide how to move data from 11g to 19c?
Which parameters should be looked at?
Should we keep the parameters same as that of 11g?

More to Explore

Performance

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