Re: Advanced queueing question

Tim Kearsley wrote:

> Hi all,
>
> Environment is:
>
> Oracle 8.1.7.3
> AIX 4.3.3
> RS6000 hardware
>
> We've recently had a major upgrade to an application and the
> application vendor has started making extensive use of Oracle's
> Advanced Queueing. My knowledge of this topic is next to nothing and
> I'm doing some reading now to try and get up to speed.
>
> In the meantime I wonder if anyone can comment on a possible
> performance issue in respect of AQ?
>
> The application users have complained of "poor performance" ever since
> the upgrade. The application uses a three-tier architecture with the
> client essentially just a browser and the middleware and database
> located on the server. I'm using statspack as one tool in my
> endeavours to find out if there really is a problem. I notice that
> the "queue messages" event is usually at the top of the "Top 5 Wait
> Events" section of a statspack report. To quote an example, in a 5.5
> hour sample the queue messages event clocked up statistics thus:
>
> Avg
> Total Wait wait Waits
> Event Waits Timeouts Time (cs) (ms) /txn
> ------------------ ------------ ---------- ----------- ------ ------
> queue messages 54,422 21,558 25,678,484 4718 0.2
>
> My question is: Is this "normal", or does this represent real waiting
> by users?
>
> There are 36 job queue processes kicked off when the database is
> started, if that is relevant to this.
>
> Many thanks for any input.
>
> Regards,
>
> Tim Kearsley
> HBS
Advanced Queueing is excellent and can handle very high transaction volumes. The same can not necessarily be said for your vendors implementation.

For a full demo on AQ to better learn it go to: http://www.psoug.org/reference/library.html and look up DBMS_AQ and DBMS_AQADM.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)