oraclerac碰到gcbufferbusy

《oraclerac碰到gcbufferbusy》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。

1、数据库版本：Oracle10.2.0.5-64节点数：2操作系统版本：centos5.6-64今天做awr报告发现gcbufferbusy等待时间gcbufferbusyThiswaitevent,alsoknownasglobalcachebufferbusypriortoOracle10g,specifiesthetimetheremoteinstancelocallyspendsaccessingtherequesteddatablock.Thiswaiteventisverysimila

2、rtothebufferbusywaitswaiteventinasingle-instancedatabaseandareoftentheresultof:1.HotBlocks-multiplesessionsmayberequestingablockthatiseithernotinbuffercacheorisinanincompatiblemode.Deletingsomeofthehotrowsandre-insertingthembackintothetablemayallevia

3、tetheproblem.Mostofthetimetherowswillbeplacedintoadifferentblockandreducecontentionontheblock.TheDBAmayalsoneedtoadjustthepctfreeand/orpctusedparametersforthetabletoensuretherowsareplacedintoadifferentblock.2.InefficientQueriesˆaswiththegccrrequestwa

4、itevent,themoreblocksrequestedfromthebuffercachethemorelikelihoodofasessionhavingtowaitforothersessions.Tuningqueriestoaccessfewerblockswilloftenresultinlesscontentionforthesameblock.第一点应该基本排除：我刚刚整理完表，我们首先找到造成次问题的sql1、首先查看上次所有的等待事件种类1.SQL>selectmin(b

5、egin_interval_time)min,max(end_interval_time)maxfromdba_hist_snapshotwheresnap_idbetween204and228;2.SQL>selectwait_class_id,wait_class,count(*)cnt3.fromdba_hist_active_sess_history4.wheresnap_idbetween204and2285.groupbywait_class_id,wait_class6.order

6、by3;7.结果如下：8.WAIT_CLASS_IDWAIT_CLASSCNT9.-----------------------------------------------------10.4217450380Application111.1740759767UserI/O312.2000153315Network713.3875070507Concurrency814.3386400367Commit2415.4108307767SystemI/O541.1893977003Other66

7、2.3871361733Cluster1043.35432、查看上次所有的等待事件1.SQL>SELECTevent_id,event,COUNT(*)cnt2.FROMdba_hist_active_sess_history3.WHEREsnap_idBETWEEN204AND228ANDwait_class_id=38713617334.GROUPBYevent_id,event5.ORDERBY3;6.结果如下：7.EVENT_IDEVENTCNT8.-------------------

8、-------------------------------9.2277737081gccurrentgrantbusy110.512320954gccrrequest111.3897775868gccurrentmultiblockrequest412.737661873gccrblock2-way513.111015833gccurrentblock2-way614.2701629120gccurrentblockbusy715.1520064534gccrblockbusy916.147