Scalability Conflict

Here’s an example of how you have to think about conflicts of interest when dealing with problems of scalability. It starts with a request (that I won’t give in detail, and was a little different from the shape I describe below)  from a client for advice on how to make a query go faster.

Basic problem: the query runs about 20 times per second, returning a very small number of rows; it’s basically a very simple “union all” of three query blocks that access the same table in slightly different ways.

Recovery

I’ve been on holiday for the last few days – the last few posts were dated to publish themselves in my absence – and got home last night. First thing I did, of course, was to download my email … second thing the machine did was to declare an automatic software update and destroy the hard-disk on reboot. So I’ve downloaded but not read all the mail that was sent to me after 8:05 a.m. (BST) on Friday 16th July.

Luckily 8:05 am was when I took a backup of the system – as you do when you’re about to to on holiday – so I haven’t lost any data, except for those emails. So if you sent me anything important over the last week, please send it again.

Footnote: while I had a backup of all my data, I discovered (as one does until you practice recovery very regularly) that there were bits of information I hadn’t catered for: my address book and my mail rules, neither of which I had been exporting regularly, both of which are quite useful.

Fragmentation 4

This note is part four of a four-part series, and covers Index fragmentation. The whole series is as follows

1. Introduction – with links to parts 2 – 4
2. Disk and Tablespace Fragmentation
3. Table Fragmentation
4. Index Fragmentation – this bit

4. Index “fragmentation”.

The multiple extent and ASSM “fragmentation” that I described in the previous article about table fragmentation applies equally well to indexes, of course, and matters in just the same way – i.e. hardly ever.

Design …

… and how not to do it.

In the last couple of weeks I’ve visited two offices which have some really high-tech coffee machines, both from the same company. When you use these machines you have two options, you can punch out the menu options for the drink you want, or you can punch out a “quick code” for the drink you want. In my case (coffee, milk, no sugar) the code is 018, so …

Key punches for option 1

• coffee
• milk
• start

Key punches for option 2

• Enable quick code
• 0
• 1
• 8
• start

Yes, it really does take more key strokes to use the quick code than it does to choose what you want.

In fact, checking the dozens of possible drinks combinations on the machine I decided there were probably about six options where the quick code was quicker than just punching in what you wanted, for example:

• coffee
• extra strength
• milk
• more milk
• sugar
• more sugar
• start

Just keep this in mind next time you think about supplying your end users with a cute feature that’s supposed to make them happier people.

Fragmentation 3

This note is part three of a four-part series, and covers Table fragmentation. The whole series is as follows

1. Introduction – with links to parts 2 – 4
2. Disk and Tablespace Fragmentation
3. Table Fragmentation – this bit
4. Index Fragmentation

3. Table “fragmentation”.

In the introduction we discussed one type of table fragmentation that doesn’t (usually) matter – the fragmentation of a table into multiple extents. Here’s a funny thought – ASSM (automatic segment space management) introduces another form of table fragmentation that usually doesn’t matter.

Fragmentation 2

This note is part two of a four-part series, and covers Disk and Tablespace fragmentation. The whole series is as follows

1. Introduction – with links to parts 2 - 4
2. Disk and Tablespace Fragmentation – this bit
3. Table Fragmentation
4. Index Fragmentation

2.1 Disk “fragmentation”.

Tablespaces are made up of files, and files are stored on discs – which are often “logical volumes” rather than real devices. When you issue a (real) disc read request, the largest amount of data you can get off a (real, physical) disc in a single physical action is something like 300KB to 500KB – the content of a single circular track on a single platter of a disc.

Changing UNDO

From time to time people run into problems with UNDO tablespaces that have grown much larger than expected (perhaps due to a rogue process doing far too much work) and refuse to shrink. The workaround is to create a new undo tablespace and switch the instance to use it – but even this simple procedure introduces a couple of surprises.

There’s a little demonstration and discussion of the operation at this URL.

Footnote 1: The example assumes you are using an spfile for your database; if you’re not, then don’t forget that changing the undo tablespace with an alter system command can work – but you might get a nasty surprise if you forget to update the pfile before you next restart the database.

Footnote 2: It is possible (despite occasional claims to the contrary) to resize the files in an undo tablespace with the standard command:

 alter database datafile {filename} resize  .... ;

However, given the unpredictable way in which undo segments can be allocated, then grow, shrink and get de-allocated, you have to be a little lucky to find a moment when there is free space at the end of the file that can be de-allocated in a resize operation.

Fragmentation 1

This note started life as a nutshell until I realised that it was going to be more of a coconut than a hazel nut and decided to turn it into a short series instead. I should manage to  post  four parts over the next two weeks:

1. Introduction (this bit)
2. Disk and Tablespace Fragmentation
3. Table Fragmentation
4. Index Fragmentation

Don’t Knows

One of the biggest problems in learning is that you don’t know how much you don’t know, and this raises two questions:

• how do you find out that there are huge gaps in your knowledge that need to be filled ?
• how do you know whether or not the material you’re learning from is any good ? (There’s a LOT of garbage on the internet.)

I can across an interesting little post from John Scott (of ApEx fame) a little while ago that shows the effect of the first question very clearly.

dba_tab_modifications

In case you don’t follow the link to Martin Widlake’s blog (see right) very often, he’s done a couple of recent posts on dba_tab_modifications that are worth reading.

(And I’ve just discovered the ‘gutter=”false”;’ option for the ‘sourcecode’ tag in one of the comments on Martin’s blog – and that’s also a  helpful feature.)

Unrecoverable

A recent question on the OTN database forum asked: “What’s the difference between index rebuild unrecoverable and nologging?”

The most important difference, of course, is that unrecoverable is a deprecated option so you shouldn’t be using it even though it still works.

Index branches

Some time ago I published a little script that showed you how to read an index treedump and summarise the leaf block usage in the logical order of the index leaf blocks – allowing you to see fairly easily if the index had any areas where the blocks were poorly filled.

Here’s another  way of looking at the treedump – this time focusing on the branch blocks only. The posting is a little long, but mostly because I’ve reproduced the output from a reasonably large index so that you can see a full example from a production system.