频繁commit导致的log file sync的诊断

背景：

2017-04-11 19:20收到开发员反馈，在某库db1上执行update语句很快，但commit很慢，至少执行了5分钟commit都没有返回。

 

问题：

是什么原因导致commit被挂起/阻塞呢？

分析：

1）当前正在运行什么后台作业呢？备份？还是之前发现的一个从18:00跑到21点的后台作业呢？

2）确认备份完成时间，发现在18:51完成，不在本次事件发生时间内。排除备份的影响。

3）查询后台作业dba_scheduler_running_jobs 发现作业job1正在运行，并根据该视图的session_id统计该作业运行时信息，具体语句如下：

select sql_id,event,count(*) from gv$active_session_history where session_id=1719

and session_serial#=45465

group by sql_id,event

order by 3 desc;

 

 

select sql_opname,count(*) from gv$active_session_history where session_id=1719

and session_serial#=45465

group by sql_opname

order by 2 desc;

4） 根据以上结果发现执行的sql语句以update,insert为主。

5） 生成awr报告，发现有大量的log file sync的等待，竟然占db time 95%，每秒处理的事务3295次，即user commit 3295次/s ，从这一刻开始，怀疑重点是“频繁commit形成队列，令其它事务commit时在队列未端，最终像挂起一样”，证据如下：

6）再查看AWR的SQL语句部份，发现有三条update语句，共运行2400万次。

7）根据sql语句的内容查询dba_source表，确认代码项：

select * from dba_source where text LIKE '%UPDATE XXXX%';

最终检查代码发现：

for v in (select id from t1 )

loop

update t2 set price=price*1.1 where id=v.id

commit;

end loop;

结论：找出问题代码，建议由每次commit改为批量commit，问题解决！

 

补充：

log file sync等待：有可能是频繁commit导致，也有可能redo logfile size太少导致，也有可能是io太慢导致。