Re: Oracle recommends rebuilding IOTs in AQ to reduce redo

Ryan,

The procedure described in note 271855.1 iss very useful in environments with AQ. I usually have all my Oracle eBusiness Suite clients run it once a week, it can be run online with no problem I sort of remember the script does a coalesce or rebuild depending on the type of queue, but mostly a coalesceto overcome a known issue with iots.

If you find the following sqls between your expensive queries, you'll benefit from executing note 271855.1.

select /*+ FIRST_ROWS */  tab.rowid, tab.msgid, tab.corrid,                                         
tab.priority, tab.delay,   tab.expiration, tab.retry_count,                                         
tab.exception_qschema,   tab.exception_queue, tab.chain_no,                                         

select t.schema, t.name, t.flags, q.name from system.aq$_queue_tables t, sys.aq$_queue_table_affinities aft, system.aq$_queues q where aft.table_objno = t.objno and aft.owner_instance = :1 and q.table_objno = t.objno and q.usage = 0 and bitand(t.flags, 4+16+32+64+128+256) = 0 ...........

Regards, Paul

• Original Message ---- From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> To: ryan_gaffuri_at_comcast.net; oracle-l_at_freelists.org Sent: Sunday, January 6, 2008 7:25:57 PM Subject: 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







      ____________________________________________________________________________________
Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ 

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