Mark BurgessYou have spent months planning, testing and preparing for your Oracle 19c upgrade. The upgrade goes to plan and you are live on Oracle’s latest and greatest.
And then Monday morning comes around.
Users are unable to perform their work. Jobs are not completing.
Very quickly the sense of achievement of completing the production upgrade is replaced by critical incident meetings, and the stress of your business critical applications grinding to a halt.
Sound familiar?
If you are in this situation the following tips can help you move forward with fixing your Oracle 19 performance issues after upgrade. If you are planning an upgrade, keep in mind the following tips when testing your upgrade procedures.
Note: If you run Oracle eBusiness Suite the Oracle 19c Upgrade for eBusiness Suite – Lessons Learnt might be of interest as well.
Query Plans
Bad query plans are a common cause of many post-upgrade pains.
Make sure that any of your previous SQL Plan Baselines exist in your upgraded production environment. Upgrading via TTS or Data Pump it’s possible to miss migrating the existing SQL Baselines during the upgrade.
If you have Stored Outlines make sure they are migrated to SQL Plan Baselines as a part of your upgrade activities. This especially applies to Oracle Standard Edition which allows for SQL Plan Baselines from Oracle Database 19c.
If you are having problems with plan stability check if your application vendor has recommendations on the following parameters:
- optimizer_adaptive_plans
- optimizer_adaptive_statistics
Alternatively, verify that they are set to the default values for Oracle 19c.
Whilst the Adaptive Plans feature can be very useful, we want to favour stability over functionality after you go live with your production upgrade. Particularly if you are fire-fighting SQL plan stability problems.
Optimiser Statistics
Make sure that you have a representative set of statistics for the database objects at the time of go-live. Don’t wait on the optimiser statistics auto-job to run in the next scheduled interval – which may be many hours away (and many hours of SQL problems compounding on top of each other).
Either gather representative stats directly after the upgrade, or import a known good set of statistics from a test environment as part of your post-upgrade activities.
If you are working through query plan problems as a result of stale statistics, take a look at the High-Frequency Automatic Optimiser Statistics Collection feature. This can be very useful if you are having to maintain statistics on tables that frequently become stale.
Keep in mind that Oracle Database 19c collects table statistics during a CTAS. Whilst this can be very useful in avoiding having to collect stats after a CTAS – keep in mind that any large DML on the table may render the stats stale again. This is particularly applicable to data warehouse environments during ETL phases.
Memory Resources
Oracle Database 19c memory configuration parameters will be dependent on site specific workloads.
PGA memory management does require consideration or review if you are upgrading, or have upgraded and experiencing performance issues.
In particular changes to the way that PGA memory is managed from Oracle Database 18c can impact memory consumption on your database server. The PGA_AGGREGATE_LIMIT parameter settings can affect the performance and stability of your database server.
The changes in summary are:
- PGA_AGGREGATE_LIMIT was introduced in Oracle Database 12c.
- Sessions can be killed if they are using too much memory over the PGA_AGGREGATE_LIMIT value.
- Database components have been making more use of memory that consumes from PGA_AGGREGATE_LIMIT – particularly from Oracle Database 18c.
- In general PGA memory consumption can be expected to increase when upgrading from earlier database releases to Oracle Database 19c.
If you are seeing random database sessions being killed, or your database server comes under memory pressure after upgrading to Oracle Database 19c then have a read through MOS Note # 1520324.1.
Auto Jobs
Oracle Database schedules automatic jobs to gather object statistics, analyse segment space usage, analyse and provide tuning recommendations for high load SQL statements.
These jobs can introduce additional workload to your database platform when they run. If you are facing multiple performance issues after upgrading to Oracle Database 19c you want to minimise unplanned changes and reduce unnecessary workloads as much as possible.
If you don’t regularly use the output from the Auto Space Advisor or the Auto SQL Tuning Advisor then consider disabling the jobs from running.
For the auto optimiser stats job check whether the database objects are being considered stale enough to have the stats updated. If you are facing significant SQL execution plan problems due to insufficient stats, consider disabling the auto stats job until you have the system stable. Stats can always be collected on a copy of production and imported into the prod database to provide complete control over how and when stats are collected.
Summary
Oracle Database 19c has some great features to help improve the performance of your database platform. Once you have defined your performance problem, the tips above can give you some ideas of things to keep in mind for Oracle Database 19c.
