How to Tune Your Oracle 12c Database After a Problem Occurs

By Chris Ruel, Michael Wessler

Unfortunately, no matter how much preparation and configuration you do before you deploy your Oracle 12c database, someday a performance problem will arise. Performance tuning is one of the more difficult tasks for database administrators (DBAs) whether they’re new or seasoned. Entire books, week-long training classes, and large pieces of software have been written to help you deal with the inevitable performance problems that will arise.

Even though it’s not funny, the running joke about a database performance problem always seems to start with, “A guy walks into a bar and says, ‘My database is slow. . . .’”

When a user, comes up to the DBA and makes a comment like that, what exactly does that mean? Often, the database is blamed for issues that could be related to something else entirely. The database always seems to get a bad rap. As a DBA, your first task is to see whether the database actually has a problem.

First, collect some basic information from the user:

  • When did this problem start?

  • Are you the only one experiencing it?

  • Can you replicate the problem at will, or is it intermittent?

  • Can you show me how to replicate the problem?

  • Does it happen only during certain times of the day?

  • What is the impact of this problem to the business?

After you get some of the above questions answered, see whether you can verify the problem. If you can’t verify it at your desk, it may be something that would make a trip to the user’s desk worthwhile to see the problem in person.

After all, you may find that he has 100 programs open on his machine, which is slowing down everything he does. Or, you may note that he is operating in a different building, on a different part of the WAN, or off of a wireless connection, which could explain a perceived problem with the database.

If you can’t replicate the problem in person or explain why he is experiencing the problem, one of the next steps to take is to ask for some help. This doesn’t necessarily mean from other DBAs (although that never hurts); it mean from other infrastructure teams.

For example, you might engage the system administrators to check the database server or the application servers for overloading, or you might engage the storage team to look at the filers or SAN (Storage Area Network) where the data resides.

The reason you want to engage these other teams early is because often times a performance problem can be crippling to the business. It’s better to get them involved now to start working on the issue than after you have spent two hours trying to solve it yourself.

They may come back and say everything looks like business as usual or they may see some increase in resource consumption. Sometimes, they can trace resource hogs to individual user processes on the system.

If that sort of red flag can be identified, it is going to help you get going into the right direction sooner. When you have something to focus on, you can start employing some of the tools at your disposal to fix the problem.