Getting Help

I hope people won’t take this as a suggestion that I want them to start using this blog like a forum – but I’d like to highlight a note written some time ago by Randolf Geist on the OTN DBA Forum: HOW TO: Post a SQL statement tuning request – template posting It’s worth following his link to the related posting by Rob van Wijk.

If you want to post a question on the Oracle forums, or newsgroups, or the list servers, (or even raise an SR) you need to think a little carefully about the information that you know but aren’t telling everyone else about. Even following the suggestions from Randolf and Rob it’s still likely that someone will ask you for more information – but at least with their guideline you’ve given other people a possible starting point for understanding your problem.

Philosophy – 11

The English language is full of irregular verbs, for example:

I am hypothesising about possible explanations
You are guessing
He’s talking rubbish

Addendum: The point, of course, is that your interpretation of an individual’s words may be critically affected by who the individual is. The use of the expresssion “English irregular verb” to describe this phenomenon was current around the time that I was at University.

[The Philosophy Series]

Philosophy – 10

The most significant question to ask when thinking about adding a new index:

“Will the index eliminate significantly more work than it introduces (at the moments when it really matters) ?”

A few examples of “moments that matter”:

• Bulk housekeeping
• Highly concurrent OLTP activity
• Frequent high-precision reporting
• Acceptance testing for side effects

[The Philosophy Series]

Philosophy – 9

There is an old joke about an engineer, a mathematician, and a philosopher sitting together in a train travelling from London (England) to Cardiff (Wales) ***

As the train crosses the border, the engineer glances out of the window and exclaims: “Oh, look! Welsh sheep are black”.
The mathematician responds: “No; all you can say is that there is at least one sheep in Wales that is black.”
The philosopher corrects both of them: “Gentlemen, all you can claim is that there appears to be a sheep in Wales that seems to be black on one side.”

(Trust me, in 1970, this was quite funny).

The point of telling the tale is this: the best viewpoint to take when trouble-shooting an Oracle database is that of the mathematician – don’t, as the engineer did, leap to extreme conclusions based on just one observation , but don’t, as the philosopher did, get so stuck into such tiny details of theoretical correctness that reasonable assumptions are swept aside.

*** Footnote: for those not familiar with the geography of the UK: “The UK” is “The United Kingdom of Great Britain and Northern Ireland” and “Great Britain” is the union of England, Scotland (most of the top half of the island), and Wales (the lump at the left hand side, excluding the thin pointy bit at the bottom).

[The Philosophy Series]

Philosophy – 8

B-tree indexes vs. Bitmap indexes – the critical difference

• A single B-tree index allows you to access a small amount of data very precisely.
• It is the combination of a subset of the available bitmap indexes that offers the same degree of precision.

You should not be comparing the effectiveness of a single bitmap index with the effectiveness of the corresponding B-tree index.

Inevitably it’s a little more subtle than this – e.g. you may create some low-precision B-tree indexes to avoid foreign key locking issues, it’s also true that the optimizer can combine B-tree indexes, and so on – but if you start from this basis you will have a rational view about how to use bitmap indexes.

Footnote: remember, also, that bitmap indexes introduce massive concurrency issues and other maintenance overheads – if you see them in an OLTP system it’s very likely that they’re causing problems.

Update 23rd Dec 2009: I’ve written a follow-up article to this note since the point I was trying to make seemed to cause some confusion.

[The Philosophy Series]

Understanding

Karen Morton has a few wise words to say about understanding vs. memorization. Definitely worth reading and understanding – and maybe even memorizing.

Experts

Chen Shapira wrote a very nice note about the unconference (and “Oracle Closed World”) presentation that I did at Oracle Open World this year, loosely titled “The Beginner’s Guide to being an Oracle Expert”. In her comments she has captured a point more clearly than I have ever expressed it: “DBAs are under a lot of pressure not to be experts.”

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 needed to run the statement. Typically you 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 in executing the statements, and getting the wrong execution plan would lead to a huge waste of resources. Typically you need to generate histograms in data warehouses or decision support systems where the queries can be ad hoc, brutal, and resource-intensive.

Spot the contradiction – one mechanism is supposed to give you one plan that everyone uses; the other mechanism is supposed to give each execution for each individual the plan that’s best for them at that moment.

Remember this 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”

[More “Philosophy”]