Skip to Main Content
  • Questions
  • deq_condition on dbms_aq.dequeue not helping with improving performance in Oracle AQ

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Tino.

Asked: September 29, 2021 - 6:20 am UTC

Last updated: October 06, 2021 - 1:19 pm UTC

Version: 18.14.0.0.0

Viewed 100+ times

You Asked

Hi Tom,

I have an AQ and the Queue table [QT_WRITE_OFF] has 40,196, 299 records. The task is to do dequeue about 28 million items[which meet a certain criterion] from this queue.
Unfortunately, the queue was left to grow too big and I have to find a way of de-queueing. The dbms_aq.dequeue takes way too long to process.
The deq_condition we've isn't helping much because the column isn't part of the index on the q_table, so the deq_condition still does a full table scan.

May you please kindly advise on an approach/optimization which can improve performance of the dequeue?
Can I use a non-payload column in the deq_condition?

The script:
********************************************
DECLARE

v_dequeueoptions   dbms_aq.dequeue_options_t;
v_msgprop          dbms_aq.message_properties_t;
v_msgid            raw (16);
v_payload          ttableid;

CURSOR woff_trxns IS
  SELECT t.rowid,
         t.msgid,
         t.user_data.obj_id obj_id,
             
from trxns tut, --normal partitioned table
     qt_write_off t  --q_table
  where tut.trxn_type = 1
  and t.user_data.obj_id =tut.obj_id
  and tut.gen_status = 'AFAIP';

  TYPE c1data IS TABLE OF woff_trxns%ROWTYPE;
  c1rec   c1data;
  handle_exception EXCEPTION;

BEGIN

 OPEN woff_trxns;
  LOOP
    FETCH woff_trxns BULK COLLECT INTO c1rec LIMIT 1000;
    EXIT WHEN c1rec.COUNT = 0;

   FOR i IN 1..c1rec.count LOOP
    v_dequeueoptions.deq_condition   := 'tab.user_data.obj_id = ' || c1rec(i).obj_id;
    v_dequeueoptions.wait            := DBMS_AQ.no_wait;

    dbms_aq.dequeue (
      queue_name           => 'Q_WRITE_OFF',
      dequeue_options      => v_dequeueoptions,
      message_properties   => v_msgprop,
      payload              => v_payload,
      msgid                => v_msgid
    );
  

 
    COMMIT;
    END LOOP;
  END LOOP;
  CLOSE woff_trxns;
  COMMIT;
 errPos  := 5;
 EXCEPTION
      WHEN OTHERS THEN
   Error_Log.LogErr(v_descr,
                          'Update',
                          errPos,
                          Error_Log.ERR_LEVEL_ERROR,
                          SQLERRM);
 END;

***************************************************
The queue was created as a normal queue.

begin
  sys.dbms_aqadm.create_queue(
    queue_name => 'Q_WRITE_OFF',
    queue_table => 'QT_WRITE_OFF',
    queue_type => sys.dbms_aqadm.normal_queue,
    max_retries => 5,
    retry_delay => 0,
    retention_time => 0);
end;
/

-- Create table
begin
  sys.dbms_aqadm.create_queue_table(
    queue_table => 'QT_WRITE_OFF',
    queue_payload_type => 'TTABLEID',
    sort_list => 'PRIORITY, ENQ_TIME',
    compatible => '10.0.0',
    primary_instance => 0,
    secondary_instance => 0,
    storage_clause => 'tablespace VPC_DATA pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited )');
end;
/

AQ$_QT_WRITE_OFF_I [q_name, local_order_no, state, priority, enq_time, step_no, chain]

/ 
CREATE OR REPLACE TYPE "TTABLEID" as object
  (
    obj_id number(16)
  );


Thank you for your assistance. The LiveSQL Link isn't available at the moment.

and we said...

You've got 28 million items to dequeue?! Ouch! That's going to take a while.

Here are some things to try:

Remove the commit from inside the dequeue loop

Processing 28 million commits adds some overhead itself. Ideally you'd only commit after the whole process is complete; at a minimum move this to be in the outer loop (so you commit every 1,000 messages).

Either way you'll want to review the error handling process to avoid getting most the way through the process, have it hit an error, then rollback.

Check DBMS_AQ.DEQUEUE_ARRAY

This enables you to dequeue a batch of messages in one call.

Remove the cursor and just dequeue

I'm not sure why you've got the cursor to find the enqueued messages. The point of queues is you just run the dequeue process! If you need to dequeue specific messages the dequeue conditions should be able to help (see below).

Querying and fetching 28 million rows will also take a non-trivial amount of time itself. Removing this, the every-message commit, and switching to array dequeuing should give you a decent gain.

Can I use a non-payload column in the deq_condition?

As the docs say:

Dequeue condition is an expression based on the message properties, the message data properties and PL/SQL functions. A deq_condition is specified as a Boolean expression using syntax similar to the WHERE clause of a SQL query. This Boolean expression can include conditions on message properties, user data properties (object payloads only), and PL/SQL or SQL functions (as specified in the where clause of a SQL query). Message properties include priority, corrid and other columns in the queue table.

https://docs.oracle.com/en/database/oracle/oracle-database/18/arpls/DBMS_AQ.html#GUID-D95A89C8-516A-4AF9-8F1F-26B4C6E64420

So yes. Let us know how you get on.

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