I wonder if you can shed any light on the following problem we're seeing when we dequeue payloads from AQ. What seems to be happening is whenever we set a delay then all delayed payloads seem to be taking ~3 seconds longer to dequeue than the wait we set.
example
create payload, queue table and start the queue
CREATE OR REPLACE TYPE delaytest_pl AUTHID DEFINER AS OBJECT (test int);
/
EXEC DBMS_AQADM.CREATE_QUEUE_TABLE(queue_table => 'delaytest_qt', queue_payload_type => 'delaytest_pl', multiple_consumers => FALSE );
EXEC DBMS_AQADM.CREATE_QUEUE(queue_name => 'delaytest_q', queue_table => 'delaytest_qt');
EXEC DBMS_AQADM.START_QUEUE(queue_name => 'delaytest_q');
enqueue a payload with a wait delay and then dequeue that message
SET SERVEROUTPUT ON;
DECLARE
enq_opts DBMS_AQ.enqueue_options_t;
enq_msg_props DBMS_AQ.message_properties_t;
msg_in delaytest_pl := delaytest_pl(1); -- setup payload
msgid RAW(16);
--
deq_opts DBMS_AQ.dequeue_options_t;
deq_msg_props DBMS_AQ.message_properties_t;
msgid_out RAW(16);
msg_out delaytest_pl;
BEGIN
-- set a 5 second delay
enq_msg_props.delay := 5;
-- enqueue our payload
dbms_output.put_line(TO_CHAR(SYSTIMESTAMP, 'MM-DD-YYYY HH24:MI:SS.FF') || ' ENQUEUE');
DBMS_AQ.ENQUEUE(
queue_name => 'bet.delaytest_q',
enqueue_options => enq_opts,
message_properties => enq_msg_props,
payload => msg_in,
msgid => msgid);
dbms_output.put_line(TO_CHAR(SYSTIMESTAMP, 'MM-DD-YYYY HH24:MI:SS.FF') || ' ENQUEUED');
commit;
deq_opts.wait := 20;
dbms_output.put_line(TO_CHAR(SYSTIMESTAMP, 'MM-DD-YYYY HH24:MI:SS.FF') || ' DEQUEUE');
DBMS_AQ.DEQUEUE(
queue_name => 'bet.delaytest_q',
dequeue_options => deq_opts,
message_properties => deq_msg_props,
payload => msg_out,
msgid => msgid_out);
dbms_output.put_line(TO_CHAR(SYSTIMESTAMP, 'MM-DD-YYYY HH24:MI:SS.FF') || ' DEQUEUED');
commit;
END;
As you can see the dequeud time is 8 seconds rather than 5, obviously if I know there is always an additional 2-3 seconds delay I can reduce the wait period but that causes a problem once we are down at 1-2 second delays (and yes in our usage case 1-2 second delays are very important).
02-06-2017 14:47:05.061781000 ENQUEUE
02-06-2017 14:47:05.068091000 ENQUEUED
02-06-2017 14:47:05.068309000 DEQUEUE
02-06-2017 14:47:13.084924000 DEQUEUED
Many Thanks
Jason
That is expected behaviour, although the docs are perhaps not as explicit as they could be:
"The delay represents a time interval after which the message becomes available to the message consumer."
A delay is basically saying we guarantee that the message will not dequeued *before* the delay has passed, not that we will dequeue right at the moment on the delay expiring.
The reason you see the 'pause' is that a background process wakes up (I *think* its every 3 seconds) and takes care of activating messages that are delayed.
If the time of dequeue is critical, then perhaps set a delay to an earlier moment, along with a "time to activate" timestamp as part of the payload itself. Then you would dequeue and pause for a small amount of time.
(I'm making a whole lot of assumptions here about concurrency, throughput etc etc)