Purging AQ can be slow

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:

  l_purge_options dbms_aqadm.aq$_purge_options_t;
   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);

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.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s