RAC数据库出现大量的gc buffer busy等候事件，请教如何解决

[ndugu]

运行addm,有下面的信息：

发现用于并发读写操作的热数据块。此块属于段 "USER.SYS_C006540", 是块 119211, 在 6 文件中。

RECOMMENDATION 1: Application Analysis, 98% benefit (5438 seconds)
ACTION: 研究应用程序逻辑, 以找出对此块中的数据的并发读写操作如此频繁的原因。
RELEVANT OBJECT: database block with object# 51868, file# 6 and
block# 119211

SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: 实例间消息传送在此实例上消耗了大量数据库时间。 (71% impact [3944 seconds])
SYMPTOM: 等待类别 "集群" 消耗了大量数据库时间。 (100% impact [5605 seconds])


==================================

[liuyi8903]

gc buffer busy
This wait event, also known as global cache buffer busy prior to Oracle 10g, specifies
the time the remote instance locally spends accessing the requested data block. This
wait event is very similar to the buffer busy waits wait event in a single-instance
database and are often the result of:
1. Hot Blocks - multiple sessions may be requesting a block that is either not in buffer
cache or is in an incompatible mode. Deleting some of the hot rows and re-inserting
them back into the table may alleviate the problem. Most of the time the rows will be
placed into a different block and reduce contention on the block. The DBA may also
need to adjust the pctfree and/or pctused parameters for the table to ensure the rows
are placed into a different block.
2. Inefficient Queries ˆ as with the gc cr request wait event, the more blocks requested
from the buffer cache the more likelihood of a session having to wait for other sessions.
Tuning queries to access fewer blocks will often result in less contention for the same
block.

[tgm78]

需要找到造成热点块的sql

一般情况下是含有全表扫描的sql会造成热点块。

1、找到最热的数据块的latch和buffer信息

select b.addr,a.ts#,a.dbarfil,a.dbablk,a.tch,b.gets,b.misses,b.sleeps from
(select * from (select addr,ts#,file#,dbarfil,dbablk,tch,hladdr from x$bh order by tch desc) where rownum<11) a,
(select addr,gets,misses,sleeps from v$latch_children where name='cache buffers chains') b
where a.hladdr=b.addr;

2、找到热点buffer对应的对象信息：

col owner for a20
col segment_name for a30
col segment_type for a30

select distinct e.owner,e.segment_name,e.segment_type from dba_extents e,
(select * from (select addr,ts#,file#,dbarfil,dbablk,tch from x$bh order by tch desc) where rownum<11) b
where e.relative_fno=b.dbarfil
and e.block_id<=b.dbablk
and e.block_id+e.blocks>b.dbablk;

3、找到操作这些热点对象的sql语句：

break on hash_value skip 1
select /*+rule*/ hash_value,sql_text from v$sqltext where (hash_value,address) in
(select a.hash_value,a.address from v$sqltext a,(select distinct a.owner,a.segment_name,a.segment_type from dba_extents a,
(select dbarfil,dbablk from (select dbarfil,dbablk from x$bh order by tch desc) where rownum<11) b where a.relative_fno=b.dbarfil
and a.block_id<=b.dbablk and a.block_id+a.blocks>b.dbablk) b
where a.sql_text like '%'||b.segment_name||'%' and b.segment_type='TABLE')
order by hash_value,address,piece;

[ndugu]

快过年 送分了