Mark BurgessConfirming that you have an Oracle database performance issue is the first step in improving the process. Having defined the level of the problem you need to start solving it.
While the detail of Oracle database performance optimisation can be complex, there are three overarching approaches that you need to review.
1. Tune Inefficient SQL for Better Oracle Database Performance
Tuning the application SQL to use the proper query plan is the fastest, least risky and most cost effective way to resolve Oracle performance problems.
Some tips for doing this effectively:
- Identify and target the SQL running for the problem only from the user’s perspective. This needs to be something along the lines of “when I open my Accounts Payable enquiry form at 09:45 on a Wednesday morning it takes 35 seconds to open – this needs to be 2s”. Until you have this level of detail keep on working with the users to define the problem.
- Trace and measure the specific database session using standard tracing tools to identify the SQL statistics, execution plan and wait time cost.
- Target the tuning efforts to the particular statements that are consuming the largest response time for the particular function.
I can not overstate how important it is to correctly define and measure system performance problems before any attempts are made to address the issues. Do not waste time “attempting” to optimise your databases without having very well defined problem statements and measurements.
2. Improve Storage Performance
When you have exhausted all SQL tuning opportunities one option is to consider the storage platform on which the the database runs. Reviewing storage options for your Oracle database server can be a cost effective approach to removing bottlenecks.
For instance flash storage technology can offer significantly higher bandwidth and reduced latency to IO bound workloads.
Targeted provisioning of flash storage for Oracle database can provide significant performance improvements without having to refresh your entire database infrastructure. You can place specific database objects on flash storage that have the most performance benefit – leave the remainder of the database on conventional storage.
Also, analytic queries that process large amounts of data can require huge amounts of sorting before the query completes.
Performance of analytic queries – or any query that processes large volumes of data – can be highly dependent on storage performance. Locating Oracle’s temporary tablespace on flash storage is a cost effective opportunity to reduce the query response time.
There are many options to consider here. The main takeaway being that a specific, targeted approach can be taken when dealing with storage related performance problems. There may not be a need to replace the entire storage platform that your Oracle database is running on to deliver considerable performance gains.
3. Provide Dedicated Database Infrastructure
In some cases, regardless of the tuning efforts applied, poor system performance is a result of other systems on a common infrastructure platform.
The most efficient way to deal with this is to isolate the Oracle database workloads on to dedicated infrastructure.
While the direct cost of this approach is additional infrastructure and associated support costs, this can be offset by the following benefits:
- No noisy neighbour impact on Oracle database IO response times.
- More predictable performance for database workloads.
- Potentially a reduced infrastructure footprint and more concentrated use of your Oracle software licenses.
- Simplified management, monitoring and security.
Attempting to measure database performance issues on consolidated infrastructure across multiple vendor technologies is a time consuming and difficult exercise.
Whilst performance, capacity and scalability are headline features of the Oracle Exadata platform – it is our experience that one of the major benefits of deploying Oracle Exadata is the ability to measure, diagnose and optimise critical workloads in isolation.
In many cases this diagnosis can be completed in a fraction of the time on Oracle Exadata compared to alternative platform solutions.
Resilient and scalable database platforms require investment. When evaluating the costs of different approaches consider the indirect cost of poor performance when evaluating your options.
Will Upgrading my Oracle Database Improve System Performance?
In some cases upgrading the Oracle database to the most current release can assist with diagnosing and resolving poorly performing processes. There have been major advances in Oracle 12c around SQL performance and query plan management.
Running your business applications on older versions of an Oracle database can limit your ability to identify and fix performance problems for your users.
Such upgrades are not without their risks but can be provide substantial performance improvements.
When considering whether to upgrade to Oracle 12c keep in mind the following:
Significant Database Query Optimiser Enhancements
Oracle 12c database optimiser contains a number of significant enhancements to improve the performance for SQL queries:
- Oracle Database optimiser can change the query plan during execution if the current query is sub-optimal.
- Statistics are collected by the database during query execution to aid with selecting the correct query plan.
Used correctly these features can help deliver consistent and predictable performance for your users.
Like anything – the correct tool needs to be used under the correct circumstances to get the best results. Familiarity with the new database behaviour should be required reading for database engineers supporting a Oracle 12c upgrade.
Improvements and Major Enhancements
Upgrading to the latest releases of Oracle database improves existing functionality and offers major enhancements to Oracle database options.
There are many new features in Oracle 12c that can significantly improve performance for problematic workloads. The introduction of Approximate Query Processing for analytic queries is a great example of this.
When evaluating your Oracle database upgrade options keep in mind:
- Oracle constantly enhances the core database functionality to improve performance.
- Oracle 12.2 offers refinement of the automatic database optimiser features introduced in Oracle 12.1 to allow for more selective use.
- Upgrading from Oracle 12.1 to 12.2/18c offers major enhancements to Oracle InMemory and Oracle Multitenant options.
Availability of new Oracle Database Options
Oracle database upgrades provide the opportunity to implement new Oracle Database options that are not available on earlier releases.
In particular Oracle 12c introduced Oracle InMemory and Oracle Multitenant options. Both of these features provide significant opportunities to support your Oracle database performance objectives.
Implementing certain Oracle database options may help with performance issues that can not be resolved cost effectively by other means.
Keep in mind that in most cases implementing Oracle database options is transparent to your applications and typically do not require any code changes.
