Skip to Main Content
  • Questions
  • Remove row from the queue_table for sharded queue upon dequeue

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, cvr.

Asked: June 11, 2024 - 11:10 am UTC

Last updated: June 13, 2024 - 7:34 am UTC

Version: 19c

Viewed 1000+ times

You Asked

I have been using AQ, and now I am trying to utilize Sharded queues in 19c. I am trying to understand some basics.

I want to have single-consumer queues, and upon dequeue the record should be removed from the queue table. In the AQ (classic queues) it used to delete the record from the queue table upon dequeue. However in sharded queues I am not sure how this works.

Following is the setup I used.

--Cleanup:
begin
    dbms_aqadm.stop_queue( 
        queue_name     => 'my_teq'
    );  
    dbms_aqadm.drop_sharded_queue(
        queue_name     => 'my_teq'
    );
end;
/

-- Create the TEQ
begin
    dbms_aqadm.create_sharded_queue(
        queue_name         => 'my_teq',
        multiple_consumers => false
    );
    -- start the TEQ
    dbms_aqadm.start_queue(
        queue_name         => 'my_teq'
    ); 
end;
/

select name,queue_table,retention,sharded from ALL_QUEUES where name = 'MY_TEQ';
-----
MY_TEQ MY_TEQ 0 TRUE

-- Enqueue

declare
    enqueue_options    dbms_aq.enqueue_options_t;
    message_properties dbms_aq.message_properties_t;
    message_handle     raw(16);
    message            SYS.AQ$_JMS_TEXT_MESSAGE;
begin
    message := SYS.AQ$_JMS_TEXT_MESSAGE.construct;
    message.set_text('{"message":"hello"}');
    dbms_aq.enqueue(
        queue_name           => 'my_teq',           
        enqueue_options      => enqueue_options,       
        message_properties   => message_properties,     
        payload              => message,               
        msgid                => message_handle);
    commit;
end;
/

select * from my_teq;
-- I see there is a record, and with state=0

--Dequeue
declare
    dequeue_options     dbms_aq.dequeue_options_t;
    message_properties  dbms_aq.message_properties_t;
    message_handle      raw(16);
    message             SYS.AQ$_JMS_TEXT_MESSAGE;

begin
    dequeue_options.dequeue_mode  := dbms_aq.remove;
    dequeue_options.wait          := dbms_aq.no_wait;
    dequeue_options.navigation    := dbms_aq.first_message;          
    dbms_aq.dequeue(
        queue_name         => 'my_teq',
        dequeue_options    => dequeue_options,
        message_properties => message_properties,
        payload            => message,
        msgid              => message_handle
    );
    commit;
end;
/

select * from my_teq;
-- I see the data is still there, and all properties are looking same as how it was before



1. How can we remove the entries upon dequeue
2. How does Oracle keep track which records have been dequeued 
3. I see that it auto-creates some internal tables AQ$ _L, _X tables etc. I understand _L table is a log table, and just upon creating the sharded queue its populated with some 100,000+ records. Wondering why!. Is there any maintenance (purging etc) required for such tables?





and Connor said...

My understanding is that shared TE queues are not managed in the same way as the previous AQ concept (ie, a queue table that we delete from).

As you dequeue, we update a row in AQ$_MY_TEQ_L with the message ID of the dequeued message

SQL> select * from "MCDONAC"."AQ$_MY_TEQ_L"
  2  where msgid != hextoraw('FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF')
  3  @pr
==============================
MSGID                         : 00000000000000000000000004660000
INCARNATION                   : 0
SEQ_NUM                       : 0
FLAGS                         : 1
DEQUEUE_TIME                  : 13-JUN-24 07.11.25.098000 AM +00:00
TRANSACTION_ID                : 5.21.73944
DEQUEUE_USER                  : MCDONAC
RETRY_COUNT                   : 0

PL/SQL procedure successfully completed.


Cleanup is managed internally over time by AQ, and is optimised by trying to truncate entire partitions when everything in that partition has been consumed.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library