Re: Advanced Queueing

From: <chris_at_thedunscombes.f2s.com>
Date: Wed, 10 Nov 2004 09:44:14 +0000
Message-ID: <1100079854.4191e2ee067ae@webmail.freedom2surf.net>

April,

Not sure how much you already know about AQ so you may already be aware of all of this:

Good introduction to AQ is in Steven Feuerstein's "Oracle Built-in Packages" book, I know it's Oracle 8 but it's still good for the basics.
Metalink note 102926.1 has some info that may be helpful to you.
One gotcha from my experience using AQ on 8.1.7 (maybe resolved in 9i) is that one of the standard AQ procedures, I think it's dbms_aq.dequeue, does a full table scan of the queue table every time it's called.

This means you must keep the number of messages on the queue table to a minimum and if for some reason you get a temporary build up of messages then you'll need to do some housekeeping on the queue table to bring down the high water mark and avoid all the unnecessary IO. (I had a regular procedure for doing this on an AQ system I worked on a year or so ago.)

As a result of this FTS issue you need to consider how many queues you map to each queue table.

HTH Chris

Quoting aj wells <awellsdba_at_gmail.com>:

> A little bird pointed out that there is a wealth of information on
> Advance Queueing on this list... and I was hoping (since google isn't
> giving me what I want) someone would have pointers to where good
> papers on setting up and tuning AQ might be.
>
> THIS job is REALLY starting to be fun...
>
> April
> --
> http://www.freelists.org/webpage/oracle-l
>

Chris Dunscombe

Christallize Ltd

Everyone should have http://www.freedom2surf.net/

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

Received on Wed Nov 10 2004 - 03:40:28 CST