Blocking Session and Waiting Events in 10gR2

Now blocking sessions and waiting events can easily be monitored using additional columns added to V$session tables in 10g database.

rajesh@10GR2> create table t (x number primary key);

Table created.

Elapsed: 00:00:00.07
rajesh@10GR2>
rajesh@10GR2> select userenv('sid') from dual;

USERENV('SID')
--------------
           142

Elapsed: 00:00:00.01
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> insert into t values (1);

1 row created.

Elapsed: 00:00:00.01

Now the Session - 1 has some uncommitted transactions ( sid = 142 )

scott@10GR2> select userenv('sid') from dual;

USERENV('SID')
--------------
           149

Elapsed: 00:00:00.00
scott@10GR2>
scott@10GR2> insert into rajesh.t values (1);

Now the Session - 2  (sid = 149) is blocked by session - 1 ( sid = 142)

Now this information is clearly visible in V$session dynamic table's.

test@10GR2> exec print_table(' select * from v$session where sid = 149 ');
    ........
    ........
     PDDL_STATUS--------- ENABLED
     PQ_STATUS----------- ENABLED
     CURRENT_QUEUE_DURATI 0
     CLIENT_IDENTIFIER---
     BLOCKING_SESSION_STA VALID
     BLOCKING_INSTANCE--- 1
     BLOCKING_SESSION---- 142
     SEQ#---------------- 45
     EVENT#-------------- 183
     EVENT--------------- enq: TX - row lock contention
     P1TEXT-------------- name|mode
     P1------------------ 1415053316
     P1RAW--------------- 54580004
     P2TEXT-------------- usn<<16 | slot
     P2------------------ 655382
     P2RAW--------------- 000A0016
     P3TEXT-------------- sequence
     P3------------------ 69712
     P3RAW--------------- 00011050
     WAIT_CLASS_ID------- 4217450380
     WAIT_CLASS#--------- 1
     WAIT_CLASS---------- Application
     WAIT_TIME----------- 0
     SECONDS_IN_WAIT----- 72
     STATE--------------- WAITING
     SERVICE_NAME-------- SYS$USERS
     SQL_TRACE----------- DISABLED
     SQL_TRACE_WAITS----- FALSE
     SQL_TRACE_BINDS----- FALSE


PL/SQL procedure successfully completed.

Elapsed: 00:00:00.17
test@10GR2>