log file sync等待事件分析

作者简介:戴秋龙,拥有超过八年的电信、保险、税务行业核心系统ORACLE数据库优化,优化经验,具备丰富的行业服务背景。对Oracle数据库有深刻的理解,擅长数据库故障诊断,数据库性能调优。

早上8点到9点。

log file sync 等待事件比较突出,当然单块读,行锁也突出这里只分析log file sync。

分析:

简述下Log File Sync

Log File Sync是从提交开始到提交结束的时间。Log File Parallel Write是LGWR开始写Redo File到Redo File结束的时间。 Log File Parallel Write 也会影响 Log File Sync。 这边log file sync 平均等待时间是 1MS, 后文中Log File Parallel Write 也是1ms 因此判断系统IO没有问题。

如果 此时Log File Parallel Write 平均等待时间件很高, 这种情况一般是IO出问题了,导致log 写到磁盘缓慢。

如果 Log File Sync的平均等待时间很长,但是 Log File Parallel Write 时间很短,这种情况查一般检查2点。

1 commit的进程和lgwr之前通过CPU调度来协调的, 会不会CPU资源紧张导致协调过慢? 如果是的。那就会伴随逻辑读高,块忙, latch….等待事件反应CPU吃紧, 这份AWR报告中并没有 这样的特征。

另外ORACLE从11G开始,为lgwr写日志引入了polling机制,隐含参数:”_use_adaptive_log_file_sync”,即在两个机制之间自适应的切换, 导致这种情况。

2 如果日志缓冲区太大,导致一次写入的量很大,也会导致这种现象。

通过AWR报告,已经系统系统查询并不是以上几点。

找到原因:

一小时提交了180万次, 506次/S。 那可以说明 commit 量太多了,导致log file sync等待事件比较高。

在系统hist 视图中也能观察到 大致看到 commits 数量增加, log file sync 的等待事件时间也拉高了。

数据来源 视图 dba_hist_system_event, dba_hist_sysstat, dba_hist_snapshot 。

另外commits_cnt 是1小时内,提交的次数, 一小时提交几千万次,肯定能增加log file sync 等待事件。

估计某个任务,在02:00点执行, 而这个任务中,提交次数很多。

寻找根本原因

目前AWR报告中体现 这1小时内提交了 182万次, 找到可疑的好几个SQl:问题都一样。这里只举例说明一个,

效率: 单次执行2小时。

过程中的逻辑:

调查 游标cur 的数据量,84万, 对比数据量发现, commit; 近80万次。

优化:

有读者可能要说这种要改成批量提交。但这不并不最好的优化办法。 需要分析。

首先 for 嵌套 for 相当于笛卡尔集,然后再通过 if 判断刷选数据。 因此性能就消耗在这边。

优化关键点:

instr(cur.bz_address, x.address) = 1, instr 的意义是返回要截取的字符串在源字符串中的位置。 这里=1表示address 要在cur.bz_address 的开头。 既然在开头那可以用 like ‘X%’ 等价改写。 因为like ‘X%’可以走索引。

过程的含义比较简单。根据go_address_info的数据更新go_prods_info。 那么就可以等价改写成merge ( 80多万的数据量,用merge 还是比较好的,当然改成批量更新也是可以的 )

等价改写的SQL:

执行效率:

总结: 最终60万的 commit, 优化后1个commit; 执行耗时2小时的过程,优化后1分钟。 该过程延伸出来的 TOP SQl, 也顺手给办了。。

未经允许不得转载:Oracle一体机用户组 » log file sync等待事件分析

相关推荐