Correlation

One of the “inspirational thoughts” on my opening page is the observation by the late Stephen J. Gould that

“The invalid assumption that correlation implies cause is probably among the two or three most serious and common errors of human reasoning.”

 It’s very easy to equate correlation with causation and take inappprioate action as a result – it’s an example of faulty thinking that I see fairly frequently on forums such as OTN or the Oracle newsgroups.

If you want to get an insight into the difference between correlation and causation, you ought to read Robyn Sands’ note on “Nonsense Correlation”.

Philosophy – 7

There are no “secrets”.

At least, there are no secrets involved in making a database perform properly. Occasionally a useful new piece of information is uncovered – and if it’s worth knowing it will be documented, discussed, and validated in public.  (It won’t necessarily be documented on Metalink, OTN, or any other official Oracle site – but that doesn’t make it a secret.)

Whenever I’ve seen people doing presentations about “secrets” they’ve usually split their time between quoting the documentation, stating the obvious,  making mistakes, and offering sweeping generalisations that needed careful justification.

I have a simple rule of thumb for presentations – the more glamorous, trendy or exciting the title sounds the less likely it is that the presentation will be useful (but that won’t stop me reading the abstract – just in case).

[The Philosophy Series]

Philosophy – 6

Testing for correctness:

• Have you tested with good data and all the different possibilities of bad data
• Does the code do the right thing … and nothing more.

[The Philosophy Series]

Philosophy – 5

Trouble-shooting with Statspack / AWR:

Something has to be top of the “Top 5 Timed Waits” … even when there are no performance problems.

[The Philosophy Series]
[Further Reading on Statspack]

Philosophy – 4

There are two critical aspects to scalability:

• In single user-mode: will the response time next year be the same as it is this year (see Philosophy – 3)
• In multi-user mode: will the response time be the same when the number of users doubles, trebles, quadruples …

If you want that as a snappy catch-phrase Cary Millsap produced a good one at Collaborate 2009: “Fast Now, Fast Later”.

[The Philosophy Series]

Philosophy – 3

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”.

[The Philosophy Series]

Philosophy – 2

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:  

•  How much data are you after.
• Where did you put it.

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.

[The Philosophy Series]

Philosophy – 1

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”

[The Philosophy Series]

Scientific Method

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.

 

Best Practices

[The Philosophy Series]

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.

Philosophy

A quotation from Leslie Lamport (of “Lamport SCN” fame) 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 interesting 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.

[The Philosophy Category]