On May 31, 10:13 am, z1h..._at_gmail.com wrote:
> On May 30, 5:21 pm, z1h..._at_gmail.com wrote:
>
>
>
>
>
> > On May 30, 4:12 pm, DA Morgan <damor..._at_psoug.org> wrote:
>
> > > z1h..._at_gmail.com wrote:
> > > > Hi,
> > > > When I dequeue a message from the queue using the DBMS_AQ.REMOVE
> > > > option, the message still stays in the queue table (test_qtbl) long
> > > > after the dequeue is complete. I am under the impression that this
> > > > option will remove the message from the queue table when the message
> > > > is dequeued. I have also specified the retention time while creating
> > > > the queue to zero (not to retain any messages after dequeue). After
> > > > the dequeue, if I try to dequeue the same message again it is not
> > > > available for dequeuing.
> > > > Although this behavior is not affecting my application, it is very
> > > > annoying to see the messages in the queue long after it has been
> > > > processed.
>
> > > > I have given a sample code below which demonstrates this. Am I missing
> > > > something here?
>
> > > > create or replace type test_typ as object (msg varchar2(5))
>
> > > > begin
> > > > dbms_aqadm.create_queue_table(queue_table => 'test_qtbl',
> > > > queue_payload_type => 'test_typ', multiple_consumers => true);
> > > > dbms_aqadm.create_queue(queue_name => 'test_queue', queue_table =>
> > > > 'test_qtbl', retention_time => 0);
> > > > dbms_aqadm.start_queue('test_queue');
> > > > end;
>
> > > > declare
> > > > l_queue_options DBMS_AQ.ENQUEUE_OPTIONS_T;
> > > > l_message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
> > > > l_message_id RAW(16);
> > > > l_type test_typ;
>
> > > > l_recipient_list DBMS_AQ.AQ$_RECIPIENT_LIST_T;
> > > > l_agent sys.aq$_agent := sys.aq$_agent('agent1', null, null);
> > > > begin
> > > > l_recipient_list(0) := l_agent;
> > > > l_message_properties.recipient_list := l_recipient_list;
>
> > > > l_type := test_typ('Help');
> > > > dbms_aq.enqueue('test_queue', l_queue_options, l_message_properties,
> > > > l_type, l_message_id);
> > > > end;
>
> > > > declare
> > > > l_type test_typ;
> > > > l_queue_options DBMS_AQ.DEQUEUE_OPTIONS_T;
> > > > l_message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
> > > > l_message_id RAW(16);
> > > > begin
> > > > l_queue_options.wait := 3;
> > > > l_queue_options.dequeue_mode := DBMS_AQ.REMOVE;
> > > > l_queue_options.consumer_name := 'agent1';
> > > > dbms_aq.dequeue('test_queue', l_queue_options, l_message_properties,
> > > > l_type, l_message_id);
> > > > DBMS_OUTPUT.put_line(l_type.msg);
> > > > end;
>
> > > > Regards,
> > > >z1hou1
>
> > > The purpose of the queue table includes an audit trail of messages. If
> > > .REMOVE removed the messages your audit trail would evaporate. The
> > > product is doing precisely what the documentation indicates that it
> > > should.
>
> > > If you want a DELETE statement then you should write that into your
> > > application code.
> > > --
> > > Daniel A. Morgan
> > > University of Washington
> > > damor..._at_x.washington.edu
> > > (replace x with u to respond)
> > > Puget Sound Oracle Users Groupwww.psoug.org-Hidequoted text -
>
> > > - Show quoted text -
>
> > Thanks Daniel for your reply. Why is it then that when we have a
> > single consumer queue, that the dequeue operation physically deletes
> > the row from the queue table? It is this characteristic that confused
> > me and prompted me to post the earlier question. Why does the same
> > operation on a multiple consumer queue not result in an empty queue
> > table after the dequeue operation.
>
> > Regards,z1hou1- Hide quoted text -
>
> > - Show quoted text -
>
> I finally found the answer to this issue and thought that I will post
> the reply. I would like to assure anyone concerned that I am not
> annoyed anymore with the fact that the dequeued messages are not
> getting deleted in multi-consumer queues. :).
>
> In release 8.0.x when two or more processes/threads that are using
> different consumer_names are dequeuing from a queue, only one process/
> thread can dequeue a given message in the LOCKED or REMOVE mode at any
> time. What this means is that other consumers that need to the dequeue
> the same message will have to wait until the consumer that has locked
> the message commits or aborts the transaction and releases the lock on
> the message. However, while release 8.0.x did not support concurrency
> among different consumers for the same message., with release 8.1.6
> all consumers can access the same message concurrently. The result is
> that two processes/threads that are using different consumer_name to
> dequeue the same message do not block each other. AQ achieves this
> improvement by decoupling the task of dequeuing a message and the
> process of removing the message from the queue. In release 8.1.6 only
> the queue monitor removes messages from multi-consumer queues. This
> allows dequeuers to complete the dequeue operation by not locking the
> message in the queue table. Since the queue monitor performs the task
> of removing messages that have been processed by all consumers from
> multi-consumer queues approximately once every minute, users may see a
> delay when the messages have been completely processed and when they
> are physically removed from the queue
>
> dbms_aqadm.start_time_manager will start the monitor.
>
> Regards,z1hou1- Hide quoted text -
>
> - Show quoted text -
For anyone interested the solution was found at
http://www.mscd.edu/~ittsdba/oradoc817/appdev.817/a76938/adq08qa5.htm#65610
Received on Thu May 31 2007 - 09:15:58 CDT
