The AQ notification process has changed from release 10gR2 to 11gR1. The most notable change is the switch from using DBMS_JOB jobs to DBMS_SCHEDULER jobs. In 10gR2 the notification process goes like this:
SYS.AQ_SRVNTFN_TABLE_Q.DBMS_JOB job to dequeue from the internal 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 DBMS_SCHEDULER job. 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.
| Parameter | Default Value | Description |
|---|---|---|
_srvntfn_job_deq_timeout |
60 | This controls how quickly a job will end when there are no messages in the queue. |
_srvntfn_q_msgcount_inc |
100 | This number of outstanding messages would trigger the startup of a new job. |
_srvntfn_q_msgcount |
50 | The number of waiting messages falls by this amount then the number of running jobs would reduce. |
_srvntfn_jobsubmit_interval |
3 | This controls the time gap (in seconds) between starting jobs. |
_srvntfn_max_concurrent_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.
Ready to optimize your Oracle Database for the future?