Queue tables and buffer busy waits

From: Mladen Gogala <mgogala_at_no.address.invalid>
Date: Fri, 15 Oct 2010 03:23:52 +0000 (UTC)
Message-ID: <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

Received on Thu Oct 14 2010 - 22:23:52 CDT