The IT Managers Tips to Oracle Database Performance Tuning

Despite significant advances in the Oracle database optimiser and the supporting platform infrastructure, we as IT Managers still see poor Oracle database performance across a diverse range of workloads.
There are well documented methodologies and tools available to diagnose and resolve Oracle performance problems.

What we don’t see much of are the high-level steps an IT Manager can take to identify causes of poor Oracle performance and usable solutions to resolve Oracle performance problems.

In this post I’ll explore those high-level steps: common causes of poor performance, assessing your current situation and preventing issues in the future.

Links to key Oracle performance tips

1. The Most Common Causes of Poor Oracle Performance
2. Asses Current Oracle Database Performance
3. Preventing Oracle Database Performance Problems
4. How Fast Can You Go

The Most Common Causes of Poor Oracle Performance

Over the many years that we have been fixing customer performance problems, the common causes keep on appearing:

Inefficient SQL Query Plans

Inefficient SQL execution plans are, in our experience, the top cause of poor Oracle performance.
SQL query plans typically fall into two categories:

• those that should never change.
• those that can change depending on data volumes.

Oracle have provided significant enhancements to:

• Identify the query plan used for any SQL run by the database.
• Identify all the relevant information about the SQL.
• Manage query plans that are available to a particular SQL .

Inefficient SQL execution plans can be easily identified when combined with effective diagnosis procedures.
The good news is that, in many cases, the poorly performing SQL queries can be easily and quickly fixed so they are a prime target for early attention.

Download our performance diagnostic template to help start fixing your performance problems today. This is the same tool we use to help us fix customer performance issues.

The Unintended Consequences of Shared Infrastructure

Care must be taken when consolidating systems on common infrastructure. While modern technologies allow for large consolidation of disparate workloads on to common infrastructure, it can also play havoc with database performance.

Consolidating incompatible workloads on the same infrastructure as your mission critical Oracle databases can cause, and in some cases amplify, the “noisy neighbour” problem. Unfortunately, inadequately designed platforms can make it extremely difficult to isolate the root cause of performance problems when the shared infrastructure is heavily utilised.

Considering the potential costs of poor Oracle performance – the real cost of consolidation can significantly outweigh any direct cost savings of a reduced infrastructure footprint.

Also, the changes required to fix Oracle performance problems may be limited on a share platform that hosts other applications apart from Oracle. Configuration changes that benefit the Oracle database platform can adversely affect the performance of other applications on the same infrastructure.

The best approach is to properly consider the impact before making the move.

Problems Caused by Generic Application Schema Design

Many application vendors “abstract” the application code from the database platform. This allows the customer to deploy the application to the customers preferred database platform. However, the flexibility this deployment grants can come with a significant reduction in Oracle performance.

Inefficient SQL query plans, significantly increased resource consumption and corresponding issues typically don’t present themselves until the system has been in operation for some time. At which point they can become difficult and expensive to fix.

Again, once implemented, these issues are hard to overcome.

So when assessing applications, determine if the application vendor has a preferred database platform. This will provide guidance to how robust their porting efforts are across databases whether it be Oracle, SQL Server, MySQL or PostgreSQL.

Make sure you check customer references, ask for database specific configuration activities and documentation.
If the application vendor has one database platform but can “accommodate” your database platform – keep in mind that the application running really well on SQL Server – may mean that it doesn’t run so well on Oracle.

Asses Current Oracle Database Performance

Now that we have an understanding of the common causes of poor Oracle performance, here are some of the ways that you can gauge your Oracle performance based on business feedback.

User Feedback

Asking users about their experience with the system is the fastest and easiest way to gauge the real system performance, as long as your questions are specific.

General questions about user’s application performance provide vague answers, which make it hard to zone in on the root cause of the issue.

Instead, ask about specific experiences with the application – all with the aim of providing us with the targeted workload that we need to measure.

Here are a few example questions to demonstrate:

• “When selecting a customer invoice does the system return the details almost immediately?”
• “When generating the monthly sales dashboard do you have to wait for the report to display?”
• “When posting the ledgers does it complete within a few minutes?”

When discussing performance with application users try to get feedback based on the following:

• Based on facts and not emotion.
• Specific functions/reports/processes that the user says are too slow.
• Identify task workarounds being used by the user to complete their job function. e.g.  when users bulk export data into an Excel spreadsheet and work with the data inside a tool they are familiar with as “the native application performance is too slow”.
• When the problem occurs – down to the exact time and date. This is one of the single most important details to have in effectively fixing user performance problems.

I’ve put together a detailed process flow which I use to diagnose my client’s Oracle performance issues.

A few things to keep in mind when seeking feedback about Oracle performance problems:

• People want to get their jobs done. If there is a work around they can use, they will.
• Work-arounds themselves can result in causing performance issues. e.g. Just because someone can download data into a spreadsheet, it doesn’t mean that they are not going to cause another problem by doing so (let alone introducing data security issues by having the data stored outside the database).
• It can take time to build trust in the users that the problem will be fixed. The quickest way to build this trust is to listen, measure, and resolve one of their problems. Ideally this will be their largest problem – but fixing even a small problem for a user will go a long way in building trust and credibility.

Identify Organically Increasing Load

Successful IT deployments generate IT demand. When you have an IT platform that can accommodate business initiatives quickly and easily it will encourage demand for new workloads.
Unfortunately the growth of new workloads over time can contribute to overall poor system performance. So be aware of how far the existing deployment can grow before the platform is unable to meet the current and planned requirements.

Scenario planning can be useful here. Consider what would happen if your organisation increased order volumes by 10% every year. After year 3 your systems are processing 33% additional workload compared to year 1.

This can gradually slow down performance over time and there is no one single cause, but lots of small causes adding up to a larger performance problem.

On busy systems identifying the “poorly performing” function can be like finding a needle in a haystack. If you are not already, start benchmarking critical application functionality.

The Oracle database provides many ways to capture performance related metrics. Once you start building a history of how long your application processes take, you can look at trends and identify cause-effect relationships amongst changes.

You should start looking for trends and building a history using the following items:

• batch process elapsed times
• application login times
• critical database SQL query time.

There are many tools available to do this.

Once you have valuable metrics keep an eye out for batch processes that consistently increase in time. e.g. login times that take longer during certain times of the day and backup durations that are inconsistent.

It’s never too early or too late to start tracking this information.

Scalability Problems

Well designed and provisioned systems should be able to accommodate additional workloads without significant re-engineering. Plus, the capacity of modern infrastructure platforms is inherently scalable by adding additional compute, network and storage components as required.

So, if your Oracle database workload does not scale by adding additional infrastructure capacity it can be a key indicator of an underlying performance problem. Keep in mind as well – fixing database scalability problems typically reduces the infrastructure footprint of the database. The problems that are preventing your Oracle database workloads from scaling well are most likely causing poor performance for your users, require additional hardware, and require additional Oracle licenses.

If you find that adding additional CPU or storage capacity has not helped with performance – or in some cases – made it worse, these can be indicators of an underlying scalability problem with the application.

Well designed applications that make effective use of the Oracle database platform generally scale well when additional CPU or IO is added to a busy system.

Preventing Oracle Database Performance Tuning Problems

Now that you’ve resolved your users performance problems and have a fantastic Oracle database platform to accommodate new business growth and initiatives here’s how  you can avoid poor Oracle performance in the future.

Reduce Database Query Footprint

Always seek to reduce your application’s SQL workload. This is a very effective way to deliver consistent, predictable performance to your users. The faster a query runs, the less time it requires system resources to complete, reducing the time that it can be subject to poor performance.

What are some practical tips to keep in mind when targeting inefficient SQL?

• Optimise the critical SQL statements to ensure that they are consuming the least amount of CPU and IO resources . This also decreases adverse impacts to other workloads on the shared infrastructure.
• Consider database infrastructure refresh cycles. With organic system growth, more frequent infrastructure refresh cycles are more cost effective.
• Advances in hardware can significantly increase database performance. With modern infrastructure platforms re-provisioning server hardware should not require considerable effort.

Provision Isolated Oracle Database Infrastructure

Use dedicated infrastructure for Oracle database platforms.Typically the intangible benefits far outweigh additional direct cost.

• Shared infrastructure platforms introduce significant complexity to diagnosing database performance problems.
• Isolating database platforms on to a dedicated server and storage infrastructure can be a cost effective way to provide predictable performance and significantly reduce diagnosis complexity.
• Dedicated database infrastructure can provide further software license optimisation.

Consider Database Options

Oracle Database options such as Advanced Compression, Partitioning, Multi-tenant and InMemory all address specific use cases.

Implementing Oracle Database options can be a cost effective way to prevent Oracle database performance problems. While they attract additional license cost, on balance they can be more cost effective than alternative options.

Make sure you consider the total alternative licencing and system cost of, scaling or resolving performance problems through adding CPU’s to the database servers.

A few things to keep in mind when considering Oracle database options:

• There are many database options available that solve specific problems around database management.
• Database options need to be evaluated by license cost and delivered performance benefit.
• Database options can deliver significant performance benefits without requiring expensive application changes or tuning effort.

How Fast Can You Go

I hope that you have been inspired by the approach and alternatives available to help improve your Oracle database performance.
From an IT Manager’s perspective, when reviewing database performance you should remember to keep it simple:

• Define the problem in your users terms.
• Measure the specific problem as accurately as possible.
• Resolve using the most effective approach.

I’ve put together a detailed process flow which I use to diagnose my client’s Oracle performance issues.
Once you start to build out your process based on this approach it will guide you to fixing the most important problems in the most efficient manner possible.