前段时间有一套RAC系统偶然会出现负载异常升高的情况，后来查出来跟节点内部通信有关系，之后升级RAC系统的网卡驱动,其中一个节点重启,另一个节点通过rmmod,modprobe加载新的网卡驱动,导致混合连接,gc buffer busy频繁 :
SQL> select INST_ID,username,count(*) from gv$session group by inst_id,username order by INST_ID,username;
INST_ID USERNAME COUNT(*)
---------- ------------------------------ ----------
1 SKYCAC 206
1 SYS 7
1 28
2 EXP_MAN 1
2 SKYCAC 206
2 SKYCACB 30
2 SYS 6
2 28
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
/
OWNER OBJECT_NAME STATISTIC_NAME VALUE
------------ -------------------------------- -------------------------------- ----------
SKYCAC IDX_CAC_PLAYER_INFO gc buffer busy 283355
SKYCAC IDX_CAC_PLAYER_INFO_NICK_NAME gc buffer busy 19832
SKYCAC CAC_PLAYER_PRIZE gc buffer busy 17370
SKYCAC CAC_MONITOR_PLAYER gc buffer busy 10278
SKYCAC CAC_GAME_ACCESS_RECORD gc buffer busy 8344
SKYCAC PK_CAC_PLAYER_PRIZE gc buffer busy 8278
SKYCAC CAC_ROOM gc buffer busy 4016
SKYCAC IDX_CAC_GAME_ACS_REC_TIME gc buffer busy 3300
SKYCAC CAC_PLAYER_INFO gc buffer busy 1198
SKYCAC IDX_CAC_PLAYER_INFO_SKY_ID gc buffer busy 592 592
处理掉2 SKYCAC 206这部分连接的话,将恢复正常.
类似案例 :
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.
