Oracle会话阻塞
程序员文章站
2022-06-02 12:27:11
...
单实例的会话阻塞
模拟232会话被1224会话阻塞的情况
select * from V$SESSION_BLOCKERS;
SID SESS_SERIAL# WAIT_ID WAIT_EVENT WAIT_EVENT_TEXT BLOCKER_INSTANCE_ID BLOCKER_SID BLOCKER_SESS_SERIAL#
---------- ------------ ---------- ---------- ------------------------------ ------------------- ----------- --------------------
232 14127 24 241 enq: TX - row lock contention 1 1224 3975 1224 会话是阻塞者
232会话是等待者
SYS > select sid,
BLOCKING_SESSION ,
event,seconds_in_wait from v$session where username='SCOTT';
SID BLOCKING_SESSION EVENT SECONDS_IN_WAIT
---------- ---------------- ------------------------------ ---------------
232 1224 enq: TX - row lock contention 1077 # 会话232等待行锁等待了1077秒
1224 SQL*Net message from client 170 select row_wait_obj#,
row_wait_file#,
row_wait_block#,
row_wait_row#
from v$session where sid=232;
ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
------------- -------------- --------------- -------------
97896 1024 106534 9 select owner, object_type,
object_name,
data_object_id
from dba_objects where object_id=97896;
OWNER OBJECT_TYP OBJECT_NAM DATA_OBJECT_ID
---------- ---------- ---------- --------------
SCOTT TABLE EMP 97896select * from scott.emp
where rowid=dbms_rowid.rowid_create (rowid_type => 1, object_number => 97896, relative_fno => 1024 ,block_number =>106534, row_number => 9);
SELECT sid,event,seq#,p1,p1raw,p2,p3 FROM v$session WHERE wait_time=0;v$lock视图中，lmode代表会话持有锁的锁模式，request代表会话等待请求锁的锁模式，block=2代表RAC环境，block=1代表会话正在阻塞其他会话，block=0代表被其他会话所阻塞
SYS > select sid,type,id1,id2,lmode,request,block from v$lock where type='TX';
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
67 TX 262160 500 0 4 0
53 TX 262160 500 6 0 1
67 TX 655363 498 6 0 2set term on;
set lines 130;
column sid_ser format a12 heading 'session,|serial#';
column username format a12 heading 'os user/|db user';
column process format a9 heading 'os|process';
column spid format a7 heading 'trace|number';
column owner_object format a35 heading 'owner.object';
column locked_mode format a13 heading 'locked|mode';
column status format a8 heading 'status';
spool coe_locks.lst;
select
substr(to_char(l.session_id)||','||to_char(s.serial#),1,12) sid_ser,
substr(l.os_user_name||'/'||l.oracle_username,1,12) username,
l.process,
p.spid,
substr(o.owner||'.'||o.object_name,1,35) owner_object,
decode(l.locked_mode,
1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Excl',
6,'Exclusive',null) locked_mode,
substr(s.status,1,8) status
from
v$locked_object l,
all_objects o,
v$session s,
v$process p
where
l.object_id = o.object_id
and l.session_id = s.sid
and s.paddr = p.addr
and s.status != 'KILLED';
spool off;
==================================================================================
输出例子
session, os user/ os trace locked
serial# db user process number owner.object mode status
------------ ------------ --------- ------- ----------------------------------- ------------- --------
67,129 oracle/SH 18561 24391 SH.DEPARTMENTS Row Exclusive ACTIVE
67,129 oracle/SH 18561 24391 SH.EMP Row Exclusive ACTIVE
53,355 oracle/SYS 25174 25175 SH.DEPARTMENTS Row Exclusive INACTIVESQL> select a.inst_id block_inst,a.sid block_sid,a.lmode,a.request,TRUNC(a.ctime/60) min_waiting, b.inst_id wait_inst,b.sid wait_sid
from gv$lock a, gv$lock b
where a.type='TX'
and a.inst_id || a.sid != b.inst_id || b.sid
and a.id1 = b.id1
and a.block !=0 ;
BLOCK_INST BLOCK_SID LMODE REQUEST MIN_WAITING WAIT_INST WAIT_SID
---------- ---------- ---------- ---------- ----------- ---------- ----------
1 1224 6 0 15 1 232集群会话阻塞的查询语句
select
b.type || '-' || b.id1 ||'-'|| b.id2 ||
case when b.type = 'TM' then
(select '(' || owner || '.' || object_name || ')' from dba_objects
where object_id = b.id1) else '' end as b_res,
s1.sid || ','|| s1.serial# || '@' || s1.inst_id as blocker,
(select count(*) from gv$lock t where
t.type=b.type and t.id1 = b.id1
and t.id2 = b.id2 and request > 0) blocked_cnt,
b.request, b.lmode, s1.username, s1.sql_id,
(select SQL_TEXT from v$sql where sql_id = s1.sql_id and rownum = 1),
s1.prev_sql_id,
(select SQL_TEXT from v$sql where sql_id = s1.prev_sql_id and rownum = 1),
b.ctime as b_ctime,
s2.sid || ','|| s2.serial# || '@' || s2.inst_id as waiter,
w.request,w.lmode,s2.username,s2.sql_id,
(select SQL_TEXT from v$sql where sql_id = s2.sql_id and rownum = 1),
s2.prev_sql_id,
(select SQL_TEXT from v$sql where sql_id = s2.prev_sql_id and rownum = 1),
w.ctime as w_ctime
from gv$lock b, gv$lock w, gv$session s1, gv$session s2
where
b.block > 0
and w.request > 0
and b.id1 = w.id1
and b.id2 = w.id2
and b.type = w.type
and b.inst_id = s1.inst_id
and b.sid = s1.sid
and w.inst_id = s2.inst_id
and w.sid = s2.sid
order by b_res, w_ctime desc;
历史等待会话信息的查询
select BLOCKING_INST_ID,blocking_session,BLOCKING_SESSION_SERIAL#,
from dba_hist_active_session_historyselect
to_char(t.sample_time, 'yyyymmdd hh24:mi:ss') sample_t,
t.event,
count(*)
from dba_hist_active_sess_history t
where t.event is not null
and t.sample_time > to_date('20171129 0830','yyyymmdd hh24:mi')
group by to_char(t.sample_time, 'yyyymmdd hh24:mi:ss'), t.event
order by 1,3 ;
20171129 08:30:08 enq: TX - row lock contention 1
20171129 08:30:09 enq: TX - row lock contention 1
20171129 08:30:18 enq: TX - row lock contention 1
20171129 08:30:19 enq: TX - row lock contention 1
20171129 08:30:28 enq: TX - row lock contention 1
20171129 08:30:29 enq: TX - row lock contention 1
select event,count(*) from dba_hist_active_sess_history
where sample_time > to_date('20171130172000','yyyymmddhh24:miss')
and sample_time < to_date('20171130175000','yyyymmddhh24:miss')
group by event order by 2 desc;
EVENT COUNT(*)
-------------------------- ------------
enq: TX - allocate ITL entry 3715select sql_id, current_obj#, count(*) from dba_hist_active_sess_history
where sample_time > to_date('20171130172000','yyyymmddhh24miss')
and sample_time < to_date('20171130175000','yyyymmddhh24miss')
and event ='enq: TX - allocate ITL entry'
group by sql_id, current_obj# order by 3 desc;
SQL_ID Current_Obj# count(*)
------------------- -------------------- -------------------
g52nulqdyvq46 74140 3634select sql_id, current_obj#, current_file#, current_block#, count(*)
from dba_hist_active_sess_history
where sample_time > to_date('20171201110500','yyyymmddhh24miss')
and event ='enq: TX - allocate ITL entry'
group by sql_id, current_obj# , current_file#, current_block#;select t.SAMPLE_ID,t.SAMPLE_TIME,t.P1,
t.BLOCKING_INST_ID || '_' || t.blocking_session block_sid,
count(*)
from dba_hist_active_sess_history t
where t.event='enq: TX - row lock contention'
and t.sample_time > to_date('20171129 0830','yyyymmdd hh24:mi')
group by t.SAMPLE_ID,t.SAMPLE_TIME, t.P1, t.BLOCKING_INST_ID || '_' || t.blocking_session
order by count(*) ;
7739140 29-11月-17 09.12.44.491000000 上午 1415053318 1_3565 1
7739010 29-11月-17 09.10.34.199000000 上午 1415053318 1_3565 1
7738990 29-11月-17 09.10.14.169000000 上午 1415053318 1_3565 1
7738910 29-11月-17 09.08.53.928000000 上午 1415053318 1_3565 1
7738760 29-11月-17 09.06.23.585000000 上午 1415053318 1_3565 1阻塞链表，从08:30-08:40时间段，阻塞会话的信息
select to_char(t.sample_time, 'yyyymmdd hh24:mi:ss') sample_time,
instance_number || '_' || session_id sid,
event,
session_state state,
seq#,p1,p2,
blocking_inst_id || '_' || blocking_session block_sid
from dba_hist_active_sess_history t
where instance_number || '_' || session_id = '1_3565'
and t.sample_time between to_date('20171129 0830','yyyymmdd hh24:mi') and to_date('20171129 0840','yyyymmdd hh24:mi')
order by sample_time;
20171129 08:30:09 1_3565 enq: TX - row lock contention WAITING 11 1415053318 1703963 2_3853
20171129 08:30:19 1_3565 enq: TX - row lock contention WAITING 11 1415053318 1703963 2_3853
20171129 08:30:29 1_3565 enq: TX - row lock contention WAITING 11 1415053318 1703963 2_3853实例2的sid为3853的会话阻塞了1号实例sid为3565的会话
select distinct instance_number || '_' || session_id,
event,
t.SQL_EXEC_ID,session_state,t.BLOCKING_INST_ID || '_' || t.blocking_session block_sid
from dba_hist_active_sess_history t
where --instance_number || '_' || session_id in ('1_3565','2_3853')
event = 'enq: TX - row lock contention' and
to_char(t.sample_time,'yyyymmdd hh24:mi') ='20171129 08:30';
wait_sid event SQL_ID state block_sid
2_2953 enq: TX - row lock contention 69w54nmn0s3ab WAITING 1_3565
1_3565 enq: TX - row lock contention 69w54nmn0s3ab WAITING 2_3853上一篇： 数据库优化