Coalesce

The following question came up in an email conversation a little while ago:

Are you aware of any problems a large oltp site might have with running index coalesce during production hours, as opposed to doing index rebuilds in a maintenance window?

The main overhead with index coalesce is that it generates a lot of undo and redo when it hits any blocks that need to be coalesced. If you have a very large index that is a total disaster area then the volume may be very large. Obviously there’s also a need to read each index leaf block in order – which may mean lots of single block reads, although Oracle could do “db file parallel reads” to make this a little more efficient.

The article http://jonathanlewis.wordpress.com/my-stats/ (will open in a new window)  happens to be a report of the work done in an 11g database for an index coalesce that collapsed a fairly smashed 32,000 block index down to 5,000 blocks while the system was fairly busy.

Figures you might note:

• 32,000 physical block reads (with an average wait time of about 4.3 m/s)
• 380MB of redo generated of which 250 MB undo.

There are also a few buffer busy waits – but the coalesce does no locking, and skips blocks with active transactions.

A side effect of skipping blocks with active transactions  means that you may find  that if you follow  one index coalesce immediately with another, you get a further reduction in size – but in terms of incremental improvement it’s likely to be small.

[Further reading on rebuilding indexes]