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.

For a larger read you continue the read by switching to a different platter (electronic switch), switching to a different track (radial head movement), or switching to a different disc – which means joining another disk queue (in which case your SAN software – or equivalent – will probably have worked out which discs you were going to be using and started all the reads queueing concurrently).

When you create an Oracle datafile you simply do not know how scattered that file might be across the physical discs in the system – at best a 1MB read from a single file may turn into three or four rotations of a single disk with electronic platter switches, at worst I have seen a single read request turn into 32 separate disc events because of the multiple layers of software that had been used to stripe across discs, then stripe across disk groups, then stripe across logical volume groups and so on .  (This can be really good for performance if you are the only user on your SAN, but a queueing disaster on a multi-user system; and this is why it has become a good strategy to present “naked” discs to ASM and have only one layer of Oracle-aware software between Oracle and the discs).

Maxim: don’t put too many clever layers of software between Oracle and the disc drives.

2.2 Tablespace “fragmentation”.

Obviously you can create a tablespace using multiple files. By definition the tablespace is fragmented – even though there is nothing inherently wrong with this particular type of fragmentation. As I pointed in the previous note, though, this has side effects on the placement of extents for a data segment and this can lead to cases where you might want to perform big reads and find that you are actually performing smaller reads than expected with an associated increase in I/O queue time.

The “fragmentation” effect that people are more like to have in mind, though, when they talk about tablespace fragmentation is something that has also been called “honey-combing” and “bubbling”. This is a side effect of dropping or shrinking objects, moving tables, or rebuilding indexes. You end up with chunks of free space scattered throughout the tablespace. Each time you rebuild another object, of course, you will probably refill some of these chunks – leaving a load more holes where the object used to be.

Basically this type of fragmentation is unlikely to matter since you’re not doing any extra work because of the empty space – except when you backup the files. If you really think that the time spent backing up the empty space is having an important  impact on your backup times (which may mean that the backup over-runs into the next dataload cycle, for example), then you might want to take some action to move objects around in a way that leaves all the empty space at the end of the files as this will allow you to reduce the file size. (See, for example, this note on shrinking tablespaces.) Bear in mind, though, that moving objects around can introduce undesirable side effects – there was a sad little note on OTN a couple of years ago where a DBA found that moving tables made them take up more space (I wrote a note in response, quoting the reason, and solution, that I had originally published in Practical Oracle 8i).

The historic difficulties associated with “honeycombing” were largely a side effect of the pctincrease parameter that Oracle allowed you to specify for data segments – helped along by the myth that it was a good idea to keep reducing your objects to a single extent each. Since the introduction of locally managed tablespaces with their strictly limited set of options for extent sizing (especially the option for uniform sizing), the only issue is timing – when are you going to reuse the space, not how can you manage to reuse the space.

[Further reading on Fragmentation]