Oracle Scratchpad

June 27, 2010


Filed under: Index Rebuilds,Indexing,Infrastructure,Oracle,Performance,redo — Jonathan Lewis @ 6:36 pm BST Jun 27,2010

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 (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 ms)
  • 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]

Website Powered by

%d bloggers like this: