Ancient History « Oracle Scratchpad

July 1, 2008

Ancient History

Filed under: Infrastructure — Jonathan Lewis @ 9:23 pm UTC Jul 1,2008

I’ve just been browsing through a subdirectory on my laptop that has been copied 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” – posisbly 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 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 the 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.

Comments (6)

6 Comments »

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 UTC Jul 1,2008 | Reply
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 envirnoments 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 UTC Jul 2,2008 | Reply
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 UTC Jul 2,2008 | Reply
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 UTC Jul 3,2008 | Reply
[...] 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 UTC May 31,2009 | Reply
[...] 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 UTC Jun 19,2009 | Reply