Home » Server Options » Streams & AQ » Performance issues when using AQ notification with one consumer (Oracle
Performance issues when using AQ notification with one consumer [message #387511] Thu, 19 February 2009 11:50
Messages: 4
Registered: August 2006
Location: Dublin
Junior Member
We have a system to load data from a reservation database to a reporting database
A a certain point in the proces, a message with the identifier of the reservation is enqueued to a 1st queue (multi-consumer) on the same DB and then propagated to a similar 2nd queue on the REP database.
This 2nd queue (multi-consumer) has AQ notification enabled (with one consumer) which calls the queue_callback procedure which
- dequeues the message
- calls a procedure to load the Resv data into the Reporting schema (through DB link)

We need each message to be processed ONLY ONCE thus the usage of one single subscriber for

But when load testing our application with multiple threads, the number of records created in the Reservation Database becomes quite large, meaning a large number of messages going through the first queue and propagating to the second queue (very quickly).
But messages are not processed fast enough by the 2nd queue (notification) which falls behind. It seems that messages are processed sequentially, these need to be processed in parallel somehow.

I would like to keep an automatic process for dequeuing and processing messages (no need to set up dbms_jobs to dequeue etc..) like notification or similar.

So having read articles, I feel I need to use:
- multiple subscribers to the 2nd queue where each message is processed only by one subscriber
(maybe using a rule : say 10 subscribers S0 to S10 with Si processing messages where last number of the identifier is i )
problem with this is that there is an attempt to process the message for each subscriber, isn't there?
- a different dequeuing method where many processes are used in parallel , with each message is processed only by one subscriber

Does anyone have experience and recommendations to make on how to enable some parallel processing and improve throughput of messages?


Previous Topic: Best method to achieve
Next Topic: Changing One way replication to Bidirection replication
Goto Forum:

Current Time: Sat Dec 02 09:55:37 CST 2023