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

TOTAL_WAIT :

TOTAL_TIMEWOUT :

TIME_WAITED :

For Checking particular session, to check all the waits

v$session_event

v$session_wait

v$session;

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