Paul Karman

Oracle Tuning for rednecks

Why can we understand redneck jokes so well?
Because every once in a while, at a less bright moment, we recognise ourselves all too well.

There is one thing to say about rednecks, they find simple solutions to nagging problems: If the neighbor's dog barks you out of your sleep you shoot it. Simple adequate solution.

I am very much for applying simple logic to performance tuning. You kill the pain where it hurts. Strangely this is not the way we see it always in practice. It sometimes seems that companies do not have much logic behind tuning. The applied technique looks a bit like throwing mud against a wall hoping that some of it will stick. By using this method problems do not always get solved in a logical way.

I'd like to use the analogy of the car that developed a performance problem. It does not want to run as fast as it should. The technicians are completely confused as to why. Could it be the drag coeifficient, roll resistance, fuel grade, technical issues? Somehow, be it because of time, be it because of knowledge, the problem just does not get discovered. So finally a stronger powerplant is installed and the car drives fine again.

If the technicians had been able to pinpoint the actual problem they would have taken that handbrake off.

The same happens in practice too. Even an average system is so complex that it is easy to lose track of all factors involved in a perfomance issue. IT people do not have all the knowledge and they certainly do not have all the time to figure out what the real problem is so quite often a solution is chosen that will work regardless of the cause.

Like the handbrake in the car there is a danger of choosing a solution that is not very economical.

But it does not end there. After solving the problem there are basically two ways to go: 1) keep on searching till you find the real cause or 2) move on to the next thing on the todo list. Theorists would of course go for the first choice while pragmatists would be inclined to go for he second one. The truth should be somewhere in the middle but somehow the theorists and the pragmatists are so far apart that the results are sub-optimal. By quickly solving the problem everybody has time to work on the rest of the todo list and we will handle other problems the best we can as they arrive. There is a problem with the reasoning though. One or more of the things on the todo list might well be related to the initial problem. The bigger motor solution for the handbrake problem quickly needed a bigger gas tank and later on it was determined that brake maintenance should be upped.

Translated to the IT departement it will often look like the "keep moving" solution is the preferred one. To solve a problem we seem quite quick to go for more and stronger servers, more memory, faster harddrives, thicker networks etcetera. But what else to do?

The solution comes from a typical stubborn redneck: Can't understand it, aint doin it.

The question that runs through my mind is about thesame: Do I understand what I am doing? There is an ongoing phnomena that seems to tell us that we need to go on. This need to go on is sometimes even stronger then the need to understand. But by answering this first question I can kinda also anticipate my next reaction: If I don't know what I am doing I tend to want to go by instinct. Now there's a redneck reaction I'd like to avoid! If for example my slightly malfunctioning TV is fixed by a light tap then my instinct would tell me that a sledge hammer would cure any problem?! It might make me feel better but I'd rather work hard on an alternative. Education for instance.

To be practical at sites that need some care here is a short list of areas that I try to gain knowledge of in order of priority.

1) Is there SQL statement performance tuning going on?
This is the handbrake on the car. There is so much performance to gain if you can find and get rid of bad behaving SQL. It is also the most difficult area of epertise. It separates the good DBA's and developers from the exelent ones. Standard software is mosttimes fine. Otherwise it would be questionable to continue with it. If there are any performance issues on SQL statement level it is often with customisations.

2) Is there Schema performance tuning going on.
Often a DBA or Developer has success by adding an index on some table. Often a database schema is part of standard software and as such taken as (mostly) bug free. The opposite is more true. Differences in companies can need differences in the schema. A company dealing with tens of thousends of internet customers a day might make tens of thousands of orders and invoices with only one or two order lines. Another company might deal with only a couple of customers that need a container full of cargo. Their orders and invoices might contain thousends of orderlines. It is easy to understand that thesame reports at these different customers might just need addapted indexes on their order and orderline tables.

3) Is there any instance tuning going on.
Instance tuning most times only needs a tiny little attention of a good DBA. Only a few exotic sites might need a little more attention. But realy, you don't want to be part of this select group. On rare occasions I see a database that has been deprived of any DBA influence. More often I see sites where this 3rd priority has gotten more attention then the previous two together.

4) Is there any OS tuning going on.
Just like priority 3 this area needs a bit of basic attention and then just becomes subordinate to the first two. There are one ot two issues that should be concidered like filesystem choice and maybe RAID configuration. Regarding RAID there is this nice support group that says it all.

5) Is there any hardware tuning going on.
Obviously hardware needs to be optimally utilized. Obviously too is that when there are performance issues and the previous priorities have not been adressed the real issue is education.

To cope with the issues described in all diciplines you could hire specialists. Best is to hire specialists and work on education too, neither all the time, just every once in a while to brush up on the knowledge.

Paul Karman