Is Oracle AQ/Streams of any use in my situation?

Question

I'm writing a workflow system that is driven entirely at each step by explicit human interaction. That is, a task is assigned to a person, that person selects from a few limited options {approve, reject, forward}, and then it is either sent along to the next person or terminated.

Just curious if Oracle Streams/AQ has anything to offer over flat tables managed by regular web application code. The amount of processing after each action is fairly limited and the volume is not terribly high, so there's not really a need to throttle things by throwing them into a queue. What are some of the benefits of introducing a queue structure, or is it overkill for my situation?

Answer 1

There are many reasons a queuing system is beneficial, but I am not sure they apply in your situation. It sounds like you have a single system all stored in a single database. As such, I don't think queuing would provide any advantage over normal tables.

Situations where AQ provides benefits include: - as a mechanism for different systems (multiple databases) to talk to each other

• when you have loosely coupled systems - a producer of messages sending to an unknown number of subscribers

As a way to manage state in a single system, such as you describe, I think Streams/AQ would be overkill.

Answer 2

The big advantage with queuing is it can make concurrency issues that are otherwise really hard (show one and only one thread this record for processing) really easy. Without queueing, you could try, but not ensure, that kind of behavior, and you'd have to end up doing a lot of intermediate state updating and checking for failed threads.

In 10g and below, Oracle implemented the transactional dequeue operation with the SKIP LOCKED syntax which end users weren't permitted. In 11g, that syntax has been exposed to allow people to solve that problem (show me the next record) without requiring AQ implementations.

A secondary advantage of AQ is that the queue cleanup is asynchronous.

The big disadvantage of AQ is it's size and maintenance -- one ends up creating on the order of 7 tables / IOTs for a single persistent queue/topic, and one can't directly maintain those database objects, but you have to do maintenance through the DBMS_AQ and DBMS_AQADM packages.

Answer 3

If your application is really that low volume and assuming a few minutes of latency is acceptable, I'd avoid both and use old school triggers to populate my own logging tables. I would then process these with pl jobs. All to avoid the additional features/complexities AQ brings.