Re: asynchronous PL/SQL development; DBMS_AQ ??

----- Original Message ----
From: Cosmin Ioan <cosmini@xxxxxxxxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
Sent: Friday, April 27, 2007 8:39:13 PM
Subject: Re: asynchronous PL/SQL development; DBMS_AQ ??


hi Alberto,  thx much for the reply -- 
 
what I'm actually looking for is an extremely fast/responsive AQ mechanism for 
a scenario where we have a big module that could be (written) in a modular 
fashion. This module contains many snippets of code that could be written 
asynchronously, but at the end, I need to retrieve data, bits and pieces, from 
each sub-procedure.
 
This **entire** main procedure may take 1-3 seconds, but in a large OLTP 
fast-transact environment, squeezing/parallelizing any operations that could be 
deemed asynchronous could be a life-saver.
 
don't think that dbms_job is fast enough to account for milli or 
microseconds.... pooling every second or so, is definitely something we can't 
entertain....
 
Any thoughts?
thx,
Cos
---------------------------------

Cosmin

I recently worked on an application that did exactly that, using Oracle AQ 
(OC4J, Oracle 9.2.0.5, RAC on Linux). Key points:
The application scaled as far as required
However, AQ consumed an uncomfortably large proportion of cpu (up to 30%)
AQ tables/indexes appear at the top of logical reads, near the top of buffer 
busy waits and at the top of row lock waits and CR blocks served
The work it was handing off to the background only amounted to 10% or so
I think our application design contributed to the poor performance; in 
particular:
the user sessions would poll a reply queue to see whether the background work 
had completed (the user could move on and ignore that, but the LoadRunner tests 
couldn't). As workload (number of users and/or transaction rate) increased, the 
response time for background transactions would (naturally) stretch out; so 
more calls to dequeue messages, so more work, so less throughput, so longer 
response times, so more polling, and so (fairly rapidly) the application hits a 
wall.
Almost all the work was sent on a single queue; this queue could become a 
bottleneck as up to 2000 users queued messages  and around 5 message driven 
beans per node dequeued. The single queue was also a point of RAC contention.
Before you use a heavyweight transactional system like AQ, consider whether a 
lighter weight messaging system (an in-memory JMS, or something even simpler) 
might not achieve the same goals. It all depends how you coordinate the 
different 'snippets', and your degree of tolerance for lost messages. The 
latter mostly boils down to 'what happens if the application or database 
crashes'. 

NB: stats: on one node I see 35,889 dequeue attempts in a 15 minute period 
taking 29.9% of cpu. I can give you more detail if you need it.

HTH

Regards Nigel

Other related posts: