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.


Jonathan Lewis

Author: Cost Based Oracle: Fundamentals

The Co-operative Oracle Users' FAQ

----- Original Message ----- From: <ryan_gaffuri@xxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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.


Other related posts: