我们先来看看gc buffer busy 的定义：
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 asingle-instance database and are often the result of:
在10g 之前。这个等待事件叫做 global cache buffer busy，产生的原因和单实例的 buffer busy waits 类似就是一个时间点节点a的实例向节点b请求block的等待。
产生原因：
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.
热块
可能有多个session 请求这个数据块，有可能是数据块还没有读入 data buffer cache(某个session正在读入)，或者session 间存在冲突的模式
一个会话在delete 热块的热行并且重新插入的时候，有可能出现这个问题。
解决的办法是修正 (扩大)pctfree 参数。来打散这个热块。
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.
低效的查询
越多的数据块请求到buffer cache 中，那么越可能造成别的会话等待。优化查询(sql)，读入更少的lock。

下面来看看遇到gc buffer busy是的数据库情况：
环境：
操作系统：AIX5.3 20C40G
数据库版本：10.2.0.5 RAC
现象：
从v$session或者v$session_wait视图中，可以查询到大量的gc buffer busy 等待。系统运行缓慢。
SID SERIAL# SPID EVENT STATUS BLOCKING_SESSION SQL_ID
---------- ---------- ------------ ------------------------------------------------------------ -------- ---------------- -------------
807 1000 2007276 gc buffer busy ACTIVE 5asgkgnjjhzac
670 827 364556 gc buffer busy ACTIVE 5asgkgnjjhzac
1013 1792 237622 gc buffer busy ACTIVE 7j4dakq0798zs
805 634 1446244 gc buffer busy ACTIVE 75kwpag9a99az
1358 44586 1118556 gc buffer busy ACTIVE 5asgkgnjjhzac
784 1697 2044138 gc buffer busy ACTIVE c0xgjncf7tab6
894 1410 487530 gc buffer busy ACTIVE 7j4dakq0798zs
801 3319 2445606 gc buffer busy ACTIVE 5asgkgnjjhzac
1280 57943 929998 gc buffer busy ACTIVE 7j4dakq0798zs
1119 119 2089450 gc buffer busy ACTIVE 5asgkgnjjhzac
1359 36831 840082 gc buffer busy ACTIVE 5asgkgnjjhzac

仔细检查发现，这些gc buffer busy 全部是由2个SQL 引起的，SQL ID分别为：5asgkgnjjhzac 和 7j4dakq0798zs 。
检查2个SQL的执行计划：
5asgkgnjjhzac
Plan hash value: 1554161953
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 8 (100)| |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | COUNT STOPKEY | | | | | |
| 2 | NESTED LOOPS | | 2 | 722 | 8 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| CERTIFICATE_DETAIL | 2 | 650 | 5 (0)| 00:00:01 |
| 4 | INDEX RANGE SCAN | IDX_CER_DETAIL_01 | 3 | | 4 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| CERTIFICATE_TITLE | 1 | 36 | 2 (0)| 00:00:01 |
| 6 | INDEX UNIQUE SCAN | PK_CERTIFICATE_TITLE | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

Plan hash value: 2556006691
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 61633 (100)| |
| 1 | COUNT STOPKEY | | | | | |
| 2 | NESTED LOOPS | | 2 | 726 | 61633 (4)| 00:12:20 |
| 3 | TABLE ACCESS FULL | CERTIFICATE_DETAIL | 4 | 1308 | 61628 (4)| 00:12:20 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 4 | TABLE ACCESS BY INDEX ROWID| CERTIFICATE_TITLE | 1 | 36 | 2 (0)| 00:00:01 |
| 5 | INDEX UNIQUE SCAN | PK_CERTIFICATE_TITLE | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

这个SQL产生了两个执行计划，这两个执行计划的区别就在于CERTIFICATE_DETAIL表，一个是走索引，一个是全表扫描。后又查询了这个表的统计分析，见下表，从主键看，这个表有8144325行，看来走索引和全表扫描的性能将会差别很大，产生gc buffer busy的原因可能是这个索引的问题。
INDEX_NAME INDEX_TYPE STATUS NUM_ROWS COLUMN_NAME
------------------------------ --------------------------- -------- ---------- ------------------------------
INDEX_45 FUNCTION-BASED NORMAL VALID 7995680 SYS_NC00066$
INDEX_CER_WRITEOFFDETAIL NORMAL VALID 4898206 WRITEOFFDETAILIDS
INDEX_CER_LEDGERCOMPANYCODE NORMAL VALID 7654900 LEDGERCATEGORY_COMPANYCODE
PK_CERTIFICATE_DETAIL NORMAL VALID 8144325 ID
INDEX_44 NORMAL VALID 7750769 CERTIFICATE_ID
INDEX_CER_COSTCENTER NORMAL VALID 3249340 COST_CENTER
INDEX_CER_LEDGERNO NORMAL VALID 8118682 LEDGER_NO
接下来看看7j4dakq0798zs的执行计划，表 CERTIFICATE_DETAIL依然走了全表扫描，看来问题可以确定了。

Plan hash value: 1109848256
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 83037 (100)| |
| 1 | HASH UNIQUE | | 4 | 1364 | 83037 (4)| 00:16:37 |
| 2 | NESTED LOOPS | | 4 | 1364 | 83036 (4)| 00:16:37 |
| 3 | TABLE ACCESS FULL | CERTIFICATE_DETAIL | 4 | 264 | 83028 (4)| 00:16:37 |
| 4 | TABLE ACCESS BY INDEX ROWID| CERTIFICATE_TITLE | 1 | 275 | 2 (0)| 00:00:01 |
| 5 | INDEX UNIQUE SCAN | PK_CERTIFICATE_TITLE | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

问题解决：
在表 CERTIFICATE_DETAIL 的相关列上建索引后，从v$session视图监控中可以看到gc buffer busy等待事件消失，等待事件个数也不断减少，一会后，数据库恢复正常。

至于索引丢失，可能是。。。。。。

ITPub博客

记一次gc buffer busy等待事件的处理

最新文章