Basics of Tuning and Tuning Costs in Oracle 12c

By Chris Ruel, Michael Wessler

Having the right approach and a good plan helps reduce the time spent identifying and fixing an Oracle 12c database performance problem. Just like tackling any kind of problem in life, learning from the experience of others and furthering your education make you more successful. Valuable practices can save you time and effort tackling your database performance problems.

What kind of questions to ask about tuning your Oracle 12c database

When a performance problem is brought to your attention (by self-discovery or by others), you need an accurate scope of the issue. Understanding the issue might involve talking to different people within the application stack. Some questions you might ask are

  • How is this problem presenting itself to you or others?

  • When did the problem start? Did it begin suddenly, or has it been worsening over time?

  • What is the impact of this problem for you or the company? Is the issue isolated or company-wide?

  • Has anything changed in the environment recently?

  • Is there a workaround to use while the problem is researched?

Knowing the answers to these questions helps you prioritize the issue and possibly engage others to help find the cause. (Remember: The problem might not be in the database.) Issues can be very impactful of a company’s bottom line; therefore, scramble all resources that can contribute to the solution.

Don’t try to do all the work by yourself — while you look for the issue on the database, make sure others are searching for it outside of the database. If you focus on the database while no one is seeking the issue elsewhere and you then discover the database is not the problem, valuable time is lost.

How to pinpoint the problem in your Oracle 12c database

Familiarize yourself with the entire technology stack that supports the application to help you pinpoint where bottlenecks exist. A problem related to the database could be many things:

  • High CPU consumption

  • High IO consumption

  • Poorly performing SQL commands

  • Database design issues

  • Hardware problems

  • Application problems

  • Software bug

Unfortunately, the list goes on.

Basic tuning costs with Oracle 12c databases

There’s a saying in the motor racing industry that goes something like, “Speed costs, so how fast do you want to spend?” This saying might also apply to database tuning. Don’t despair though. This focus is mostly on the tools that Oracle offers as standard features within its software stack. However, the “for pay” features are also discussed.

If you can get your company to spring for them, paid tools can sometimes make up the expense quickly. Rather than have a performance problem costing your company revenue, it might be worth your while to spend some money up front to have the right tools in your hands to make performance problems go away.

Is this saying that a good database administrator should be able to tune a database with just the free stuff? Well, to an extent, yes. The more experience you have, the better you can take advantage of what is at hand.

An experienced carpenter can probably build a deck by using a rock instead of a hammer, but having an actual hammer would make the job go faster and reduce the fatigue he or she would experience. See below for a cost-comparative list of resources that you can use when tuning your database.

Tuning Resource Example or Where to Find This Resource Cost
Oracle Documentation Free
Oracle Non-Licensed Tools STATSPACK Free
Oracle Support $ (The cost is normally included with license.)
Oracle Training Oracle University $
Third-Party Tools Quest TOAD $
Oracle Licensed Tools Tuning pack $$
Specialized Consultants Outside Contractor $$$

There’s a saying that claims, “Time is money.” It’s good to keep in mind when considering which database-tuning resources to have on hand.

Also, keep in mind that hearing someone say, “The database is slow. Can you fix it?” can be frustrating when the person reporting the problem has no idea whether the issue lies in the database or in one of the other layers of the complex software stack.

No one wants to spend hours looking for a problem that doesn’t exist, but many tuning efforts are spent proving that the problem is indeed not within the database. This fact goes for newbies and experienced administrators alike.

The good news is that, as you practice the tuning techniques, you get better and faster at finding the cause of performance issues — even those not within the database.