latches dają exclusive access do obiektu
locks – concurrent access but only one can write at the time
simple objects (np. buffer cache)
compound object (table)
locks dla simple objects:
- exclusive – session needs to modify a simple object
- shared – session needs to view an object
- null – if session caches info about an object – null locki is placed on it – it acts like a trigger in case an object is invalidated
tylko dla compound
- sub shared – shared locks on some rows in a table (np w przypadku utworzenia kursora)
- sub exclusive – session need an exclusive lock on a part of an object
- shared sub-exclusive lock – shared lock on a whole table and an exclusive lock on a part of a table
dictionary views:
dba_locks – session id who is holding a lock, type of lock, what mod the lock is in, whether it is blocking another session for access
dba_dml_locks – data manipulation language locks
dba_ddl_locks – data dictionary language locks
dba_blockers – show which sessions are blocking other sessions
dba_waiters – show which sessions are waiting for resources
select * from dba_tables
tam jet init_trans i max_trans
buffer cache – tu trzymane bloki z transakcji
v$system_event
select event, total_waits, average_wait from v$system_event where wait_class != ‘Idle’;
V$session_event – describes events since the session has started up
V$session_wait – check what sid is of the user which is haveing problems
select event, seconds_in_wait, state
from v$session_waits
where sid=142;
select * from v$session;
blocking_session, blockin_instance
select row_wait_obj#, row_wait_block#, row_wait_row#, blocking_session from v$session where sid=142;
w dba_objects sprawdzam czego dotyczy ten object
select owner, object_name from dba_objects where object_id=51161;
select * from v$session
śledzenie sesji
alter session set events ’10046 trace name context forever, lever 8′;
wyłączenie śledzenia sesji
alter session set events ’10046 trace name context off’;
potem do userdump directory i sprawdzenie co się dzieje.
aby to działało to trzeba mieć time_statistics ustawione na true
show paramete timed_statistics;
db file sequential read – indexes
db file scattered read - full table scans
direct path read/write – extensive sort – not enough memory
buffer busy wait – you should tune your sql statements