Re: Advanced queueing CPU consumption

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 30 May 2007 14:50:54 -0700
Message-ID: <1180561853.84614@bubbleator.drizzle.com>

Mladen Gogala wrote:
> This is 10.2.0.3 database on Linux. My application developers have
> installed an application that utilizes advanced queueing and the CPU
> consumption skyrocketed. The 3 SQL statements consuming most CPU are all
> related to AQ. The "Streams AQ: waiting for messages in the queue" event
> surpasses even "SQL*Net mssage from client" and the database is spinning
> like crazy. Is anybody here aware of anything I can do to alleviate the
> burden to the CPU resources?

Dequeue often happens in a construct like this:

LOOP
   BEGIN

     dbms_aq.listen(agent_list => qlist, wait => 15, agent =>agent_w_msg);
       demo_dequeue(USER);
   EXCEPTION
     WHEN listen_timeout THEN
       EXIT;

   END;
END LOOP; Look at either the timeout ... it is raising the exception too often or putting a dynamic sleep into the loop.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Wed May 30 2007 - 16:50:54 CDT