The performance of a query should be related to the size of the data set you’re interested in, not to the size of the database.
If this is not the case then you’ve made a mistake in the physical implemenation. Typically this will be poor indexing, but it could be due to errors in other structural choices such as partitioning, clustering, IOTs, or materialized views.
The primary symptom of this error is the complaint: “as time went by the database got slower”.
Here’s another of those ideas that are so fundamental that you should always keep them in mind when dealing with an Oracle database.
The fundamental strategy embedded in the optimizer is based on just two key points:
If you “tune” SQL by fiddling with it until it goes fast enough then you’ve missed the point and you’re wasting time.
If you start your tuning exercise by thinking about how much data you’re supposed to collect and how randomly scattered it is, then you’re going to minimise the time you spend working out the best way of acquiring that data efficiently.
There are some ideas about Oracle that are so fundamental that they should be lodged in your brain and floating behind your eyes whenever you want to investigate SQL performance problems. Here’s one of them:
Histograms and bind variables exist for diametrically opposed reasons – they won’t work well together without help.
You use bind variables because you want everyone to share the same child cursor for an SQL statement because that statement is going to be used very frequently, everyone is going to do the same (small) amount of work with it, the same execution plan should be ideal for everyone, and you don’t want to re-optimise it every time you use it because the overheads for optimisation would probably be greater than the resources need to run the statement. Typically we want to make great (but not exclusive) use of bind variables in OLTP systems.
You create histograms because statements that are virtually identical do enormously different amounts of work, need different execution plans, and the work done in optimising is tiny compared to the work done by the statements, and getting the wrong execution plan would lead to a huge waste of resources. Typically you need to generate histograms in data warehouse or decision support systems where the queries can be brutal and expensive.
Spot the contradiction – one technology is supposed to give you one plan that everyone uses; the other technology is supposed to give each individual the plan that’s best for them.
Remember that and you will remember to be cautious about sticking histograms into OLTP systems, and won’t be tempted to turn absolutely every literal into a bind variable.
Footnote: It seems that this article should have been called “Philosophy – 2” because I’ve just rediscovered a short note called “Philosophy”
I’ve finally found out why I seem to disagree with Don Burleson more frequently that I do with other people on the internet.
From a recent OTN thread:
Me: “you’re supposed to design a theory to match the facts, not select the facts to match the theory.”
Burleson: “I think it’s the other way around, Jonathan, the scientific method requires that you start with a hypothesis.”
So that’s my problem – I let the facts stand in the way of a perfectly nice theory.
This is a note that I wrote for the Northern California Oracle User Group a few months ago. It was published in the February issue of the magazine in the section “Ask the Oracles”, an interesting and innovative section that I first contributed to in August 2006 with a note on hints.
A quotation from Leslie Lamport in “Specifying Systems” (ISBN 0-321-14306-X):
The hardest part of writing a specification is choosing the proper abstraction. I can teach you about TLA+, so expressing an abstract view of a system as a TLA+ specification becomes a straightforward task. But I don’t know how to teach you about abstraction. A good engineer knows how to abstract the essence of a system and suppress the unimportant details when specifying and designing it. The art of abstraction is learned only through experience.
With a little mangling, this is a sentiment that seems to be appropriate to the skill of trouble-shooting.
I can tell you all sorts of things about the way Oracle works and describe various ways of making best use of the database engine but (apart from the trivial cases – like missing indexes, or massively undersized memory, or silly numbers of disks) I can’t tell you how to look at a set of symptoms and extract the essence of the problem. I can’t tell you how to suppress the unimportant details and expose the root cause of the most important issue; it’s a skill that gets honed with experience.
Fortunately, unlike the analyst or designer, you don’t have to wait for the experience to come to you. You can construct examples that generate intersting symptoms then start playing them off against each other to see how problems interfere with each other and how symptoms overlap but can still be differentiated.
