On 8 Mar 2004 06:55:37 -0800, tim.kearsley_at_milton-keynes.gov.uk (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
I see the vendor is going to drive you in the direction of disaster.
The scenario of 36 jobs sounds all too familiar. In our particular
case *no* client ever performs a *direct* insert or update, everything
is being dealt with by Advanced Queuing. Advanced Queing might work,
if you use it for the purpose it was designed for in the first place.
It was NOT designed to replace Oracle's native consistency and locking
mechanism.
I would investigate what jobs they have defined urgently. I am afraid
I can suspect what your findings are.
--
Sybrand Bakker, Senior Oracle DBA
Received on Mon Mar 08 2004 - 13:25:23 CST
