Oracle Scratchpad

July 1, 2008

Ancient History

Filed under: fragmentation,Infrastructure,Oracle — Jonathan Lewis @ 9:23 pm BST Jul 1,2008

I’ve just been browsing through a subdirectory on my laptop that has survived many migrations from machine to machine over the years – even though some of the material needs programs that probably don’t exist any more.

This slide, from a presentation I gave at the UKOUG annual conference in 1996, raised a wry smile. The title of the presentation was “Fact, Folklore, or Fairy-table” – possibly the first ever “Mythbuster” presentation on the Oracle scene. The title of each slide declared a popular belief, and the rest of the slide commented on the sense or (usually) lack thereof in the belief.

The notes on this particularly slide show that I was talking about Oracle 7 at the time but I remember explaining this particular concept to the DBA at one client site shortly after 6.0.33 went into production.

Amazingly you can still find questions on the internet about tablespace fragmentation – even though the topic should have been done to death years ago.

[Further reading on Fragmentation]


  1. This is great! Can you post the whole thing? Are there any you would disagree with now? Were you making assumptions about different sized data or volatility per tablespace?

    Comment by joel garry — July 1, 2008 @ 10:13 pm BST Jul 1,2008 | Reply

  2. Joel,
    The presentation was 45 minutes, and apart from the introduction, background and conclusion there are only seven slides. That makes the slides very sparse talking points – so I’m not keen to post the presentation as someone’s bound to misinterpret it. (How long do you think it’s going to be before someone cites the title from this slide without reading the rest of the note ?)

    The comments I chose to discuss were:

    1) Objects should be compressed to one extent
    2) Fragmented tablespaces are bad
    3) Raw disks improve performance
    4) Raw disk is hard to handle
    5) Tablescans are bad
    6) Foreign keys need to be indexed
    7) Every site needs a full-time DBA

    In outline, omitting a few of the details and caveats, the comments that went with them were along the lines of:

    1) Waste of time (unless you were still running 5.1)

    2) Doesn’t happen if you set things up properly

    3) Maybe/Maybe not – depending on the quality your application design and code. (The discussions would probably be about direct I/O – in it’s various forms – now).

    4) Not with logical volume managers (which had been around for a few years by then). (I don’t think anyone would bother to make this remark nowadays).

    5) Not necessarily (This still has to be explained fairly frequently !)

    6) Not necessarily (This still has to be explained fairly frequently !)

    7) Times and working environments change, but the point I was making with this one was that if a DBA doesn’t have to spend all their time dealing with design and coding errors, they can keep an eye on quite a lot of databases. At the time I was suggesting a couple of hours per week. (Funnily enough, the notes that go with that slide say that one of the things a DBA may have to do is an occasional index rebuild).

    The closing slide was a bit of a classic, it said:

    “If it’s going to make a big difference, don’t believe it until you’ve proved it.”

    If I were to present that last slide today, I’d probably change it to “If it’s supposed to make a big difference …”.

    Comment by Jonathan Lewis — July 2, 2008 @ 10:26 am BST Jul 2,2008 | Reply

  3. Can’t remember the precise date, but Dave Ensor came out to Oz in the mid 90’s and did a similar talk – something like ’20 stupid things people do’….As I ticked off around 17 of the 20 as things I was regularly doing, I decided it might be time to learn a little more :-)

    Comment by Connor — July 2, 2008 @ 2:44 pm BST Jul 2,2008 | Reply

  4. Humm… is that enough material to demonstrate correlation between “Buffer hit ratio” and “tablespace fragmentations”?

    Comment by Polarski Bernard — July 3, 2008 @ 7:41 am BST Jul 3,2008 | Reply

  5. […] under: Uncategorized — Jonathan Lewis @ 7:09 pm UTC May 31,2009 Some time ago I posted an extract from a short presentation I had given at the UKOUG annual conference about 12 years […]

    Pingback by Ancient History 2 « Oracle Scratchpad — May 31, 2009 @ 7:11 pm BST May 31,2009 | Reply

  6. […] Troubleshooting — Jonathan Lewis @ 7:08 pm UTC Jun 19,2009 This could nearly be one for the “ancient history” series, because it starts with a quote from that marvellous book “Practical Oracle 8i”, […]

    Pingback by Tablespaces « Oracle Scratchpad — June 19, 2009 @ 7:09 pm BST Jun 19,2009 | Reply

  7. […] Reading: a little bit of ancient history that I published long before Oracle introduced locally managed tablespace wtith uniform extent […]

    Pingback by Fragmentation 2 « Oracle Scratchpad — July 16, 2010 @ 6:52 pm BST Jul 16,2010 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: