Deferred processing

Alexander Chervinsky

Hi oracle community experts,

I have a question for which I haven’t found an answer yet.

I have a queue (AQ) of events to be processed, but I want the processing to be single-threaded, i.e. I want only one job running if there are events to process. And I want no jobs running if there are no events in the queue, so that I don’t spend server resources (sessions, job queue processes).

As far as I understand, event-based jobs don’t support this out of the box. They have parameter parallel_instances with the following description:

For event-based jobs only.

If TRUE, on the arrival of the specified event, the Scheduler creates a new lightweight job to handle that event, so multiple instances of the same event-based job can run in parallel.

If FALSE, then an event is discarded if it is raised while the job that handles it is already running,

It means that with parallel_instances=false I will miss events that come when the job is running. With parallel_instances=true I will have multiple instances of the same job running at the same time. I don’t want to miss events, and I want max one job running. Does anyone know a good solution for this?

It is needed for deferred processing, so that we don’t spend writers’ time on processing, and return control to them as soon as possible, guaranteeing that subsequent processing will happen. I need something like job_queue_processes, only not global, but for a single job.

upd: I found dbms_scheduler.create_resource procedure, but unfortunately I will not be able to use it, because it appeared it oracle 12.2, and we have only 11.2 and 12.1.

upd2: dbms_scheduler.set_resource_constraint exists in 12.1 documentation, it seems I’m on the right path, now I need to figure out how to use it without create_resource

upd3: there is no dbms_scheduler.set_resource_constraint in 12.1, so it looks like I found a documentation bug.

Alexander Chervinsky

Answering to myself. The best solution I could find is to have a dispatcher job running all the time, checking if there’s anything to process in relevant queues, and starting processing jobs.