So, you’ve constructed an awesome multi consumer, event-based processing system built around Oracle AQ. The system’s humming along with messages are flying all over the place when something goes wrong, maybe a network outage or an application fault, and it all grinds to a halt. Within the database things keep going but the messages are piling up.
“No problem” you say, “I’ve set an expiration time on the messages so they’ll get moved onto the exception queue if they hang around for too long.”. Sure enough, the next morning when you get into work and everything is running smoothly again you see that the exception queue has a couple of million of entries in it. After a bit of checking you decide the expired messages are no longer necessary and that they can be removed. “Easy, I’ll just run a purge of the exception queue, using the dbms_aqadm.purge_queue_table routine” you think. Something like:
DECLARE
l_purge_options dbms_aqadm.aq$_purge_options_t;
BEGIN
l_purge_options.block := false;
l_purge_options.delivery_mode := dbms_aqadm.persistent;
dbms_aqadm.purge_queue_table (queue_table => 'TEST_Q_TAB'
,purge_condition => 'qtview.queue = ''AQ$_TEST_Q_TAB_E'' '
,purge_options => l_purge_options);
END;
/
Lunchtime comes around and the purge is still running. “It’s taking a while…”, you muse. A few hours later and you’re thinking about leaving for the day but the purge is still running. By now you’re beginning to wonder what’s going on and whether you should kill the purge and try it again tomorrow.
Well, what’s going on is that using dbms_aqadm.purge_queue_table against a multi conumer queue and specifying a purge condition is slow. Real slow. Here are the results of removing 100,000 messages on a 12.2 database running on my laptop:
- Multi consumer queue: 20 mins, 57.11 secs
- Single consumer queue: 4.87 secs
- Dequeue operation: 34.49 secs
Yep, the purge operation against a multi consumer queue is around 250 time slower than the same operation against a single consumer queue. Interestingly, it is much faster to dequeue the messages rather than purging them. I raised this with Oracle Support but it appears that this is due to the work that the purge needs to do, i.e. it’s working as designed.
In summary, when it comes to removing messages from a multi-consumer queue you may like to check how many messages there are and dequeue those messages rather than using the dbms_aqadm.purge_queue_table routine.
The test scripts used to create the results shown above are available.
Mark Hoxey 