15年05月14日是上巡检AWR发现数据库中出现了大量的
enq TX row lock contention 锁等待事件
解决方案如下:
首先利用之前写的ASH自定义脚本进行检查:
将时间替换为问题时段,等待事件修改为问题等待事件
如下:
select *
from dba_hist_active_sess_history
where to_char(sample_time, 'YYYY-MM-DD hh24:mi:ss') between
'2015-05-18 02:30:00' and '2015-05-18 06:30:00'
and program not like 'oracle@%'
and session_state = 'WAITING'
and time_waited > 0 and event='enq: TX - row lock contention'
找到是哪些语句SESSION产生了锁
也可以用更简单的这个语句:
select blocking_session,count(*) from (select *
from dba_hist_active_sess_history
where to_char(sample_time, 'YYYY-MM-DD hh24:mi:ss') between
'2015-05-20 02:30:00' and '2015-05-20 06:30:00'
and program not like 'oracle@%'
and session_state = 'WAITING'
and time_waited > 0 and event='enq: TX - row lock contention') group by blocking_session order by count(*) desc
找到是哪些语句SESSION产生了锁
然后重点观察:blocking-sesison,看是哪个会话造成的。
如图:
然后对问题会话进行查询
select sql_id,count(*)
from dba_hist_active_sess_history
where to_char(sample_time, 'YYYY-MM-DD hh24:mi:ss') between
'2015-05-18 02:30:00' and '2015-05-18 06:30:00'
and session_id='423' group by sql_id order by count(*) desc
分析是哪个语句造成的锁。
select * from dba_hist_sqltext where sql_id='3jyjdfsb6hn26'
最后,找到问题。搞定。