RE: Advanced Queueing

From: Post, Ethan <Ethan.Post_at_ps.net>
Date: Wed, 10 Nov 2004 07:45:31 -0600
Message-ID: <83FCA77436D6A14883E132C63F4101D0019FCB68@pscdalpexch50.perotsystems.net>

We use AQ quite a bit, watch out for some character set bugs, there are some character sets you can not use with AQ. Also import/export with queues is hell. Export/import user level you will have issues since you usually don't want to drop your queues before exporting (not sure if that even solves the problem). There is an article on metalink about the issue. We basically have a cleanup process which deletes from SYS tables to get the queues uninstalled after import so we can recreate them.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of aj wells Sent: Wednesday, November 10, 2004 6:20 AM To: chris_at_thedunscombes.f2s.com
Cc: oracle-l_at_freelists.org
Subject: Re: Advanced Queueing

Don't know a whole lot honestly. We were going to try to impliment at my last job because "we" throught they were totally cool and good for something ("we" were into really cool even if not practial or applicable). THIS situation, I think it will help, but I really want to prove out whether it will or not with a production load.

Will go look at the Feuerstein book... just because it is Oracle8 doesn's mean it isn't still applicable.

Read the note (that is why I want to do load tests and dink around with stripe sizes)

There are indexes that can be built (if I am reading what I DID find correctly) to help with the enqueues and the dequeues, so the 'feature' might be fixed in 9i.

I was thinking that it might be a good idea to test out with one broker at first, and add an additional broker or two to help with the processing.

Thank you... it helps
aj=20

On Wed, 10 Nov 2004 09:44:14 +0000, chris_at_thedunscombes.f2s.com <chris_at_thedunscombes.f2s.com> wrote:
> April,

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

>=20
> 1. 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.

>=20

> 2. Metalink note 102926.1 has some info that may be helpful to you.
>=20
> 3. 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.
>=20
> 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.)
>=20
> As a result of this FTS issue you need to consider how many queues you
map to
> each queue table.

>=20

> HTH

>=20

> Chris

>=20
>=20
>=20

> Quoting aj wells <awellsdba_at_gmail.com>:

>=20

> > 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
> >

>=20
>=20

> Chris Dunscombe

>=20

> Christallize Ltd

>=20


> -------------------------------------------------

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

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

Received on Wed Nov 10 2004 - 07:42:44 CST