The AQ notification process has changed from release 10gR2 to 11gR1. The most notable change is the switch from using
DBMS_JOB jobs to
In 10gR2 the notification process goes like this:
- Message gets enqueued to the User queue.
- The EMON process is notified that a message has been placed in the queue.
- EMON enqueues a message into the internal queue
- EMON creates a
DBMS_JOBjob to dequeue from the internal queue.
- The job is run, and dequeues from the internal queue, and then runs the notification callback procedure associated with the User queue.
- The notification procedure then does its business which can include de-queuing the original message from the User queue.
The number of available jobs to run the notifications is limited to the number of
JOB_QUEUE_PROCESSES that have been set up for that instance, and each job dequeues one message.
In 11gR1 the notification process is very similar but instead of creating a
DBMS_JOB job it creates a
This, in itself, is not very different but here’s the big difference: when the
DBMS_SCHEDULER job has completed its current notification, it waits (for a default time of 60 seconds) to see if any more messages appear in the internal queue. Should any new messages appear within this time frame, a new
DBMS_SCHEDULER job is not started, and the message is processed within the existing job.
This saves the expensive cost of creating and starting a new job, but the question then comes—is this scalable?
Oracle have obviously thought about this and have put in place some default values (controlled by hidden parameters) that affect how this feature operates. For example, extra
DBMS_SCHEDULER jobs can be created if the queue length of the internal queues rises; by default this value is every 100 messages. So with a queue length of 300 waiting messages, we should expect to see three concurrent jobs de-queuing from the internal queue.
So let’s take a look at some of the hidden initialization parameters.
||60||This controls how quickly a job will end when there are no messages in the queue.|
||100||This number of outstanding messages
would trigger the startup of a new job.
||50||The number of waiting messages falls by this amount then the number of running jobs would reduce.|
||3||This controls the time gap (in seconds) between starting jobs.|
||20||This controls the maximum number of jobs running at any one time.|
All of these parameters are dynamic but be warned you should only change them with the agreement of Oracle support.
Another hidden initialization parameter that can affect this process is
_emon_regular_ntfn_slaves, which controls the number of slave processes automatically started and defaults to 4. This parameter is static.
I have not played with any of these parameters but I am glad to know of their existence should there come a need to tune the notification processing as a whole.