Forum Stats

  • 3,836,735 Users
  • 2,262,175 Discussions
  • 7,900,088 Comments

Discussions

Urgent: Oracle AQ process poor performance.

3210004
3210004 Member Posts: 1
edited Apr 21, 2016 2:47AM in Advanced Queueing

Hi people.

Please your help!!!

I have designed a solution which uses Oracle advanced queuing for transporting data from a DB to another.
In general, I have a single multiple consumer queue table. There are 32 queues with 96 subscribers.
Each subscriber is properly registered to its queue using its own PLSQL callback procedure.
Each callback procedure "does what it have to do" and before have finished, it enqueues the message to the next subscriber.

In general it works fine for a single message. When a single message is queued, all 96 susbcribers promptly consume their messages as soon as they are available in the correspondent queues, and the whole process is finished in 9 seconds. But when I enqueue 1000 messages, the first 200-250 messages still keep the high performance. The next 300 messages are not so fast. The last 500 messages have very poor performance.

I have noticed that for the last ones, the message is queued but the callback procedure is not executed immediately.
The callback procedure starts 2-5 minutes after the message is available in the queue.

Do you know why it happen? I have tried with persistent and buffered queues (streams pool size 1GB) but in both situations, the same problem.
Maybe oracle have a parameter that limits the number os callback processes in parallel, something like that,

Please, your comments are important to me.

Thanks on advance.

Ivan.

Answers

  • WGabriel
    WGabriel Member Posts: 202 Bronze Badge
    edited Apr 1, 2016 9:41AM

    Hello,

    in general, the AQ callback mechanism is like a black box and there are no parameter to influence the time bahavior.

    Concerning performance it would be better to setup dedicated AQ dequeue processes.

    Kind regards,

    WoG

  • Harald Stefan
    Harald Stefan Member Posts: 1
    edited Apr 14, 2016 7:11AM

    Hello,

    we have registered packages running and sometimes we face problems with the notification and that messages are not dequeued automatically.

    This was fixed with 10g and is now back with patchset 11.2.0.4.

    But when i understood it right, your messages are dequeued but with poor performance.

    I have never tried with such amount of consumers and packages/procedures.

    Points from my experience to check:

    - amount of segments of the queue tables

       we had this when many messages are in the queue the queue table adds extents and getting realy slow;

       but when your first messages are good this shouldn't be the problem

    - aq_tm_processes

      check dependencies of processes or check with different values

      not sure but i assume that for each message and consumer you need separate processes and probable the value is to less

      not sure if you are set the processes manual or the automatic process

    By the way: You know the this kind of use is deprecated in 12c.

    Regards

  • gdanby
    gdanby Member Posts: 221 Bronze Badge
    edited Apr 21, 2016 2:47AM

    Is this basic AQ or is it using JMS over AQ ? I've seen severe performance issues when dequeuing with a JMS Property filter, similar to what you're describing.

This discussion has been closed.