STREAMS PROPAGATION ERROR: ORA-25307 ENQUEUE RATE TOO HIGH. ENABLE FLOW CONTROL

Applies to

Contents

PROBLEM

ORA-25307 "Enqueue rate too high. Enable flow control"

EXPLANATION

Flow control in 10g is automatically invoked by default.

DBA_QUEUE_SCHEDULES will display this informational message when the automatic flow control (10g feature of Streams) has been invoked.

This warning message typically occurs when the target site is unable to keep up with the rate of messages flowing from the source site. Although, this is a warning message, it is also a possible indication of another problem. Other than checking that the apply process is running normally on the target site, no action is required by the DBA. Propagation and the capture process will be resumed automatically when the target site is able to accept more messages. In some situations, propagation may become disabled (if the number of failures is 16). In these situations, the propagation can be re-enabled manually.

You can check the propagation using:

select schema,qname,destination,schedule_disabled,failures,last_error_msg

from dba_queue_schedules;

You can reschedule the propagation using DBMS_AQADM.SCHEDULE_PROPAGATION

10.2 Flow Control Query

COLUMN queue_schema FORMAT A12

COLUMN queue_name FORMAT A20

SELECT

queue_schema, queue_name, unbrowsed_msgs, overspilled_msgs,

memory_usage, publisher_state

FROM V$BUFFERED_PUBLISHERS;

NOTE: The Flow Control query will show:

• the queue name and owner
• the number of messages that have been enqueued, but not browsed
• the number of messages that have been spilled, but not browsed
• the percentage of the streams pool that is being used (or 0 if there is no streams pool)
• the state of the publisher. Possible values are:

• PUBLISHING MESSAGES (normal)
• IN FLOW CONTROL: TOO MANY UNBROWSED MESSAGES
• IN FLOW CONTROL: OVERSPILLED MESSAGES
• IN FLOW CONTROL: INSUFFICIENT MEMORY AND UNBROWSED MESSAGES

This last column will really help to determine why flow control is being enabled for a system and what can be done to resolve the matter.

If the state is IN FLOW CONTROL: TOO MANY UNBROWSED MESSAGES, then you can run the following query to determine which subscriber is not dequeuing messages from the queue:

SELECT subscriber_name, cnum_msgs, total_dequeued_msg, total_spilled_msg

FROM V$BUFFERED_SUBSCRIBERS;

A comparison of the total number of messages enqueued for the subscriber

(CNUM_MSG) and the number of messages dequeued by that subscriber

(TOTAL_DEQUEUED_MSG) should indicate which subscriber has fallen behind.