Re: Advanced Queue ordering?

Sorry, I didn't know anybody else was interested in the problem. Here's what I found.

When you set the ordering clause to 'ENQ_TIME, PRIORITY' the query against the queue table looks like:

ORDER BY ..., ..., ENQ_TIME, STEP, PRIORITY, .... That STEP is incremented if the ENQ_TIME is the same as the last enqueue time from the same session. If you have a session that enqueues three messages in the same second they will have a STEP of 1, 2, 3,.... This explains the weird ordering when they get dequeued because you see messages that were enqueued like this:

(enq_time, step, priority)

Session 1: Session 2:

(14:21:01, 1, 100)
(14:21:01, 2, 101)
(14:21:01, 3, 102)
			(14:21:01, 1, 103)
			(14:21:01, 2, 104)
			(14:21:01, 3, 105)
(14:21:02, 1, 106)
(14:21:02, 2, 107)
			(14:21:02, 1, 108)
			(14:21:02, 2, 109)

You get a dequeue stream like this:

(14:21:01, 1, 100)
(14:21:01, 1, 103)
(14:21:01, 2, 101)
(14:21:01, 2, 104)
(14:21:01, 3, 102)
(14:21:01, 3, 105)
(14:21:02, 1, 106)
(14:21:02, 1, 108)
(14:21:02, 2, 107)
(14:21:02, 2, 109)

If you didn't know about that step number in there and you just look at enq_time, priority you're left shaking your head wondering what's going on.

Since there's no control over this it essentially means that if you want to guarantee that the dequeue order strictly matches the enqueue order you need to make the queue table sort order 'PRIORITY' and populate the priority from a sequence number.

S-

On Tue, 21 Jun 2005, Karthik wrote:

> Hi there,
>
> Did you happen to get a solution of how to dequeue in the same second
> based on priority?
>
> Thanks, Karthik
>
> From: Martic Zoran <zoran_martic_at_xxxxxxxxx>
> To: srospo_at_xxxxxxxxxxx, oracle-l_at_xxxxxxxxxxxxx
> Date: Wed, 8 Jun 2005 00:08:02 -0700 (PDT)
> Hi Steve,
>
> There is not a magic behind AQ beahviour.
>
> From what you said I assumed it is the proper
> behaviour.
>
> Both sessions at the beggining of dequeue are using
> this kind of SQL (easy to find out if you turned on
> tracing 10046 with binds):
>
> select .... from <queue_tab>
> order by enq_time, priority
> for update skip locked;
>
> the undocumented "skip locked" will skip all locked
> rows by somebody else, so it is easy to find out that
> two simultanious sessions will read different messages
> in the general order by enq_time, priority as stated
> by you.
>
> What you showed us is that both sessions were equally
> fast to process rows one by one.
>
> I am not seeing any problem there.
> What did you want to achieve?
>
> Oh, I get it now. Enqueue time is the time when the
> messages were enqueue (insert) into the queue.
> You probably misread what is the enqueue time.
> Is that true?
>
> I believe that enqueue time is in the order as showed
> with the one session run.
> You can check this by doing direct select on the queue
> table.
>
> Regards,
> Zoran
>
>
> --- Steve Rospo <srospo_at_xxxxxxxxxxx> wrote:
>
> >
> > I'm helping out a colleague here with some strange
> > AQ behaviour. We create
> > a queue with a sort_list of 'ENQ_TIME,PRIORITY'.
> > What we're seeing is
> > that if we have two concurrent sessions enqueuing to
> > the same queue with
> > the PRIORITY value coming from a sequence.
> >
> > The enqueue timeline looks like:
> >
> > Session 1: Session 2:
> >
> > Enqueue priority 10
> > Enqueue priority 11
> > Enqueue priority 12
> > COMMIT
> >
> > Enqueue priority 13
> > Enqueue priority 14
> > Enqueue priority 15
> > COMMIT
> >
> > We do both of these quick succession such that the
> > ENQ_TIME is the
> > same for both of the sessions. After the two
> > sessions run, we dequeue
> > from the queue using a single session and the
> > messages come out like this:
> >
> > Priority 10
> > Priority 13
> > Priority 11
> > Priority 14
> > Priority 12
> > Priority 15
> >
> > We've tried to use the navigation of FIRST_MESSAGE
> > and NEXT_MESSAGE and
> > the behavior doesn't change. Has anyone seen this
> > or know how to fix it?
> >
> >
> > --
> > Stephen Rospo Principal Software Architect
>
>
>
>
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>

-- 
Stephen Rospo        Principal Software Architect
Vallent Corporation (formerly Watchmark-Comnitel)
Stephen.Rospo_at_vallent.com           (425)564-8145

This email may contain confidential information. If you received this in
error, please notify the sender immediately by return email and delete this
message and any attachments. Thank you.


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