Insights on Oracle technologies and trends by columnist Craig Shallahamer
Sometimes I get things I really don't need. Like this really cool GPS I got for my birthday last month. It does do some useful stuff like tell me where I am on this earth, and it keeps me from getting lost while backpacking or driving to a customer site. But the really cool thing is that it allows me to play a new sport called geo-caching.
Geo-caching is basically a high-tech treasure hunt. Someone hides a small box full of worthless stuff and you try and find it. You start by punching the cache's coordinates into your GPS. Some geo-caches are really easy and some require a good hike and serious navigational skill. But it's a great way to get the entire family out into creation, just breathe, use your brain in a different way and have some fun together.
There are other forms of treasure hunts. When I was about 13 years old I got my amateur radio (i.e., ham radio) license. I could communicate via morse code and speech with people all over the world... and I did. It was fascinating. I took my love for this hobby to college. While I was in college, my ham radio friends and I used to do something similar to geo-caching with our hand-held radios. One of the guys would hide somewhere in our college town and just start talking into his radio. My friends and I would then break up into teams and try to find him. It was both a race and a treasure hunt.
When playing this game you quickly discover the quickest way to find the person is not to follow the signal. It's much quicker to drive to a few different locations and determine the person's location using a directional antenna. After plotting only three readings on a map you begin to get a very good idea of where the person is hiding. When moving in close, we followed the signal strength using the directional antenna and listened for the person's voice. This actually works and can be great fun for a bunch of geeky college kids.
Looking back, it's kind of strange, but it makes sense. When I optimize Oracle performance, I triangulate. I really do. I take three basic readings and then within each reading I take many smaller readings. The three basic readings are taken from the three subsystems that every computing system has -- the operating system, the Oracle system and the application system. I find out where each of the subsystems are struggling and then triangulate. That is, I look for the crossover. Every time, the problem is in the crossover area and staring me straight in the face.
In order to get a free ride to a conference, I had to give this triangulation method a name and write a paper about it. So I came up with the Holistic Problem Isolation Method (HPIM) or simply The Three Circle Method. But that's a whole other story.
Let me explain this computing system triangulation in more detail. I find out where each subsystem is having problems. That is, where the contention is, where the resources are being requested in significant quantity, and where the subsystem is just plain out of gas. Regardless of the subsystem, either there is not a significant load or one of it's systems is bottlenecked. In the operating system I look at CPU, IO, network and memory. Then I look at Oracle using a response-time-based analysis, that is, analyzing where Oracle is spending most of its time. Then I look at the application focusing on users, processes and resource-intensive SQL. I then summarize each of the three subsystems into a very simple thought looking for the crossover.
The crossover is the triangulation and that's where the problem is. It may sound very simple... but that's because it is very simple. It's simple, quick and never ever fails, which means it yields consistent results.
And consistency, not guessing, is what every DBA desires.
Another benefit to triangulation is multiple perspective problem identification. It's like having your own group of wise counselors telling you where the contention resides, each from their own perspective. When they all say nearly the same thing, you know you're right. When they don't say the same thing, you know either someone made a mistake or there is something you don't yet understand.
When I started optimizing Oracle systems, I started learning about Oracle internals. Then I started learning about indexing and optimizing SQL. But when I learned about how Oracle and the application interacted with its host operating system and then triangulated, that's when things really started to happen for me. Performance optimization is like a treasure hunt because you are always trying to find something of value. Interesting, though, once we find the treasure, we try to eradicate it.
This was first published in June 2004