Pythian Group

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: Read the rest of this entry . . .

If you ever thought it was easy to kill an oracle job, let me show you something that gave me a headache today.

We’re testing an application that uses custom code to de-queue and propagate AQ messages between databases. There are nine processes in total, and all of them are submitted as jobs using DBMS_JOB. They are supposed to run all the time, waiting for messages to de-queue. Once in a while, I need to kill them in order to recompile the objects they are executing.

The steps are simple enough: remove/break all jobs in question, kill the jobs that are running, recompile the packages with new the version, and submit/un-break the jobs again. This time, however, things were not going as smoothly as I expected. Let’s have a look.

First I remove all the currently running jobs:
Read the rest of this entry . . .