记一次索引分裂造成rac业务短暂业务不可用的情况

来源：这里教程网时间：2026-03-03 17:38:03 作者：

根据客户描述， 9:47 分到 9:50 分业务出现短暂不可用的现象

节点一

	Snap Id	Snap Time	Sessions	Cursors/Session
Begin Snap:	242040	07-Dec-21 09:40:36	1462	8.8
End Snap:	242041	07-Dec-21 09:50:09	1802	10.1
Elapsed:		9.56 (mins)
DB Time:		1,278.11 (mins)

节点二

	Snap Id	Snap Time	Sessions	Cursors/Session
Begin Snap:	242040	07-Dec-21 09:40:35	4326	8.5
End Snap:	242041	07-Dec-21 09:50:09	4655	8.9
Elapsed:		9.55 (mins)
DB Time:		1,130.81 (mins)

节点一

Top 5 Timed Foreground Events

Event	Waits	Time(s)	Avg wait (ms)	% DB time	Wait Class
gc current block 2-way	742,186	15,588	21	20.33	Cluster
gc buffer busy acquire	57,670	12,568	218	16.39	Cluster
gc buffer busy release	41,139	11,069	269	14.43	Cluster
gc cr grant 2-way	343,681	8,629	25	11.25	Cluster
enq: TX - index contention	14,672	6,489	442	8.46	Concurrency

节点二

Top 5 Timed Foreground Events

Event	Waits	Time(s)	Avg wait (ms)	% DB time	Wait Class
enq: TX - index contention	19,547	17,643	903	26.00	Concurrency
gc buffer busy acquire	76,148	13,004	171	19.17	Cluster
DFS lock handle	228,842	3,593	16	5.30	Other
DB CPU		3,556		5.24
gc cr grant 2-way	623,429	3,316	5	4.89	Cluster

可以看到gc消耗严重

11g开始gc buffer busy分为gc buffer busy acquire和gc buffer busy release:

gc buffer busy acquire是当session#1尝试请求访问远程实例(remote instance) buffer，但是在session#1之前已经有相同实例上另外一个session#2请求访问了相同的buffer，并且没有完成，那么session#1等待gc buffer busy acquire。

gc buffer busy release是在session#1之前已经有远程实例的session#2请求访问了相同的buffer，并且没有完成，那么session#1等待gc buffer busy release。

Event	Waits	Time(s)	Avg wait (ms)	% DB time	Wait Class
gc current block 2-way	742,186	15,588	21	20.33	Cluster
gc buffer busy acquire	57,670	12,568	218	16.39	Cluster
gc buffer busy release	41,139	11,069	269	14.43	Cluster
gc cr grant 2-way	343,681	8,629	25	11.25	Cluster
enq: TX - index contention	14,672	6,489	442	8.46	Concurrency

我认为可能是两个原因造成的：

1. 低效SQL，逻辑读过大，且访问频繁，造成争用严重。

2. 数据库IO资源紧张，导致一些频繁访问的SQL语句响应慢，造成gc buffer busy acquire,gc buffer busy release等待事件。

定位是否是原因1的问题，就找Segments by Global Cache Buffer Busy。然后根据对象的名称去找对应的SQL，然后查看SQL的执行计划定位问题。

节点一

Segments by Global Cache Buffer Busy

% of Capture shows % of GC Buffer Busy for each top segment compared

with GC Buffer Busy for all segments captured by the Snapshot

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	GC Buffer Busy	% of Capture
HZSI	USERS	IDX_BUSSINESSLOG_AAE036		INDEX	34,132	63.10
HZSI	USERS	IDX_KC22_TEMP21_BAZ001		INDEX	3,384	6.26
HZSI	SJZH	IDX_KC61_BAZ001		INDEX	3,328	6.15
HZSI	INDX_K	IDX_KC21_AKC002		INDEX	2,289	4.23
HZSI	USERS_K	PK_KC61_AKC001		INDEX	1,427	2.64

节点二

Segments by Global Cache Buffer Busy

% of Capture shows % of GC Buffer Busy for each top segment compared

with GC Buffer Busy for all segments captured by the Snapshot

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	GC Buffer Busy	% of Capture
HZSI	USERS	IDX_BUSSINESSLOG_AAE036		INDEX	46,991	58.48
HZSI	SJZH	IDX_KC61_BAZ001		INDEX	7,218	8.98
HZSI	USERS	IDX_KC22_TEMP21_BAZ001		INDEX	5,558	6.92
HZSI	INDX_K	IDX_KC21_BAZ001		INDEX	2,792	3.47
HZSI	USERS_K	PK_KC61_AKC001		INDEX	2,714	3.38