direct path read 及 log file sync 等待事件分析过程及优化方法案例一则

5月20号下午4－5点，某项目组进行数据入库作业，作业人员反映入库速度很慢。在16:30和16:50分别采集了快照，并根据两个快照得到AWR报告，即打包文件中的ruku1.html。

直接看TOP 5 EVENTS，这是数据库问题诊断的最快捷径。
        

先看占DB TIME达63.33%的direct path read事件。等待次数78586次，等待总时间3833s（约64分钟），而elapsed time只有20分钟。因此我们需要弄清楚是什么动作导致这么高的direct path read。

那什么是direct path read呢？一般来说，数据块BLOCK(即ORACLE的最小存储单元)总是先由后台服务器进程缓冲至buffer cache，而后才被服务器进程获取。但对于一些大表，将其缓冲至buffer cache势必会将buffer cache中的许多其它对象挤出，即ageing out。为了避免这一情况，产生了direct path read,即不需要缓冲到缓存区，而是直接由服务器进程从磁盘获取。ORACLE通过一些参数控制在何种情况下采取direct path read。

        既然direct path read很高，那就直接去查看对于哪些对象的direct path read高。通过查看segment by direct physical reads，可以获得这一信息：

显而易见，direct physical reads是由于访问tbcm_catalogfile引起的。因为physical reads= physical reads cache + physical reads direct，因此，除了查看segment by direct physical reads，也有必要查看一下segment by physical reads 的情况：

Physical reads最多的仍然是表tbcm_catalogfile。现在我们知道了physical reads主要发生在哪个对象上，但仍然不知道发生在哪个业务上（即哪个SQL逻辑上）。即然Physical reads是等待最多，自然地，我们需要去查看Physical reads最多的SQL语句：

根据SQL_ID查看第一条SQL语句，其文本为：

SELECT F_ID, F_OBJECTID, F_FILELOCATION, f_filesrclocation, F_ISONSERVER, F_DATASIZE, F_PACKAGEPATH, F_SERVERID, F_ISMAINFILE, F_FILEPROPERTY, F_DIRTYPE FROM TBCM_CATALOGFILE where F_OBJECTID=:"SYS_B_0" and F_PACKAGEPATH=:"SYS_B_1" order by F_OBJECTID

果然与表tbcm_catalogfile有关，接下来，我们查看该表的相关信息。得知，该表有4,000,000多条记录，F_OBJECTID字段几乎是唯一的，然而表上没有任何索引。由于没有索引，有执行上述SQL时，ORACLE只有选择全表扫描的方式，而对于如此大的一张表，恰好符合了DIRECT PATH READ的条件，因此执行计划选择使用DIRECT PATH READ的方式来获取数据。如果是单个进程，事实上已经很糟了。多个进程是，同于是direct path read，没有将block缓冲至缓存区，所以每个进程都得通过direct path read获取自己想要的数据。情况因此变得更糟。

 

分析完TOP 5 EVENTS中和第1名，接下来，我们分析一下第2名。

第2名是log file sync。当发出COMMIT或ROLLBACK命令的时间，服务器进程会唤醒LGWR进程，LGWR负责将REDO BUFFER中的日志缓存刷新到日志文件中。而LGWR后台进程产生的等待事件是log file parallel write。因此一般说来，前台log file sync等待事件高，后台log file parallel write也会高，我们在AWR报告中验证一下：

果不其然。另外log file parallel write的avg wait为28ms，高于20，根据经验意味着存在日志文件IO急用。

    继续看：

    

    日志在20分钟内切换了5次，平均每4分钟切换一次，这个是远高于15－20分钟公认的切换一次。这说明REDO FILE文件可能过小。

    继续看：

    

    20分钟之内，没有发生回退，即user rollback=0。User calls/(user commints + user rollback) =9.87 ，该值小于经验值25，说明系统是提交过于频繁的。

   

    针对上述问题，给出以下应对办法：

1.   在tbcm_catalogfile表的F_OBJECTID,F_PACKAGEPATH字段上创建组合索引

2.   由于硬件无法更换，所以日志文件的IO争用可不管它

3.   将日志文件从现在的50M，改为2G大小

4.   由于调整代码工作量过大，COMMIT提交过于频繁的问题可不用管它。

 

调整之后，再次执行入库作业，并收集15:00-15:15之间的AWR报告。通过验看报告，上述问题解决：

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31340387/viewspace-2108436/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31340387/viewspace-2108436/