Pythian Blog: Technical Track

AQ Notifications in Oracle 11gR1

by Luke Davies on Oct 15, 2009 12:00:00 AM

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.
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.
Topics: Technical Track Cassandra

No Comments Yet

Let us know what you think

See all

See all

Subscribe by email
Follow us
Love Your Data
Pythian Logo

© 2024 All rights reserved.