Oracle Database

AQ Notifications in Oracle 11gR1

2 min read

Oct 15, 2009 12:00:00 AM

Introduction

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:

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 SYS.AQ_SRVNTFN_TABLE_Q.
EMON creates a DBMS_JOB job 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.

Processing

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.

Conclusion

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.