Re: Oracle recommends rebuilding IOTs in AQ to reduce redo
Date: Thu, 10 Jan 2008 09:42:21 -0800 (PST)
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,tab.local_order_no, tab.enq_time, tab.time_manager_info, .......
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 ...........
- 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
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
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.
Author: Cost Based Oracle: Fundamentals
The Co-operative Oracle Users' FAQ
- 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
> 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
> rebuilds IOT indexes on the AQ tables. However, they don't say why
> 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-lReceived on Thu Jan 10 2008 - 11:42:21 CST