First Steps to Defining Your Oracle Database Performance Problem

upgrade to Oracle 12c

Clearly defining your Oracle database performance problems is one of the most important steps in resolving system performance issues.

With this in mind, how can you help your technical teams resolve issues more quickly and with less risk?

In this post, we’ll look at how to clearly define performance problems and start the process of improving that performance in a structured and methodical way.

3 Practical Ways to Improve Oracle Database Performance

improve Oracle database performance

Confirming 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 db performance optimisation can be complex, there are three overarching approaches that you need to review.

How to Reduce the Performance Risk of an Oracle Database Upgrade

reduce performance risk of an Oracle database upgrade

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.

The IT Managers Tips to Oracle Database Performance Tuning

upgrade to Oracle 12c

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.

The Business Cost of Poor Oracle Performance

Cost of Poor Oracle Performance

Optimising and tuning Oracle systems for optimal performance is a well-covered topic. There a range of tools and methodologies to help support engineers to resolve system performance issues.

Why, with all of this in place, are there still so many poorly performing Oracle systems – perhaps even yours?

Why is it that with the significant investments made in Oracle technology, many organisations are still constrained by the performance of their database systems?

The answer is, we often don’t understand the underlying business cost.

Oracle Analytic SQL Sort Performance Improvements

Oracle Analytic SQL Sort

Recently I have been optimising the Oracle analytic SQL sort operations for a number of queries that make use of the ROLLUP analytic function. The test system was Oracle Database 11.2.0.1. Reviewing the extended SQL trace files to understand exactly where the time for the query was being spent we observed the same type of problem documented in Analytic Agony.

Tracing the sort operations performed by the analytic SQL we noticed the following:

  • Many small sort operations that were completing in memory using less than 80mb sort_area_size.
  • A few very large sort operations that were writing out to temporary tablespace of approximately 8GB in size.

Oracle Database Operational Design Requirements

I spend a lot of time working on issues of performance and scalability for clients systems. The bulk of this work is around database and application transaction processing as these areas typically have the greatest business visibility. An area that does not seem to have the same level of visibility is how operationally efficient these same environments are – not through neglect however, but as a result of operational support requirements not being included as a part of the core requirements during a design phase.

Streamlining Oracle eBusiness Suite Maintenance

Reducing Oracle eBusiness Suite Maintenance Overhead

Streamlining Oracle eBusiness suite maintenance provides opportunities to reduce system downtime and opportunities for human error.

Those who are responsible for maintaining an Oracle eBusiness Suite environment will be familiar with the adpatch, adadmin and adctrl tools. Any patching or maintenance activities on the Oracle eBusiness Suite application tier will usually require running these tools at some point during the maintenance activities.

Running these tools will also present the usual familiar prompts that require the same keystrokes every single time. This can become a fairly tedious exercise even if you have a single APPL_TOP let alone an environment with multiple application tiers and no shared APPL_TOP.

Oracle Database Flashback Physical Standby

Oracle Flashback Physical Standby Database

This third instalment covering some of the flashback features in Oracle 10g takes a look at combining flashback with using a physical standby. I will take a look at two usage scenarios in which I think could offer significant benefit to anyone using Oracle Database Flashback with a physical standby. This post will cover the use of the physical standby as a read/write copy of a production database.

Oracle Flashback Query

Oracle Flashback Query

The next requirement that I found a very good use for Oracle Flashback Query came about as part of a tuning exercise.

I was working through an issue where an application would use a table to temporarily store data before presenting the data to the client program. What was happening was a classic case of incorrect statistics on the table segment resulting in incorrect SQL execution plans.

Even by using the automatic statistics collection features in Oracle Database 10g this problem would not go away. Basically the application performed the following across multiple sessions:

  1. Populate table with data.
  2. Query data to present to user.
  3. Delete data from table.