Hello,

I have an Oracle Advanced Queuing queue and would like to be able to process this queue from inside of the database, as opposed to using an external app server. However, I am concerned about the scalability of internal solutions.

Please assume the following:

1. The queue receives an arbitrary number of messages.
2. Each message results in a PLSQL procedure being called, which can take an arbitrary amount of time.
3. You want to limit the number of messages that can be processed at once to some value N.

---

Solution #1: Run N permanent DBMS_SCHEDULER jobs that loop and call DBMS_AQ.DEQUEUE with WAIT_FOREVER. This is good because you can easily cap how many jobs you want to have processing this queue by adjusting the N number of permanent DBMS_SCHEDULER jobs. This is bad because all of these permanent jobs will reduce the available JOB_QUEUE_PROCESSES. It is fine if you only need a handful of jobs to process your queue, but as you scale the number of jobs up, eventually you will degrade the other unrelated jobs that need to run in the system.

Does calling DBMS_LOCK.SLEEP or DBMS_AQ.DEQUEUE inside a DBMS_SCHEDULER job free up a JOB_QUEUE_PROCESSES slot while the job is sleeping? My guess is no.

---

Solution #2: Use a PL/SQL callback, and in the callback, create a one-time DBMS_SCHEDULER job per message, and use a common resource constraint, such that only N scheduler jobs can run at once. For example, if you set a cap of 128 jobs in your resource constraint, and you receive 1000 messages, the PL/SQL callback will create 1000 jobs, but only 128 jobs will be running at once, and the rest will be blocked.

The downside here is that you have to create a whole dbms_scheduler job to process a message. This will increase the time between receiving a message and starting to process it, and just seems like an overall heavy solution. Lightweight jobs won't help because the resource constraints don't work for lightweight jobs.

In fact, you might as well not use AQ at all if you go down this route. Instead of writing messages to a queue, which later calls DBMS_SCEDHULER.CREATE_JOB, you could simply call DBMS_SCHEDULER.CREATE_JOB directly with a resource constraint.

---

Solution #3: Use an external app server. Run N threads, where each thread grabs a connection from a connection pool, loops and calls DBMS_AQ.DEQUEUE with WAIT_FOREVER. This is the best approach because you can set N to cap the number of connections processing messages in parallel easily, and you do not have to block up and slots in JOB_QUEUE_PROCESSES.

However, this has downsides. Your app server often has much more downtime than your database due to releases, network partitions, and many other various issues. If your session which is executing a long running PL/SQL procedure is terminated, you cannot assume whether the PL/SQL procedure on the server will complete or be stopped. While this is also true for DBMS_SCHEDULER Jobs that end up getting killed when the database has unexpected downtime, the frequency of downtime is much less compared to an app server.


---

Event based jobs won't work, because you have to use parallel_instances to true (or else messages will be skipped), which would then force the use of lightweight jobs, which would then prevent the use of resource constraints. Even if you could use normal jobs or set resource constraints, you might as well not use AQ and just schedule the jobs yourself with a resource constraint.

---

Is there any solution that can be implemented internally to the database, which does not permanently block JOB_QUEUE_PROCESSES slots even when there is no messages in the queue, and which doesn't rely on excessive one time job creation via DBMS_SCHEDULER?