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 https://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 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.
Oracle Scratchpad 