Re: Queue tables and buffer busy waits

"Mladen Gogala" <mgogala_at_no.address.invalid> wrote in message news:i98hg8$bvi$1_at_solani.org...
> The new application, recently installed to run against a RAC database (3
> nodes, 64 bit linux, Oracle 10.2.0.5) is making heavy use of advanced
> queueing. My problem is that the queue tables are incessant source of
> contention, suffering from all kinds of buffer busy waits, both local and
> global. If I check V$SEGMENT_STATISTIC with the following query,
>
> with stats as (
> select owner,object_name,statistic_name,value
> from v$segment_statistics
> where statistic_name = 'gc buffer busy'
> order by value desc
> )
> select * from stats where rownum<=10
> /
>
>
> The result looks like this:
> OWNER OBJECT_NAME STATISTIC_NAME
> VALUE
> --------------- ------------------------------ --------------------
> ----------
> SYS I_JOB_JOB gc buffer busy
> 30184683
> SYS JOB$ gc buffer busy
> 10128719
> ADBASE PK_PENDING_ALERTS gc buffer busy
> 7899852
> SYS I_JOB_NEXT gc buffer busy
> 5302448
> ADBASE PENDING_ALERTS gc buffer busy
> 5288135
> LOCATIONSERVICE AQ$_MMSRES_MMSAGENT_TABLE_I gc buffer busy
> 1082715
> LOCATIONSERVICE MMSRES_MMSAGENT_TABLE gc buffer busy
> 1055558
> LOCATIONSERVICE SPEECH2TEXT_Q_TABLE gc buffer busy
> 622833
> LOCATIONSERVICE TASKS gc buffer busy
> 358430
> LOCATIONSERVICE DQV2MIN_STARTDATE_IDX gc buffer busy
> 256124
>
> Now, everything that is not owned by SYS and is not index is a queue
> table. The problem is systemic in nature, queue tables are by their very
> nature the point of contention. What can be done to alleviate the
> contention, short of restricting the queue to a single node only?
> Every queue has retention time set to 0. Developers argue that setting
> retry_delay to something >0 would be extremely detrimental to
> performance.
>
>
>
>
>
> --
> http://mgogala.byethost5.com

Nothing.

I think there is even a comment in the manuals that if you want to mix AQ with RAC then the enqueue and dequeue processes for a single queue table should be constrained to run on the same node.

(I can't remember if this is in the RAC manuals or the AQ manuals - but it was probably the AQ manuals - because I recall reading at the same time that AQ and Shared Servers (MTS) don't mix well if you use the AW polling mechanism.)

On second thoughts - you might look at the possibility of hash partitioning everything simply to increase the number of possible points of contention - in case this manages to reduce the contention on any particular point; but I don't
know if you can do that legally and it might do terrible things with execution
plans, and it might simply mean you have more points subject to the same amount of contention.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com