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”:
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).
Btree indexes vs. Bitmap indexes – the critical difference
You should not be comparing the effectiveness of a bitmap index with the effectiveness of a b-tree index.
(Inevitably it’s a little more subtle than this – you may create some low-precision b-tree indexes to avoid foreign key locking issues,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.
Karen Morton has a few wise words to say about understanding vs. memorization. Definitely worth reading and understanding – and maybe even memorizing.
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.”
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”.
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 tahiti.oracle.com – 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).
Testing for correctness:
Trouble-shooting with Statspack / AWR:
Something has to be top of the “Top 5 Timed Waits” … even when there are no performance problems.
There are two critical aspects to scalability:
If you want that as a snappy catch-phrase Cary Millsap produced a good one at Collaborate 2009: “Fast Now, Fast Later”.
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.
Theme: Rubric. Blog at WordPress.com.
