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 19c 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.
Upon completing your upgrade, make sure that any of your previous SQL Plan Baselines exist in your upgraded production environment. When upgrading via TTS or Data Pump it is 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.
Looking for help to fix your Oracle Database 19c performance problems? Get in touch with us today.
FAQ
How to accurately migrate SQL Plan Baselines to Oracle 19c?
Migrating SQL Plan Baselines accurately to Oracle 19c involves several steps to ensure that the execution plans which your applications depend on remain stable post-upgrade. First, before the upgrade, you should capture the existing plan baselines from your current Oracle database version using the DBMS_SPM package to export them. After the upgrade, you can then import these baselines into the Oracle 19c environment. It’s crucial to verify that the plans are enabled and accepted for use by the optimiser. Additionally, after the upgrade, monitor the performance of queries to ensure they are using the intended plans, and if necessary, adjust the baselines using the DBMS_SPM package to evolve and adapt to the new version’s optimiser behavior.
What are the best practices for gathering or importing optimizer statistics in Oracle 19c to avoid performance issues?
Best practices for gathering or importing optimiser statistics in Oracle 19c include ensuring that the statistics are representative of your data and workload. This can be achieved by gathering fresh statistics right before the upgrade if the data distribution has significantly changed. For large databases where gathering statistics on the entire database might be time-consuming, consider using incremental statistics gathering or gathering statistics on highly accessed tables. Oracle also offers the DBMS_STATS package, which can be used to export statistics from the previous database version and import them into the new Oracle 19c environment. It’s essential to regularly gather statistics post-upgrade to maintain optimiser efficiency, especially for volatile or rapidly changing data.
How to effectively manage PGA memory in Oracle 19c to enhance performance?
Managing PGA (Program Global Area) memory effectively in Oracle 19c involves monitoring and adjusting the PGA to optimise the memory allocated for sorting, hashing, and other memory-intensive operations. The PGA_AGGREGATE_LIMIT parameter is a key factor in controlling the total PGA memory usage, preventing excessive consumption that could impact the database server’s overall performance. Additionally, the PGA_AGGREGATE_TARGET parameter can be adjusted to provide a target for Oracle’s automatic PGA memory management, optimising the distribution of memory among active sessions. Regularly monitoring the V$PGA_TARGET_ADVICE view can provide insights into how changes in the PGA_AGGREGATE_TARGET value might affect performance, allowing for informed adjustments to be made based on actual workload patterns.
