Re: Oracle recommends rebuilding IOTs in AQ to reduce redo
- From: Paul Albeck <palbeck_ar@xxxxxxxxxxxx>
- To: jonathan@xxxxxxxxxxxxxxxxxx, ryan_gaffuri@xxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Thu, 10 Jan 2008 09:42:21 -0800 (PST)
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,
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 ...........
Regards, Paul
----- Original Message ----
From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
To: ryan_gaffuri@xxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@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.
>
--
//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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
