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
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
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.
select 'exec dbms_system.set_ev(' ||sid||','||serial#||',10046,12,'''');' from v$session where username='<username>'
select 'exec dbms_system.set_ev(' ||sid||','||serial#||',10046,0,'''');' from v$session where username='<username>';