Oracle會話阻塞，oracle會話

Oracle會話阻塞，oracle會話
單一實例的會話阻塞

類比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執行的SQL語句。
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

232受阻塞會話等待的對象，等待的資料區塊號

 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                 97896

查看阻塞行的具體資訊，需要對象號、檔案號、塊號和行號來產生rowid

select * from scott.empwhere rowid=dbms_rowid.rowid_create (rowid_type => 1, object_number => 97896, relative_fno => 1024 ,block_number =>106534, row_number => 9);

查詢DML死結會話sid，及引起死結的堵塞者會話blocking_session

SELECT sid,event,seq#,p1,p1raw,p2,p3 FROM v$session WHERE wait_time=0;

如果正在等待，wait_time的值為0。如果已經完成，wait就不等於0. 使用wait_time來斷言你是否找到潛在被阻塞的會話。

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          2

查詢行鎖阻塞會話的指令碼

set 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) statusfrom    v$locked_object l,    all_objects     o,    v$session       s,    v$process       pwhere    l.object_id = o.object_idand l.session_id = s.sidand s.paddr      = p.addrand s.status != 'KILLED';spool off;==================================================================================輸出例子session,     os user/     os        trace                                       lockedserial#      db user      process   number  owner.object                        mode          status------------ ------------ --------- ------- ----------------------------------- ------------- --------67,129       oracle/SH    18561     24391   SH.DEPARTMENTS                      Row Exclusive ACTIVE67,129       oracle/SH    18561     24391   SH.EMP                              Row Exclusive ACTIVE53,355       oracle/SYS   25174     25175   SH.DEPARTMENTS                      Row Exclusive INACTIVE

SQL> 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_sidfrom gv$lock a, gv$lock bwhere a.type='TX'and a.inst_id || a.sid != b.inst_id || b.sid and a.id1 = b.id1and 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_history

列出某段時間的等待事件個數統計

select to_char(t.sample_time, 'yyyymmdd hh24:mi:ss') sample_t,t.event,count(*)from dba_hist_active_sess_history twhere t.event is not nulland t.sample_time > to_date('20171129 0830','yyyymmdd hh24:mi')group by to_char(t.sample_time, 'yyyymmdd hh24:mi:ss'), t.eventorder by 1,3  ; 20171129 08:30:08enq: TX - row lock contention120171129 08:30:09enq: TX - row lock contention120171129 08:30:18enq: TX - row lock contention120171129 08:30:19enq: TX - row lock contention120171129 08:30:28enq: TX - row lock contention120171129 08:30:29enq: TX - row lock contention1select event,count(*) from dba_hist_active_sess_historywhere sample_time > to_date('20171130172000','yyyymmddhh24:miss')and sample_time < to_date('20171130175000','yyyymmddhh24:miss')group by event order by 2 desc;EVENTCOUNT(*)--------------------------     ------------enq: TX - allocate ITL entry     3715

列出導致等待事件的SQL語句

select sql_id, current_obj#, count(*) from dba_hist_active_sess_historywhere 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_IDCurrent_Obj#count(*)------------------- --------------------  -------------------g52nulqdyvq46 741403634

分析到該等待事件競爭的資料區塊

select sql_id, current_obj#,  current_file#, current_block#, count(*)from dba_hist_active_sess_historywhere 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 twhere 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_sessionorder by count(*)  ;773914029-11月-17 09.12.44.491000000 上午14150533181_35651773901029-11月-17 09.10.34.199000000 上午14150533181_35651773899029-11月-17 09.10.14.169000000 上午14150533181_35651773891029-11月-17 09.08.53.928000000 上午14150533181_35651773876029-11月-17 09.06.23.585000000 上午14150533181_35651

阻塞鏈表，從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_sidfrom dba_hist_active_sess_history twhere 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:091_3565enq: TX - row lock contentionWAITING11141505331817039632_385320171129 08:30:191_3565enq: TX - row lock contentionWAITING11141505331817039632_385320171129 08:30:291_3565enq: TX - row lock contentionWAITING11141505331817039632_3853

1號執行個體sid為3565的會話在申請TX - row lock鎖時無法快速擷取，
執行個體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_sidfrom dba_hist_active_sess_history twhere --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_sid2_2953enq: TX - row lock contention69w54nmn0s3abWAITING1_35651_3565enq: TX - row lock contention69w54nmn0s3abWAITING2_3853