One of the biggest problems wih RAC is getting it installed. It’s not something I do often – which means it takes me far too long when I need to do it. But sometimes you’ve just got to go ahead and find the time. Three or four years ago I built myself a RAC stack – four nodes, because that’s the minimum number for sensible testing. (If you want to know why: two nodes is a very special case because you don’t have to worry about three-way negotiations; three nodes is a special case because if you’re testing failure scenerios then three nodes fails to two nodes – which is a special case. So four nodes is the smallest generic RAC installation – and that’s ignoring the question of standby systems, of course.)
July 27, 2011
July 26, 2011
O-1 Again
I don’t often waste time searching the internet for evidence of my brilliance – but sometimes you’ve just got to do it 
My O-1 visa (aliens of exceptional ability) was up for renewal, and the immigration authority decided to ask me for more evidence of the significance of my work.
In part, I think, this was because they had decided that Cary Millsap and Tom Kyte were colleagues of mine and therefore couldn’t be used as referees for Cost-Based Oracle. This put me into a weird “Catch-22″ situation – everyone I know that’s in a position to act as a referee is someone I know quite well, which means they’re just as much a “colleague” as Tom or Cary. So anyone I could ask to write a reference presumably wouldn’t be acceptable because I’d asked them!
In the end I asked the presidents of a number of international Oracle User Groups and editors of various magazines to make a statement for me – and they were all very helpful, for which I am grateful.
I also found a couple of dozen books and a handful of academic papers that reference my work – and that must have helped too. I also checked some salary surveys for the USA – it looks as if my fee income puts me somewhere between a top-rate gynaecologist and a top-rate surgeon (there’s got to be an analogy lurking there somewhere;).
Anyway, my passport arrived back from the US Embassy early this morning – so I’m ready for my next trip to the USA (California and Minnesota – 16th Aug)
July 15, 2011
Philosophy 15
If you run a query that is supposed to return one row from a large table, and there’s a suitable index in place you would probably expect the optimizer to identify and use the index. If you change the query to return all the data (without sorting) from the table you would probably expect the optimizer to choose a full tablescan.
This leads to a very simple idea that is often overlooked:
Sometimes it takes just one extra row to switch a plan from an indexed access to a full tablescan.
There has to be a point in our thought experiment where the optimizer changes from the “one row” indexed access to the “all the rows” tablescan.
If you’re lucky and the optimizer’s model is perfect there won’t be any significant difference in performance, of course. But we aren’t often that lucky, which is why people end up asking the question: “How come the plan suddenly went bad, nothing changed … except for a little bit of extra data?” All is takes is one row (that the optimizer knows about) to change from one plan to another – and sometimes the optimizer works out the wrong moment for making the change.
July 12, 2011
RTFM
Many questions I see on the internet could be resolved by the simple expedient of looking at the information that’s in front of you; and many of the questions that clutter up forums and newsgroup would simply cease to appear if more people adopted this approach. Here’s an example of what I mean:
According to Metalink note 34559.1 the p1 value is the absolute file number. According to dba_data_files all my file numbers are one, two or three digits, but when I “select distinct p1 from v$session_wait” I am seeing a file number (p1) of 1413697536. Why is this appearing ?
No doubt someone reading this post will look at that number and immediately recognise it as coming from an SQL*Net message wait on a tcp connection (1413697536 = 0×54435000 -> 0×54 0×43 0×50 -> T C P) and may wonder why the Metalink note didn’t explain that p1 was the absolute file number only when the wait was related to some sort of file I/O. This was the thought that crossed my mind, so I logged on to MOS to see what the note said (because sometimes I fill in the feedback form to correct the errors or ask for a note to be withdrawn) and this is what I found:
Title of note: “db file sequential read” Reference Note
First line of note: This is a reference note for the wait event “db file sequential read” which …
I find that I can’t conjure up any circumstances where a person could know enough to query v$session_wait and dba_data_files, and yet not notice the significance of the title and first line of the metalink note.
Footnote: According to a story I heard recently, there is at least one site where the management has blocked access to any internet sites that cause too much traffic on the company network. Unfortunately the IT department seemed to spend a lot of time reading the Oracle online documentation. So whenever I see the expression RTFM I now interpret it as “read the firewalled manuals”.
July 6, 2011
Indexing
A question about partitioning came up on OTN a few days ago – but the really interesting part of the thread was about the choice of indexes, and how the choice of partitioning, combined with the data patterns, could make a big difference to the choice of indexes. I think the comments I made about indexes are worth seeing, so I’ve linked to the thread.
July 1, 2011
Partitioned Bitmaps
The following question appeared in a comment to an earlier posting on multi-column bitmap indexes and the inability of Oracle to create a bitmap index join when (to the human eye) the strategy was an obvious choice.
- I have a query which is using 2 indexes both are bitmap indexes (sizes are 37 and 24 Mbs) and table size is 17gb. While i ran the following query which can very well get the index itself, it takes around 6-8 minutes and using pga around 3 gb.
could you please explain me why ?
