wait event:gc buffer busy

由於多節點的原因，會因為節點間的資源爭用產生GC類的等待，而這其中GC Buffer Busy 最常見

檢查B2B_CORE_RAC DB(VIP:10.134.130.225/226)發現 SCHEMA:WM71_AP MODULE:JDBC Connect Client  執行很多類似SQL：

SELECT  d.DocID,  d.DocTimestamp,  t.TypeName,  s.CorporationName AS SenderCorp,  s.OrgUnitName AS SenderUnit, 
r.CorporationName AS ReceiverCorp,  r.OrgUnitName AS ReceiverUnit,  d.RoutingStatus,  d.UserStatus,  d.NativeID, 
d.GroupID,  d.ConversationID,  d.Comments,  dj.JobStatus FROM  BizDocTypeDef t,  Partner s,  Partner r,  BizDoc d,  DeliveryJob dj
WHERE  d.DocTypeID = t.TypeID AND   d.SenderID = s.PartnerID AND   d.ReceiverID = r.PartnerID AND   d.DocID = dj.DocID(+) 
AND  d.NativeID = '2SADADAASDAAASSSS65467547YDF2SSS' ORDER BY d.DocTimestamp DESC;

分析：此SQL對BizDoc的query 條件為欄位NativeID，但沒有index從而全表掃描table:WM71.BIZDOC (大於1G的資料)產生
 RAC環境間的gc buffer busy,gc cr multi block request等待事件，並對DB server產生極高負載。

with stats as
(select * from v$segment_statistics
where statistic_name = 'gc buffer busy'order by value desc)select * from stats where rownum<=5
看到BIZDOC排在最前面
OWNER OBJECT_NAME SUBOBJECT_NAME TABLESPACE_NAME TS# OBJ# DATAOBJ# OBJECT_TYPE STATISTIC_NAME STATISTIC# VALUE
WM71 BIZDOC  B2B_LDATA 7 95908 95908 TABLE gc buffer busy 2 1300348915
DELLYSD EDIINBOUNDTRACKING  B2B_LDATA 7 121244 124753 TABLE gc buffer busy 2 2553364
WM71 IDX_ACTLOG_RELATEDDOCID  B2B_LINDX 9 96720 96720 INDEX gc buffer busy 2 317549