History

A little while ago I noticed a couple of page views that had come from the AskTom website – and I’m always interested to see what question has prompted a link and visit to my blog. In this case it was a question that raised a piece of (nearly ancient) history. In a question dated July 2009 someone was asking about a comment I had made in “Practical Oracle 8i”. (Despite the book being over 10 years old I’m still getting royalty cheques on it – small ones, but they keep on coming – and it’s still a book worth reading).

The question was about the following text:

Avoid naming tablespaces according to time-periods; this is particularly relevant to large, time-based data warehouses where very old data is ultimately dropped off the back end of the database.

There is an unfortunate quirk of tablespace naming – Oracle never forgets a tablespace – in other words it never deletes it from the data dictionary table TS$ (I think this is some sort of hangover from Trusted Oracle where tablespaces have security labels associated with them). Unfortunately, one of the permanently running Oracle processes called SMON (the System monitor) scans this list of tablespaces every 3 minutes looking for tablespaces that might have some free space to coalesce; the size and cost of this scan will grow indefinitely unless you adopt a strategy of recycling tablespace names. It is actually surprising how many databases suffer a massive I/O load on the SYSTEM tablespace because of SMON.

The problem will be reduced somewhat by the introduction of locally managed tablespaces, since the scanning problem is actually exacerbated by the number of free extents that gets generated in poorly managed systems in a way that can’t happen with locally managed tablespaces; nevertheless you need to make sure that the list of tablespace names does not get out of hand.

You’ll be pleased to hear that this is no longer a problem. If you have access to Metalink (MOS), you can find information about (unpublished) bug 5861536: “Slow DDL due to Tablespace lookup with large number of tablespaces” dated 24th Sept 2008; and see in note 401436.1 (10.2.0.4 Patch Set – List of bug fixes by Problem Type) that this problem has now been addressed.