Propagation Issues

Caution: Propagation makes use of the system queue aq$_prop_notify_X (where X is the instance number of the instance where the source queue of a schedule resides) for handling propagation run-time events. These messages in this queue are stored in the system table aq$_prop_table_X (where X is the instance number of the instance where the source queue of a schedule resides). The queue aq$_prop_notify_X should never be stopped or dropped and the table aq$_prop_notify_X should never be dropped for propagation to work correctly.

Optimizing Propagation

In setting the number of JOB_QUEUE_PROCESSES, the DBA should aware that this need is determined by the number of queues from which the messages have to be propagated and the number of destinations (rather than queues) to which messages have to be propagated.

In this release, a new scalable scheduling algorithm has been incorporated for handling propagation. It has been designed to make optimal use of the available job queue processes and also minimize the time it takes for a message to show up at a destination once it has been enqueued into the source queue, thereby providing near OLTP behavior. This algorithm is capable of simultaneously handling an unlimited number of schedules. The algorithm also has robust support for handling various types of failures. While propagation tries to make the optimal use of the available job queue processes, the number of job queue processes to be started also depends on the existence of non-propagation related jobs such as replication jobs. Hence, it is very important to use the following guidelines to get the best results from this new algorithm.

The new algorithm uses the job queue processes as follow: (for this discussion an active schedule is one which has a valid current window):

• If the number of active schedules is less than half the number of job queue processes, the number of job queue processes acquired corresponds to the number of active schedules

• If the number of active schedules is more than half the number of job queue processes, after acquiring half the number of job queue processes multiple active schedules are assigned to an acquired job queue process

• If system is overloaded (all schedules are busy propagating), depending on the availability additional job queue processes will be acquired up to one less than the total number of job queue processes

• If none of the active schedules handled by a process have messages to be propagate then that job queue process will be released

• The algorithm performs automatic load balancing by transferring schedules from a heavily loaded process to a lightly load process such that no process is excessively loaded

The scheduling algorithm places the restriction that at least 2 job queue processes be available for propagation. If there are non-propagation related jobs then more number of job queue processes is needed. If heavily loaded conditions (when there are a large number of active schedules all of which have messages to be propagated) are expected then it is recommended to start a larger number of job queue processes keeping in mind that the job queue processes will be used for non-propagation related jobs as well. In a system which only has propagation jobs, then 2 job queue processes can handle all schedules but higher the number the faster the messages get propagated. Note that, since one job queue process can propagate messages from multiple schedules, it is not necessary to have the same number of job queue processes as the number of schedules.

Handling Failures in Propagation

The new algorithm also has robust support for handling failures. It may not be able to propagate messages from a queue due to various types of failures. Some of the common reasons include failure of the database link, non-availability of the remote database, non-existence of the remote queue, remote queue not started and security violation while trying to enqueue messages into the remote queue. Under all these circumstances the appropriate error messages will be reported in the dba_queue_schedules view. When an error occurs in a schedule, propagation of messages in that schedule is attempted periodically using an exponential backoff algorithm for a maximum of 16 times after which the schedule is disabled. If the problem causing the error is fixed and the schedule is enabled, the error fields that indicate the last error date, time and message will still continue to show the error information. These fields are reset only when messages are successfully propagated in that schedule. During the later stages of the exponential backoff, the time span between propagation attempts can be large in the tune of hours or even days. This happens only when an error has been neglected for a long time. Under such circumstances it may be better to unschedule the propagation and schedule it again.