Statspack之十四-"logfilesync"等待事件

来源:互联网 发布:网络挖矿机怎么赚钱 编辑:程序博客网 时间:2024/03/29 17:21
<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>
<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>


原文出处:

http://www.eygle.com/Statspack/Statspack14-LogFileSync.htm

当一个用户提交(commits)或者回滚(rollback),session的redo信息需要写出到redologfile中.
用户进程将通知LGWR执行写出操作,LGWR完成任务以后会通知用户进程.
这个等待事件就是指用户进程等待LGWR的写完成通知.

对于回滚操作,该事件记录从用户发出rollback命令到回滚完成的时间.

如果该等待过多,可能说明LGWR的写出效率低下,或者系统提交过于频繁.
针对该问题,可以关注:
logfileparallelwrite等待事件
usercommits,userrollback等统计信息可以用于观察提交或回滚次数

解决方案:
1.提高LGWR性能
尽量使用快速磁盘,不要把redologfile存放在raid5的磁盘上
2.使用批量提交
3.适当使用NOLOGGING/UNRECOVERABLE等选项

可以通过如下公式计算平均redo写大小:

avg.redowritesize=(Redoblockwritten/redowrites)*512bytes

如果系统产生redo很多,而每次写的较少,一般说明LGWR被过于频繁的激活了.
可能导致过多的redo相关latch的竞争,而且可能无法有效的使用piggyback的功能.

我们从一个Statspack中提取一些数据来研究一下这个问题.

1.主要信息

DBNameDBIdInstanceInstNumReleaseOPSHost---------------------------------------------------------------------DB1222010599oracle18.1.7.4.5NOsunSnapIdSnapTimeSessions---------------------------------BeginSnap:347313-Oct-0413:43:00540EndSnap:347513-Oct-0414:07:28540Elapsed:24.47(mins)CacheSizes~~~~~~~~~~~db_block_buffers:102400log_buffer:20971520db_block_size:8192shared_pool_size:600MLoadProfile~~~~~~~~~~~~PerSecondPerTransaction------------------------------Redosize:28,458.112,852.03......

2.等待事件

 

EventWaitsTimeoutsTime(cs)(ms)/txn-------------------------------------------------------------------------logfilesync14,46624,15031.0dbfilesequentialread17,20202,86921.2latchfree24,84113,4892,07211.7directpathwrite12101,4551200.0dbfileparallelwrite1,31401,383110.1logfilesequentialread1,54006300.1....logfileswitchcompletion103300.0refreshcontrolfilecommand230100.0LGWRwaitforredocopy460000.0....logfilesinglewrite40000.0

我们看到,这里logfilesync和dbfileparallelwrite等待同时出现了.
显然logfilesync在等待dbfileparallelwrite的完成.

这里磁盘IO肯定存在了瓶颈,实际用户的redo和数据文件同时存放在Raid的磁盘上,存在性能问题.
需要调整.

3.统计信息

 

 StatisticTotalperSecondperTrans-------------------------------------------------------------------------....redoblockswritten93,85363.96.4redobufferallocationretries10.00.0redoentries135,83792.59.3redologspacerequests10.00.0redologspacewaittime30.00.0redoorderingmarks00.00.0redosize41,776,50828,458.12,852.0redosynchtime4,1742.80.3redosynchwrites14,1989.71.0redowastage4,769,2003,248.8325.6redowritetime3,6982.50.3redowriterlatchingtime00.00.0redowrites14,5729.91.0....sorts(disk)40.00.0sorts(memory)179,856122.512.3sorts(rows)2,750,9801,874.0187.8....transactionrollbacks360.00.0transactiontablesconsistentrea00.00.0transactiontablesconsistentrea00.00.0usercalls1,390,718947.494.9usercommits14,1369.61.0userrollbacks5120.40.0writeclonescreatedinbackgroun00.00.0writeclonescreatedinforegroun110.00.0-------------------------------------------------------------共3页  第1页  
<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>
<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>
原创粉丝点击