Oracle notki, latches, locks « Różności …

Różności …

28 grudnia 2010

Oracle notki, latches, locks

Zaszufladkowany do: Bazy danych,Oracle — Tagi: — Jacek @ 12:46

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

Brak komentarzy

Brak komentarzy.

Kanał RSS z komentarzami do tego wpisu.

Przepraszamy, możliwość dodawania komentarzy jest obecnie wyłączona.

Strona startowa: www.jaceksen.pl