Hi, We're frequently having some issues with Oracle database. Some queries take (too) long to return a result to Mendix. Some queries are heavy but others not so much. We setup a new environment including a dedicated Oracle Database. We now experience performance issue we don't have on existing database. We want to optimize the Oracle Database but there are a lot of settings in Oracle. Is there and existing / advised list of Oracle parameters / settings specific to optimize performance for Mendix? Screenshot of subset of parameters in Oracle Enterprise Manager, just for reference where we want to optimize the Oracle settings.
We performed several additional test and found out that when executing the Mendix generated SQL directly in Oracle (via PLSQLDeveloper) the first time took 0.2s and the second time 800+ seconds, third, forth etc took about 0.2s again. After some googling we suspected that this could be caused by a cardinality issue in Oracle.
It should be possible to avoid this by using the hidden setting: _OPTIMIZER_USE_FEEDBACK' = 'FALSE' but we were not able to find / edit this in Oracle 18.104.22.168.0.
Apparently simular result can be achieved bij changing setting below: optimizer_adaptive_reporting_only parameter = TRUE We tried this and it works for us. We are not sure yet whether this has impact on other queries