0

I work with a piece of software (WMS) where Oracle PL/SQL packages are a huge part of the business logic. I would like to introduce a way to asynchronously do some calculations which are triggered by an event. For some better understanding, we are to introduce some freight estimation which should be calculated whenever a new order is submitted to the database. It is vital that the calculation is made on another session/thread than the original one committing the order.

My first approach was to use an advanced queue (AQ). Whenever an order is created, a message is enqueued to the AQ. Then I got three ways of processing the message (at least I have come up with):

  1. Using an infinite DBMS Scheduler job I will create a job that runs for ever which will call the dequeue method on the AQ. In this way, the job is able to process the message and do some calculation. Pros: It will process the message immediately it has been submitted. Cons: An infinitely running job is not easily stopped.

  2. Using a DBMS Scheduler job running every X seconds Pros: It is possible to stop the job Cons: It will not process the message immediately

  3. Using a trigger on the AQ to create a new job This is another approach. With this approach I can create a trigger that fires whenever a message is enqueued to the AQ. The logic of the trigger will then create a DBMS Scheduler job that dequeues the AQ and auto drops. Pros: It runs somewhat immediately Cons: The database will be creating and dropping jobs all the time. I am not sure if this is an issue, but it gut-feeling about this is not great.

Anyone with any experience with this?

Valsby
  • 13
  • 1

2 Answers2

1
  1. Using an infinite DBMS Scheduler job I will create a job that runs for ever which will call the dequeue method on the AQ. In this way, the job is able to process the message and do some calculation. Pros: It will process the message immediately it has been submitted. Cons: An infinitely running job is not easily stopped.

While event-driven jobs are always listening, that is kind of the point. They can still be enabled/disabled, just like any other job. Your first instinct is the correct one, as this option provides the greatest responsiveness.

  1. Using a DBMS Scheduler job running every X seconds Pros: It is possible to stop the job Cons: It will not process the message immediately

All this does is introduce a lag time, as you've noted. Other than that, there is no difference in practical terms between options 1 and 2. Jobs can still be enabled and disabled in the same way.

  1. Using a trigger on the AQ to create a new job This is another approach. With this approach I can create a trigger that fires whenever a message is enqueued to the AQ. The logic of the trigger will then create a DBMS Scheduler job that dequeues the AQ and auto drops. Pros: It runs somewhat immediately Cons: The database will be creating and dropping jobs all the time. I am not sure if this is an issue, but it gut-feeling about this is not great.

Using dynamic DDL in application logic is pretty much always a bad idea. It obfuscates the functionality of your code, adds needless complexity, and is often considered a security risk.

pmdba
  • 6,457
  • 2
  • 6
  • 16
0

Perhaps simpler than using AQ, you can do your own queuing by inserting an event record into a (normal) "queue" table, while another process is polling on it. But rather than use the scheduler to initiate every polling event, use it only to start up a 24/7 agent process which does a constant loop to poll and calls dbms_lock.sleep to add a second or so of sleep time in each loop so it doesn't tie up a CPU. I have found this to be quite workable.

A constantly running process if initiated by the scheduler at the start will be auto re-initiated by the scheduler if it gets killed for any reason. If you put a interval of 1 minute in the scheduler, as long as the process is still running it won't do anything (it knows it's still running). But if the process dies (hopefully a rare situation), it will restart it within a minute. You can of course make that even shorter window. But the key thing is, using dbms_lock.sleep for the actual polling loop allows you to do much faster polling than you can using a scheduler. You can even go sub-second if you need.

After working an event, you delete the event record or update it with status back to the calling process (if needed) and then the calling process can acknowledge receipt of the status-back by deleting the record itself. Whatever works best for you.

To stop the job, you can simply call dbms_scheduler.stop_job at any time. It's not hard to stop, particularly with the sleep.

Paul W
  • 5,507
  • 2
  • 2
  • 13