When planning on an Oracle database upgrade, to maintain software currency or utilise new features, there are a number of things that can be done to minimise the risk of poor Oracle database performance after the upgrade.
If there are existing performance problems make sure to have a good understanding of what they are from a technical and non-technical perspective. Fix the low hanging fruit prior to the upgrade, or alternatively during the Oracle database upgrade project.
Running on the latest Oracle releases can provide new functionality that can significantly reduce the cost and complexity to resolve existing Oracle performance issues.
We have included a few points to keep in mind when planning to upgrade to the latest Oracle database releases.
Identify Poorly Performing Business Processes
- Using the techniques covered here to identify the slow business processes that are dependent on the database platform.
- Create a baseline measurement of these processes before the upgrade. This can be elapsed time for the process or, for a more detailed approach, capturing the SQL execution statistics and execution plans if relevant.
- The problem processes identified are the ones that can be tested and validated first after the upgrade.
This is a good way to determine if the database upgrade will have a positive, neutral or negative impact on the problem processes.
Fix Inefficient SQL Query Plans
Fixing inefficient SQL query plans either before or during the Oracle database upgrade project can be a major contributor to the upgrade’s success.
A few things to consider when approaching this:
- Remember to lock down the query plans with SQL Plan Management baselines once they are resolved.
- Revisit database object statistics collection strategies to ensure that there are no statistics gaps from stale objects.
- Oracle 12c has some major enhancements to improve query performance for a wide range of workloads. Whilst many of the features aim to address administrative and application development bad practices – your technical teams need to be on top of how the new functionality works to have the best results.
- There are plenty of excellent resources available that explain the new features and how to manage them.
Look at Opportunities to Isolate Database Workloads
If your Oracle database infrastructure platform is due for a refresh, upgrading your Oracle database can be a great opportunity to deliver significant capabilities into your Oracle platform for future growth.
Some things to keep in mind when planning your Oracle database upgrade and infrastructure platform:
- Database upgrades provide a great opportunity to consolidate compatible workloads into a reduced infrastructure footprint.
- Features such as Oracle Multitenant introduced in Oracle 12c facilitate database consolidation (and enable a level of agility simply unavailable with alternate technologies).
- Deploying pluggable databases on an isolated infrastructure platform can be a great way to isolate database workloads as well as consolidate licenses and operating procedures.
- Keep in mind the need to manage consolidated workloads – even on dedicated database infrastructure.
- Specific features available across the Oracle Database Resource Manager (DBRM), Oracle Multitenant and Oracle Exadata provide database engineers with a comprehensive toolkit to deliver database Quality of Service.
Mark Burgess has been helping organisations obtain the maximum value from their data management platforms for over 20 years. Mark is passionate about enabling secure, fast and reliable access to organisations data assets.