Queuing and Decoupling for Performance…


Some of the data warehousing and Exadata presentations have talked about queuing requests to improve performance. They have suggested that using Resource Manager to throttle the number of active requests results in better performance/throughput compared to letting multiple requests all run simultaneously. The Terabyte Hour session yesterday showed an example of this and sure enough, when they limited the heavy requests to batches of 3, the overall throughput of requests improved.

This kinda links to something I keep banging on about in my PL/SQL presentations, which is decoupling. Don’t do it, queue it…

In any system you have a variety of business functions that have differing importance and differing required turnaround time. It’s good if you can identify this up front so you can consider decoupling some business functions. That way, functions that MUST happen instantly are fired on the spot, while those that can accommodate some lag time are queued for later processing. The acceptable lag for each of these business functions may vary.

Why do I care? Two reasons really:

  • Why hog resources processing low priority tasks when they could be used for high priority tasks? You don’t run your backups and stats collection during peak hours. Why would you waste cycles on low priority business functions when the user experience is poor due to lack of resources.
  • Decoupling allows you to take small transactions and batch them up, allowing you to take advantage of performance features available in both SQL and PL/SQL.

Queuing and decoupling are by no means new concepts, but they seem to have been lost in the mix. It’s interesting to see them being brought back onto the agenda, even with the sort of horsepower provided by Exadata.



Author: Tim...

DBA, Developer, Author, Trainer.

2 thoughts on “Queuing and Decoupling for Performance…”

  1. I agree but with one reservation – don’t queue a unit of work that costs less than the cost of the queue and de-queue. I would almost call that an anti-pattern. Especially so when the work is dequeued by polling (which costs even when there is no work to do):

    – client stores a record in a table identifying work to do
    – server polls the table looking for unprocessed items…

    Get the polling frequency wrong (or the indexing) and you can sink the entire app (I once flew all the way to Inverness to discover within 20 minutes that a too-frequent poll of background work was the reason for a housing benefit system disappearing up its own fundament).

    I’ve seen the same with the use of message beans and AQ. The AQ interface looks interrupt driven but there is polling under the counter…

    Regards Nigel

  2. Hi.

    Of course, you have to apply some sense to it. I purposely said “business function” because business functions usually involve transactions bigger than a single insert. 🙂



Comments are closed.