Oracle Tuning Log File Sync 等待事件的几种策略
- - CSDN博客数据库推荐文章 在一个频繁 commit/rollback 或磁盘 I/O 有问题、大量物理读写争用. 那么、我们便会经常瞧见 LOG FILE SYNC 等待事件出现在 TOP EVENTS 中. 评估 LOG FILE SYNC等待事件的指标是平均等待时间、以及 AWR 后续的 WAIT EVENT HISTOGRAM.
--查询等待的会话ID , 阻塞的等待时间类型、事件ID 、 SQLID 等等信息
select *
from v$active_session_history h
where sample_time > trunc(sysdate)
and session_state = 'WAITING'
and exists(
select 1 from v$sql s
where upper(s.sql_text) like '%T_USER%'
and s.sql_id = h.sql_id
)
order by sample_time desc;
--会话阻塞的事件查询
select * from v$session_wait where sid = 148;
--会话发生过的所有等待事件查询
select s.time_waited/1000,s.* from v$session_event s where sid = 148;
--被锁了之后,查看持有该锁的会话查询
select a.sid blocker_sid,
a.serial#,
a.username as blocker_username,
b.type,
decode(b.lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_mode,
b.ctime as time_held,
c.sid as waiter_sid,
decode(c.request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,
c.ctime time_waited
from v$lock b, v$enqueue_lock c, v$session a
where a.sid = b.sid
and b.id1= c.id1(+)
and b.id2 = c.id2(+)
and c.type(+) = 'TX'
and b.type = 'TX'
and b.block = 1
and c.sid = 148
order by time_held, time_waited;
--查询持有锁的会话执行了的SQL
select s.sql_text,h.* from v$active_session_history h,v$sql s
where h.sql_id = s.sql_id
and h.session_id = 150;