Database Technologies‎ > ‎Oracle‎ > ‎

Oracle Session Monitoring, Locks, Latches and Waits

Latches allows exclusive access to the data structure
These usually last for very short period of time.

Locks  allows concurrent access , but only one process could modify when it gains
exclusive access.

Locks could last for long time upto hours.

Various Lock Modes.

Simple Objects. - Objects which dont have sub objects like buffer cache      
Exclusive  : If a process needs to modify the object it gets exclusive lock
Shared     : Is a process needs to view the object
null       : acts as trigger if object is invalidated

Compound Objects. - Object which have sub objects like table has rows as sub objects
sub-shared            : when we use cursor for update,
                        sub-shared lock is places on rows of table

sub-exclusive         : Session need exclusive lok on rows of table
shared sub-exclusive  : exclusive lock on some rows and shared lock on whole table

Important views on getting lock information

dba_locks        :   give info about lock mode,session id, what its blocking etc
dba_dml_locks :   dml locks
dba_ddl_locks  :   ddl locks
dba_blockers    :   which session are blocking other session
dba_waiters      :   which session are waiting for resources.

Oracle handles locks and Block level,

Oracle handles lock and oracle block level

Block header contains the information about the transactions

If one row of block has lock on it, the lock info will be placed
in the block header, if another session needs lock on it, it will have
to wait till the first session commits or rollback.

Oracle Wait interface.

V$SYSTEM_EVENT : Shows data since ur system is up

Event :
WAIT_CLASS : Type of waits, I/O wait, network wait, or application wait etc

For Checking particular session, to check all the waits


select row_wait_obj#,row_wait_block#,row_wait_row#, blocking_sessino from
v$session where sid=356'

You could enable tracing for that particular session.

To enable
select 'exec dbms_system.set_ev(' ||sid||','||serial#||',10046,12,'''');' from v$session where username='<username>'
To Disable
select 'exec dbms_system.set_ev(' ||sid||','||serial#||',10046,0,'''');' from v$session where username='<username>';