背景:
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太慢导致。