Oracle session blocking, oracle session

Oracle session blocking, oracle session
Session blocking for a single instance

Simulate 232 sessions being blocked by 1224 sessions

 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 

The lock held by session 1224 blocks the SQL statement executed by session 232.
1224 sessions are congested
232 session is waiting

SYS> select sid, BLOCKING_SESSION, event, seconds_in_wait from v $ session where username = 'Scott ';

 SID BLOCKING_SESSION EVENT SECONDS_IN_WAIT ---------- -------------- else ----------------- 232 1224 enq: TX-row lock contention 1077 

# session 232 waiting for row lock wait 1077 seconds 1224 SQL * Net message from client 170

232 blocked session waiting object, waiting data block number

 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

Detailed information of the blocked wait object

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

View the specific information of the blocked row. The rowid must be generated by the object number, file number, block number, and row number.

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

Query the DML deadlock session sid and the blocking_session session that causes the deadlock

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

If you are waiting, the value of wait_time is 0. If the process has been completed, wait is not equal to 0. Use wait_time to assert whether you have found a potentially blocked session.

In the v $ lock view, lmode indicates the lock mode in which the session holds the lock. request indicates the lock mode in which the session waits for the request lock. block = 2 indicates the RAC environment, block = 1 indicates that the session is blocking other sessions. block = 0 indicates that the session is blocked by other sessions.

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

Query the script of the row lock blocking session

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 sub Str (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, 'clusive ', 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; 

========================================================== ========================================================== = output example session, OS user/OS trace lockedserial # db user process number owner. object mode status ------------ --------- ----------------------------------------- ----------- -------- 67,129 oracle/SH 18561 SH. 

specified ments Row Exclusive ACTIVE67, 129 oracle/SH 18561 24391 SH. EMP Row Exclusive ACTIVE53, 355 oracle/SYS 25174 25175 SH. specified ments 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

Query statement for cluster session Blocking

         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;

Query of historical pending session information

select BLOCKING_INST_ID,blocking_session,BLOCKING_SESSION_SERIAL#,from dba_hist_active_session_history

List the number of waiting events for a certain period of time

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

List the SQL statements that cause the wait event

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

Analyze the data blocks waiting for event Competition

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#;

Number of blocked sessions

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 ('1970 20171129 ', '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.49000000 am-17 09.10.34.199000000 am-17 09.10.14.169000000 am-17 Am-17 Am-17 Am-17 AM

Blocks the linked list, and blocks the session information from.

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

Sessions with sid 3565 of instance 1 cannot be obtained quickly when applying for the TX-row lock,
Session with sid 3853 of instance 2 blocks session with sid 3565 of instance 1

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