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>'; |
Database Technologies > Oracle >