Oracle Scratchpad

March 25, 2011


Filed under: Oracle,Philosophy,Troubleshooting — Jonathan Lewis @ 6:51 pm GMT Mar 25,2011

“There is no space problem.”

If you saw this comment in the middle of a thread about some vaguely described Oracle problem, which of the following would you think was the intended meaning:

    There is a problem – we have no space.
    We do not have a problem with space

Wouldn’t it make life so much easier to choose between:

    We are not seeing any Oracle errors.
    We are seeing Oracle error: “ORA-01653: unable to extend table X by N in tablespace Z”

(That’s just one of many possible space-related errors, of course.)

February 17, 2011

Philosophy – 14

Filed under: Philosophy — Jonathan Lewis @ 6:48 pm GMT Feb 17,2011

Paraphrasing Yogi Berra:

    “It ain’t committed until it’s committed.”

If you’re wondering why it’s worth remembering this odd comment – it addresses the (commonly asked) question:

    “does the redo log contain uncommitted data as well as committed data?”

The answer is: yes.

When a session is creating redo change vectors it doesn’t know whether it is going to commit or rollback. But a session has to be able to store an arbitrarily large list of change vectors somewhere, and that list has to appear in the redo log (ideally “instantly”) if the session commits – so Oracle avoids delays on commit by putting the change vectors into the redo log as they are created***.

If you view the question from the opposite extreme, the recovery mechanism has to be able to deal with uncommitted data anyway because there are, after all, several scenarios where data that definitely was committed cannot be recovered; for example, recovery until end of log file 9998 because log file 9999 was destroyed and simply doesn’t exist – how can the code handle transactions that were not committed until part way through file 9999 if it only knows how to handle committed transactions ?)

*** Not strictly true from 10g onwards where Oracle introduced a delaying effect aimed at reducing competition for the redo allocation and redo copy latches for “small” transactions.

[The Philosophy Series]

February 9, 2011

Philosophy – 13

Filed under: Philosophy — Jonathan Lewis @ 6:32 pm GMT Feb 9,2011

If you see a comment like “X is a bad idea” this does not mean “some mechanism that is vaguely ‘not X’ is a good idea”.

If, for example, I say:

    “Histograms will not work well on character strings that are more than 32 bytes long and generally similar in the first 32 bytes”

that is absolutely not the same as saying

    “It’s a good idea to create histograms on character strings that are less than 32 bytes long.”

If this were a purely mathematical world we could invoke symbolic logic and point out:

(A => B) <=> (¬B => ¬A)

which means my statement is equivalent to:

    if you have a histogram that is working well then the data is not character strings of more than 32 bytes with generally similar values in the first 32 bytes”

Of course, being Oracle, you may find that someone, somewhere, has exactly such a histogram that appears to work brilliantly for them – but that will be because the optimizer has messed up the arithmetic so much that they are getting a great execution plan for completely the wrong reason … so they need to watch out for the next upgrade or patch release in case the optimizer gets enhanced.

[The Philsophy Series]

October 10, 2010

Philosophy – 12

Filed under: Philosophy — Jonathan Lewis @ 5:46 pm GMT Oct 10,2010

Here’s a useful description I heard recently from philosopher Daniel Dennett:

The canons of good spin:

  1. It is not a bare-faced lie
  2. You have to be able to say it with a straight face
  3. It has to relieve skepticism without arousing curiosity
  4. It should seem profound

It seems to describe a lot of the stuff that our industry publishes on the internet.

[The Philsophy Series]

June 30, 2010

Getting Help

Filed under: Philosophy — Jonathan Lewis @ 6:31 pm GMT Jun 30,2010

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.

May 20, 2010

Philosophy – 11

Filed under: humour,Philosophy — Jonathan Lewis @ 7:02 pm GMT May 20,2010

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]

May 11, 2010

Philosophy – 10

Filed under: Philosophy — Jonathan Lewis @ 6:56 pm GMT May 11,2010

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]

March 29, 2010

Philosophy – 9

Filed under: Philosophy — Jonathan Lewis @ 6:54 pm GMT Mar 29,2010

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]

October 26, 2009

Philosophy – 8

Filed under: Philosophy — Jonathan Lewis @ 8:33 pm GMT Oct 26,2009

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

[The Philosophy Series]

October 20, 2009


Filed under: Philosophy — Jonathan Lewis @ 7:27 pm GMT Oct 20,2009

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

October 18, 2009


Filed under: Philosophy — Jonathan Lewis @ 7:30 pm GMT Oct 18,2009

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


October 16, 2009


Filed under: Philosophy — Jonathan Lewis @ 7:14 pm GMT Oct 16,2009

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

October 12, 2009

Philosophy – 7

Filed under: Philosophy — Jonathan Lewis @ 8:26 pm GMT Oct 12,2009

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]

September 24, 2009

Philosophy – 6

Filed under: Philosophy — Jonathan Lewis @ 8:15 pm GMT Sep 24,2009

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]

September 10, 2009

Philosophy – 5

Filed under: Philosophy,Statspack,Troubleshooting — Jonathan Lewis @ 6:31 pm GMT Sep 10,2009

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]

« Previous PageNext Page »

Powered by