DEQUEUE with FOREVER wait time hangs Oracle when there is no entry to be dequeued

3004

I am using a multiconsumer queue and whenever I try to get the next message from the queue with the FOREVER wait time or a wait time greater than 0 and there is not entry in the queue to be dequeued, Oracle seems to hang for a couple of seconds. This time can go from 2 seconds and more than 30 seconds.

During that hang time, I cannot even create a new connection to Oracle, run a query in another session. Everything seems to be hanging. At the same time the machine is mostly idle during that time.

This hang time comes and goes until a message is available to be dequeued or the dequeue call returns when its wait time has elapsed.

Here is the code I use to dequeue the next message with wait time FOREVER from the default.

=============================================

DECLARE

msg_queue_msgid msg_queue_tab.msgid%TYPE;
q_opts DBMS_AQ.dequeue_options_t;
msg_opts DBMS_AQ.message_properties_t;
data msg_queue_t;

BEGIN

q_opts.consumer_name := 'ROUTER';

DBMS_AQ.DEQUEUE (queue_name => 'msg_queue',
dequeue_options => q_opts, message_properties => msg_opts,
payload => data,
msgid => msg_queue_msgid);

END;

=============================================

Is this related to Oracle configuration or anything special has to be done to use AQ?
null

3004

In which mode your database server is running? Probably if its in dedicated mode it will hang u can check it by changing the database mode.
null