Re: Oracle recommends rebuilding IOTs in AQ to reduce redo

Make sure you are using next_message, not first_message unless you are using a priority queue. The difference is that next_message opens a cursor and processes the queue from that point in time until it has processed those records, then it re-executes the cursor again. First_message opens the cursor each and every time (to check for priority) which can cause unneeded overhead.

Depending on your throughput needs array dequeues will yield some performance gains (as does array anything).

Again, depending on your throughput requirements, a background (dbms_job) coalesce will be beneficial (say every 5-15 minutes or so depending on your needs). Gains will vary by workload. As always, test and measure.

On 1/7/08, ryan_gaffuri_at_comcast.net <ryan_gaffuri_at_comcast.net> wrote:
> FIFO
> 10.2.0.1
> single row queue
> -------------- Original message ----------------------
> From: "Greg Rahn" <greg_at_structureddata.org>
> > What db version is this?
> > What is the queue navigation type (first or next message)?
> > Are you doing single row or array enqueues/dequeues?

-- 
Regards,

Greg Rahn
http://structureddata.org
--
http://www.freelists.org/webpage/oracle-l