AQ Notifications in Oracle 11gR1

Posted in: Technical Track

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:

  1. Message gets enqueued to the User queue.
  2. The EMON process is notified that a message has been placed in the queue.
  3. EMON enqueues a message into the internal queue SYS.AQ_SRVNTFN_TABLE_Q.
  4. EMON creates a DBMS_JOB job to dequeue from the internal queue.
  5. The job is run, and dequeues from the internal queue, and then runs the notification callback procedure associated with the User queue.
  6. 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 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.

ParameterDefault ValueDescription
_srvntfn_job_deq_timeout60This controls how quickly a job will end when there are no messages in the queue.
_srvntfn_q_msgcount_inc100This number of outstanding messages
would trigger the startup of a new job.
_srvntfn_q_msgcount50The number of waiting messages falls by this amount then the number of running jobs would reduce.
_srvntfn_jobsubmit_interval3This controls the time gap (in seconds) between starting jobs.
_srvntfn_max_concurrent_jobs20This 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.

Interested in working with Luke? Schedule a tech call.

About the Author

I have been working as a DBA since Oracle V6 for over 20 years.

1 Comment. Leave new

Blogroll Report 09/10/2009-16/10/2009 « Coskan’s Approach to Oracle
October 21, 2009 7:30 pm

[…] Luke Davies-AQ Notifications in Oracle 11gR1 […]


Leave a Reply

Your email address will not be published. Required fields are marked *