Hello,
we migrated Infor LN data from Oracle 12.1 to the version 12.2 using expdp and impdp utilities. No errors occurred during migration. Infor is running. Users can work.

BUT!

We have big performance issues with Generate Order Planning session (cprrp1210m000 ). It took about 2 hours in 12.1 and 8 hours in 12.2. Another example with a filter in the form (only one planner - i.e. less data) - 7 minutes in old version of DB and 44 minutes in current version 12.2. The entire planning cycle took about 12 hours before migration and now about 24 hours...

Other sessions are also affected. Especially those that work with large tables, such as the BOM (12M rows) tibom010, tirou1202m000...

We've already tried and didn't help:
- Update DB statistics
- Set OPTIMIZER_ADAPTIVE_STATISTICS to TRUE
- Set OPTIMIZER_ADAPTIVE_PLANS to FALSE
- Recreate indexes using Infor

Any ideas, please?

What is your database block size in oracle 12.2

What is your database block size in oracle 12.2

Hi, the size is 32768 in 12.2 and in 12.1 too...

Hello Jasper,

when moving to Oracle 12.2 (With SLES 12) some month ago we also had terrible performance problems with several sessions, caused by FULL TABLE SCANs coming from the generated Oracle execution plans.

In our case setting parameter optimizer_features_enable='11.2.0.4' in the db_resource did solve most of that problems.

See also KB 1986485 "Performance problems after upgrading Oracle to version 12.2" on Infor Support Portal

Regards,
Juergen

Hello Juergen,
thanks a lot, your solution solves our problem. Everything works perfect again.

We migrated from version 12.1, so our first try was set optimizer_features_enable parameter to 12.1.0.2. But it didn't help at all. Then, we tried to set this parameter to '11.2.0.4' and this helped.

ora_alter_session:set optimizer_features_enable='11.2.0.4'

Thank you very much again.