Home > Advanced Queue, Oracle > Solution of “ORA-25307: Enqueue rate too high”

Solution of “ORA-25307: Enqueue rate too high”

One of the method of to speed up the performance of Oracle Advanced Queue is using Buffered Messages. In default, maxiumum 5000 non-dequeued messages can reside in memory. If you enqueued more than 5000 messages that have not dequeued yet, you will get an error like : “ORA-25307: Enqueue rate too high”. So, your messages will be written to disk.

Oracle uses this error to prevent memory overflow when you are enqueuing messages. You can increase this number by setting a system parameter.

SQL> alter system set “_buffered_publisher_flow_control_threshold”=500000 scope=both;

System altered

SQL> show parameter buffered;

NAME TYPE VALUE
———————————— ———– ——————————
_buffered_publisher_flow_control_threshold integer 500000

SQL>

After that, you can observe how many messages were spilled to the disk:

SQL> select queue_name,subscriber_name,total_spilled_msg
2 from
3 v$buffered_subscribers
4 ;

QUEUE_NAME SUBSCRIBER_NAME TOTAL_SPILLED_MSG
—————————— —————————— —————–
NLIZT01_STREAM_Q NLIZT01 0
NLCR01_STREAM_Q NLCR01 0
NLER01_STREAM_Q NLER01 0
NLER03_STREAM_Q NLER03 0
NLER02_STREAM_Q NLER02 0
NLCR03_STREAM_Q NLCR03 0
NLCR02_STREAM_Q NLCR02 0
NLIZT03_STREAM_Q NLIZT03 0
NLIZT02_STREAM_Q NLIZT02 0
NLBRS03_STREAM_Q NLBRS03 0
NLBRS02_STREAM_Q NLBRS02 0
NLBRS01_STREAM_Q NLBRS01 0
NLBLK03_STREAM_Q NLBLK03 0
NLBLK02_STREAM_Q NLBLK02 0
NLBLK01_STREAM_Q NLBLK01 0

15 rows selected

SQL>

If you use dbms_capture package, you can use :
alter system set “_capture_publisher_flow_control_threshold”=500000 scope=both;

Advertisements
Categories: Advanced Queue, Oracle
  1. August 30, 2009 at 10:58 am

    yan etkisi-alternatif maliyeti nedir acaba? (iç kilitler(latch) çok artıyor olabilir mi, ki ölçeklenebilirlik düşebilir bu nedenle)

  2. June 8, 2010 at 12:18 pm

    Hi,

    What is the equivalent of this solution in Oracle 10g?

    Thanks!

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: