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.
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 loopProcessing 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_ARRAYThis enables you to dequeue a batch of messages in one call.
Remove the cursor and just dequeueI'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.