Home » Server Options » Streams & AQ » Dequeue with RETENTION on queue not working as expected with DELAY (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)
| Dequeue with RETENTION on queue not working as expected with DELAY [message #614940] |
Thu, 29 May 2014 00:26 |
himang
Messages: 282
Registered: March 2005
Location: Bangalore
|
Senior Member |
|
|
Hi,
I have noticed some strange behavior when using AQ with DELAY & RETENTION_TIME. If we have message in WAIT status and queue has retention_time, dequeue giving message_id works but message is not removed from the queue table. Though if the retention time is 0, then it is removed immediately even though delay is there... Wondering if this is expected behavior or bug in Oracle AQ.
Tried with both REMOVE and REMOVE_NODATA for DEQUEUE_MODE and results are same... below are steps to reproduce...
SQL> BEGIN
2 SYS.DBMS_AQADM.CREATE_QUEUE_TABLE
3 (
4 QUEUE_TABLE => 'XXG.XXG_ORDER_RDD_TL'
5 ,QUEUE_PAYLOAD_TYPE => 'APPS_NE.XXG_ORDER_RDD_OBJ_TYPE'
6 ,COMPATIBLE => '10.0.0'
7 ,SORT_LIST => 'ENQ_TIME'
8 ,MULTIPLE_CONSUMERS => FALSE
9 ,MESSAGE_GROUPING => 0
10 ,SECURE => FALSE
11 );
12 END;
13 /
PL/SQL procedure successfully completed.
--- Creating queue with RETENTION time as 0
SQL> BEGIN
2 SYS.DBMS_AQADM.CREATE_QUEUE
3 (
4 QUEUE_NAME => 'XXG.XXG_ORDER_RDD_QUEUE'
5 ,QUEUE_TABLE => 'XXG.XXG_ORDER_RDD_TL'
6 ,QUEUE_TYPE => SYS.DBMS_AQADM.NORMAL_QUEUE
7 ,MAX_RETRIES => 3
8 ,RETRY_DELAY => 60
9 ,RETENTION_TIME => 0
10 ,COMMENT => 'Queue used to push order list for GRDD Integration'
11 );
12 END;
13 /
PL/SQL procedure successfully completed.
SQL> BEGIN
2 SYS.DBMS_AQADM.START_QUEUE
3 (
4 QUEUE_NAME => 'XXG.XXG_ORDER_RDD_QUEUE'
5 ,ENQUEUE => TRUE
6 ,DEQUEUE => TRUE
7 );
8 END;
9 /
PL/SQL procedure successfully completed.
-- Retention time is 0
SQL> select * from dba_queues
2 where name = 'XXG_ORDER_RDD_QUEUE';
OWNER NAME QUEUE_TABLE
------------------------------ ------------------------------ ------------------------------
QID QUEUE_TYPE MAX_RETRIES RETRY_DELAY ENQUEUE DEQUEUE
---------- -------------------- ----------- ----------- ------- -------
RETENTION
----------------------------------------
USER_COMMENT
----------------------------------------------------------------------------------------------------
NETWORK_NAME
----------------------------------------------------------------------------------------------------
XXG XXG_ORDER_RDD_QUEUE XXG_ORDER_RDD_TL
4189713 NORMAL_QUEUE 3 60 YES YES
0
Queue used to push order list for GRDD Integration
-- Enqueue message with DELAY
SQL> DECLARE
2 l_payload APPS_NE.XXG_ORDER_RDD_OBJ_TYPE := APPS_NE.XXG_ORDER_RDD_OBJ_TYPE();
3 l_enqueue_options DBMS_AQ.ENQUEUE_OPTIONS_T;
4 l_queue_msg_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
5 l_message_handle RAW (16);
6 l_delay NUMBER := 0;
7 BEGIN
8
9 l_payload.order_num := 2345432;
10 l_payload.bu_id := 10;
11 l_payload.grdd_region := '';
12 l_payload.urgent_attention_flag := 'Y';
13 l_queue_msg_properties.correlation := 12345678;
14
15 SELECT ((TRUNC(sysdate+2) - TRUNC(SYSDATE))*24*60*60) into l_delay from dual;
16
17 l_queue_msg_properties.delay := l_delay;
18
19 DBMS_AQ.ENQUEUE(queue_name => 'XXG.XXG_ORDER_RDD_QUEUE'
20 ,enqueue_options => l_enqueue_options
21 ,message_properties => l_queue_msg_properties
22 ,payload => l_payload
23 ,msgid => l_message_handle);
24 COMMIT;
25 END;
26 /
PL/SQL procedure successfully completed.
SQL> select msg_id, msg_state, delay,enq_timestamp, user_data
2 from xxg.AQ$XXG_ORDER_RDD_TL;
MSG_ID MSG_STATE DELAY
-------------------------------- ---------------- ---------
ENQ_TIMESTAMP
---------------------------------------------------------------------------
USER_DATA(ORDER_NUM, BU_ID, GRDD_REGION, URGENT_ATTENTION_FLAG)
----------------------------------------------------------------------------------------------------
FA81ED9BB4234C39E0430D73A00A3901 WAIT 31-MAY-14
29-MAY-14 08.30.21.316630 AM
XXG_ORDER_RDD_OBJ_TYPE(2345432, '10', NULL, 'Y')
-- DEQUEUE the message by specific msg_id
SQL> DECLARE
2 dequeue_options DBMS_AQ.dequeue_options_t;
3 message_properties DBMS_AQ.message_properties_t;
4 message_handle RAW(16);
5 payload APPS_NE.XXG_ORDER_RDD_OBJ_TYPE := APPS_NE.XXG_ORDER_RDD_OBJ_TYPE();
6 BEGIN
7 FOR i in (select msg_id from xxg.aq$XXG_ORDER_RDD_TL QT)
8 LOOP
9 dequeue_options.msgid := i.msg_id;
10 dequeue_options.dequeue_mode := dbms_aq.remove;
11 dequeue_options.wait := dbms_aq.NO_WAIT;
12 DBMS_AQ.DEQUEUE(
13 queue_name => 'XXG.XXG_ORDER_RDD_QUEUE',
14 dequeue_options => dequeue_options,
15 message_properties => message_properties,
16 payload => payload ,
17 msgid => message_handle);
18 COMMIT;
19 END LOOP;
20 END;
21 /
PL/SQL procedure successfully completed.
-- message is dequeued and removed from the queue table when the retention time of queue was 0
SQL> select msg_id, msg_state, delay,enq_timestamp, user_data
2 from xxg.AQ$XXG_ORDER_RDD_TL;
no rows selected
-- modifying queue to have retention of 120 seconds.
SQL> Begin
2 DBMS_AQADM.ALTER_QUEUE (
3 queue_name =>'XXG.XXG_ORDER_RDD_QUEUE',
4 retention_time => 120 );
5 Commit;
6 END;
7 /
PL/SQL procedure successfully completed.
SQL> select * from dba_queues
2 where name = 'XXG_ORDER_RDD_QUEUE'
3 /
OWNER NAME QUEUE_TABLE
------------------------------ ------------------------------ ------------------------------
QID QUEUE_TYPE MAX_RETRIES RETRY_DELAY ENQUEUE DEQUEUE
---------- -------------------- ----------- ----------- ------- -------
RETENTION
----------------------------------------
USER_COMMENT
----------------------------------------------------------------------------------------------------
NETWORK_NAME
----------------------------------------------------------------------------------------------------
XXG XXG_ORDER_RDD_QUEUE XXG_ORDER_RDD_TL
4189713 NORMAL_QUEUE 3 60 YES YES
120
Queue used to push order list for GRDD Integration
-- Enqueuing same message again with same delay
SQL> DECLARE
2 l_payload APPS_NE.XXG_ORDER_RDD_OBJ_TYPE := APPS_NE.XXG_ORDER_RDD_OBJ_TYPE();
3 l_enqueue_options DBMS_AQ.ENQUEUE_OPTIONS_T;
4 l_queue_msg_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
5 l_message_handle RAW (16);
6 l_delay NUMBER := 0;
7 BEGIN
8
9 l_payload.order_num := 2345432;
10 l_payload.bu_id := 10;
11 l_payload.grdd_region := '';
12 l_payload.urgent_attention_flag := 'Y';
13 l_queue_msg_properties.correlation := 12345678;
14
15 SELECT ((TRUNC(sysdate+2) - TRUNC(SYSDATE))*24*60*60) into l_delay from dual;
16
17 l_queue_msg_properties.delay := l_delay;
18
19 DBMS_AQ.ENQUEUE(queue_name => 'XXG.XXG_ORDER_RDD_QUEUE'
20 ,enqueue_options => l_enqueue_options
21 ,message_properties => l_queue_msg_properties
22 ,payload => l_payload
23 ,msgid => l_message_handle);
24 COMMIT;
25 END;
26 /
PL/SQL procedure successfully completed.
SQL> select msg_id, msg_state, delay,enq_timestamp, user_data
2 from xxg.AQ$XXG_ORDER_RDD_TL;
MSG_ID MSG_STATE DELAY
-------------------------------- ---------------- ---------
ENQ_TIMESTAMP
---------------------------------------------------------------------------
USER_DATA(ORDER_NUM, BU_ID, GRDD_REGION, URGENT_ATTENTION_FLAG)
----------------------------------------------------------------------------------------------------
FA81ED9BB4244C39E0430D73A00A3901 WAIT 31-MAY-14
29-MAY-14 08.33.15.212850 AM
XXG_ORDER_RDD_OBJ_TYPE(2345432, '10', NULL, 'Y')
-- dequeuing message with giving specific message_id
SQL> DECLARE
2 dequeue_options DBMS_AQ.dequeue_options_t;
3 message_properties DBMS_AQ.message_properties_t;
4 message_handle RAW(16);
5 payload APPS_NE.XXG_ORDER_RDD_OBJ_TYPE := APPS_NE.XXG_ORDER_RDD_OBJ_TYPE();
6 BEGIN
7 FOR i in (select msg_id from xxg.aq$XXG_ORDER_RDD_TL QT)
8 LOOP
9 dequeue_options.msgid := i.msg_id;
10 dequeue_options.dequeue_mode := dbms_aq.remove;
11 dequeue_options.wait := dbms_aq.NO_WAIT;
12 DBMS_AQ.DEQUEUE(
13 queue_name => 'XXG.XXG_ORDER_RDD_QUEUE',
14 dequeue_options => dequeue_options,
15 message_properties => message_properties,
16 payload => payload ,
17 msgid => message_handle);
18 COMMIT;
19 END LOOP;
20 END;
21 /
PL/SQL procedure successfully completed.
-- message still existing in WAIT status though this has been dequeued.
SQL> select msg_id, msg_state, delay,enq_timestamp, user_data
2 from xxg.AQ$XXG_ORDER_RDD_TL;
MSG_ID MSG_STATE DELAY
-------------------------------- ---------------- ---------
ENQ_TIMESTAMP
---------------------------------------------------------------------------
USER_DATA(ORDER_NUM, BU_ID, GRDD_REGION, URGENT_ATTENTION_FLAG)
----------------------------------------------------------------------------------------------------
FA81ED9BB4244C39E0430D73A00A3901 WAIT 31-MAY-14
29-MAY-14 08.33.15.212850 AM
XXG_ORDER_RDD_OBJ_TYPE(2345432, '10', NULL, 'Y')
-- cannot dequeue again which is expected as it is already dequeued but sitting in WAIT status.
SQL> DECLARE
2 dequeue_options DBMS_AQ.dequeue_options_t;
3 message_properties DBMS_AQ.message_properties_t;
4 message_handle RAW(16);
5 payload APPS_NE.XXG_ORDER_RDD_OBJ_TYPE := APPS_NE.XXG_ORDER_RDD_OBJ_TYPE();
6 BEGIN
7 FOR i in (select msg_id from xxg.aq$XXG_ORDER_RDD_TL QT)
8 LOOP
9 dequeue_options.msgid := i.msg_id;
10 dequeue_options.dequeue_mode := dbms_aq.remove;
11 dequeue_options.wait := dbms_aq.NO_WAIT;
12 DBMS_AQ.DEQUEUE(
13 queue_name => 'XXG.XXG_ORDER_RDD_QUEUE',
14 dequeue_options => dequeue_options,
15 message_properties => message_properties,
16 payload => payload ,
17 msgid => message_handle);
18 COMMIT;
19 END LOOP;
20 END;
21 /
DECLARE
*
ERROR at line 1:
ORA-25263: no message in queue XXG.XXG_ORDER_RDD_QUEUE with message ID
FA81ED9BB4244C39E0430D73A00A3901
ORA-06512: at "SYS.DBMS_AQ", line 335
ORA-06512: at line 12
-- after 120 seconds the message becomes READY but cannot be dequeued by giving message_id but normal dequeue would work...
SQL> select msg_id, msg_state, delay,enq_timestamp, user_data
2 from xxg.AQ$XXG_ORDER_RDD_TL
3 /
MSG_ID MSG_STATE DELAY
-------------------------------- ---------------- ---------
ENQ_TIMESTAMP
---------------------------------------------------------------------------
USER_DATA(ORDER_NUM, BU_ID, GRDD_REGION, URGENT_ATTENTION_FLAG)
----------------------------------------------------------------------------------------------------
FA81ED9BB4244C39E0430D73A00A3901 READY 31-MAY-14
29-MAY-14 08.33.15.212850 AM
XXG_ORDER_RDD_OBJ_TYPE(2345432, '10', NULL, 'Y')
SQL> DECLARE
2 dequeue_options DBMS_AQ.dequeue_options_t;
3 message_properties DBMS_AQ.message_properties_t;
4 message_handle RAW(16);
5 payload APPS_NE.XXG_ORDER_RDD_OBJ_TYPE := APPS_NE.XXG_ORDER_RDD_OBJ_TYPE();
6 BEGIN
7 FOR i in (select msg_id from xxg.aq$XXG_ORDER_RDD_TL QT)
8 LOOP
9 dequeue_options.dequeue_mode := dbms_aq.remove;
10 dequeue_options.wait := dbms_aq.NO_WAIT;
11 DBMS_AQ.DEQUEUE(
12 queue_name => 'XXG.XXG_ORDER_RDD_QUEUE',
13 dequeue_options => dequeue_options,
14 message_properties => message_properties,
15 payload => payload ,
16 msgid => message_handle);
17 COMMIT;
18 END LOOP;
19* END;
SQL> /
PL/SQL procedure successfully completed.
SQL> select msg_id, msg_state, delay,enq_timestamp, user_data
2 from xxg.AQ$XXG_ORDER_RDD_TL
3 /
MSG_ID MSG_STATE DELAY
-------------------------------- ---------------- ---------
ENQ_TIMESTAMP
---------------------------------------------------------------------------
USER_DATA(ORDER_NUM, BU_ID, GRDD_REGION, URGENT_ATTENTION_FLAG)
--------------------------------------------------------------------------------
--------------------
FA81ED9BB4244C39E0430D73A00A3901 PROCESSED 31-MAY-14
29-MAY-14 08.33.15.212850 AM
XXG_ORDER_RDD_OBJ_TYPE(2345432, '10', NULL, 'Y')
Regards
|
|
|
|
Goto Forum:
Current Time: Wed Dec 04 01:58:31 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
