Re: Oracle recommends rebuilding IOTs in AQ to reduce redo

Whenever you have a index which is being used to model a FIFO (first-in, first-out) queue, you run the risk of degenerate indexes if you don't manage to clear the "old" end of the index 100% as you go. You could imagine an index where the first 98 leaf blocks average 1 or 2 entries and the last two leaf block average 200 entries.

Queues, being IOTs - which means large index entries because the IOT carries the payload with the index structure - can make this problem more likely. Queues with multiple consumers can make it even more likely because one 'slow' consumer can be responsible for holding a long history in parts of the index that have been cleared of entries for other consumers.

In simple indexes, the optimum solution to this type of problem is usually a coalesce, rather than a rebuild. Given that Queues are IOTs that you're not really supposed to know about, it's not too surprising that Oracle has suggested a rebuild instead.

You mention, however, excess redo. This may simply be related to the size of index entries and the associated frequency and overheads of leaf block splits (a leaf block split generates redo that is a little larger that two times the block size).

There is another possibility, though. Queues are supposed to empty themselves out over time - and empty index blocks can get re-used in other parts of the index.

Unfortunately there is an oddity of the implementation that on a leaf block split, Oracle can pick an empty leaf block for re-use, go through all the work of the leaf block split, then decide that it's used a block that should not have been used - at which point it unwinds the changes and tries another block. I actually have an example of a redo log dump from a "single" leaf block split that generated about 15 Mb of redo because of this. Again, a coalesce is sufficient to deal with this problem; although again a rebuild would work. Maybe this happens more with queues that in other circumstances given the constant "delete from the left, insert at the right" nature of their use.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message ----- From: <ryan_gaffuri_at_comcast.net> To: <oracle-l_at_freelists.org> Sent: Saturday, January 05, 2008 12:51 AM Subject: Oracle recommends rebuilding IOTs in AQ to reduce redo

> Note: 271855.1
>
> We are getting massive amounts of redo generation well out of performance of
> the regular DML we have. We are using AQ and we enqueue and dequeue
> constantly. I don't have exact volumes.
>
> Oracle recommends in that note to run a procedure they have that basically
> rebuilds IOT indexes on the AQ tables. However, they don't say why this would
> help. Anyone know? I think an enqueue is basically an insert to an IOT and a
> dequeue is a delete.
>

--
http://www.freelists.org/webpage/oracle-l