Tuning Database Locks & Latches

1. Tuning Database Locks & Latches Hamid R. Minoui Fritz Companies Inc. NoCOUG May 16, 2001

2. The Challenge of Tuning • Oracle performance tuning requires a good understanding of all the components of a database system and the way they operate and interact. • This presentation addresses two types of these components: Database Locks and Latches

3. Need for locks and latches • To access shared resources concurrently by other processes requiring access to the same resources. • To protect the contents of database objects while they are being modified or inspected by other processes • To serialize access to SGA data structures

4. Locks & Latches • Oracle mechanisms for protecting and managing SGA data structures and database objects being accessed concurrently while maintaining consistency and integrity

5. Latches: Provide only exclusive access to protected data structures Request are not queued, if a request fails, process may try later Locks: Allow serialized access to some resources Requests for locks are queued and serviced in order Differences between locks and latches

6. Latches: Simple data structure Protect resources that are briefly needed (LRU list) Very efficient Locks: Complex data structure that is further protected by latch Protect resources needed for a longer time (e.g. tables) Less efficient Locks & Latches

7. Categories of latches: • Solitary latches protecting one data structure (majority of latches) • Multiple latches protecting different parts of a single data structure (grouped in a child-parent relationship) • Latches protect locks (type varies depending on type of locks)

8. Modes of latches • An Oracle process can request a latch in one of two modes: • Willing-to-Wait Mode • If the requested latch is not immediately available, the process will wait. • Immediate Mode (no-wait mode) • Then process will not wait if the requested latch is not available and it continues processing

9. Latch free wait (spin & sleep) 1- Active wait or spin • When an attempt to get a latch in a willing-to-wait mode fails, the process will spin and try again 2- Sleep • If the number of attempts reaches the value of SPIN_COUNT parameter, the process sleeps • Sleeping is more expensive than spinning

10. Wakeup Mechanisms • Timeout • The operating system signals (wakes up) the process when a set alarm is triggered • Latch wait posting • The next process to free the required latch will wake up the process waiting for the latch • Initiated by the requesting process before going to sleep by putting itself in a latch wait list

11. Benefit & cost of wait posting • Benefit: • The process is woken up as soon as the latch is freed • Cost: • Requires protecting a latch wait list data structure by yet another latch, namely latch wait list latch • When used extensively, it can result in a secondary latch contention

12. Latch Contention • Latch contention has a significant impact on performance when: • Enough latches are not available • A latch is held for a relatively long time • Latch contention can be resolved by increasing specific init.ora parameters associated with latches • To detect latch contention latch statistics should be examined

13. Dynamic Performance Views for latches • Oracle collects statistics for the activity of all latches and stores them in the dynamic performance view V$LATCH. • Latch statistics can be used to find performance problems associated latch contentions.

14. V$LATCH • Each row contains statistics for a specific type of latch. • Contains summary statistics for both non-parent and parent latches grouped by latch number (latch#). • Should be the first point of reference when investigating a suspecting latch contention.

15. Understanding the V$LATCH Statistics V$LATCH contains information such as: • GETS-Number of successful “willing-to-wait” requests for a latch • MISSES- Number of times a “willing-to-wait” process had to spin on the first try • SPIN_GETS - Number of times a latch is obtained after spinning at least once • SLEEPS- Number of times a “willing-to-wait” process slept • WAITERS_WOKEN- Number of times a wait was awakened

16. V$LATCH Statistics (2) • WAITS_HOLDING -Number of waits while holding a different latch • IMMEDIATE_GETS - Number of times obtained without a wait • IMMEDIATE_MISSES - Number of times failed to get without a wait • For the entire iterations for a latch request no more than one gets, misses and spin_gets is recorded • (gets-misses) :Number of times a latch was obtained without spinning at all

17. V$LATCHNAME • Holds information about decoded latch names for the latches shown in V$LATCH • The rows of this view have one-to-one correspondence to the rows of V$LATCH

18. Latches willing to wait • Query that shows the number of processes that had to sleep, and the number of times they had to sleep. • This query is run by UTLESTAT. SELECT name latch_name, gets, misses, round((gets-misses)/decode(gets,0,1,gets),3) hit_ratio, sleeps, round(sleeps/decode(misses,0,1,misses),3) “sleeps/misses” from stats$latches where gets != 0 order by name;

19. Evaluating the result Hit_ratio: The ratio of gets to misses: (gets-misses)/gets Sleeps/Misses: The ratio of sleeps to misses: sleeps/misses • Any latches that have a hit ratio below .99 should be investigated. • Sleeps/misses is > 1 means there were processes that had to sleep more than once before getting the latch • Increasing the parameter _LATCH_SPIN_COUNT can increase the amount of CPU time a process will burn before trying to acquire a latch (tunable in Oracle7)

20. Latches not willing to wait • For not willing-to-wait latches, the query the immediate_gets and immediate_misses columns of the v$latch view. It shows the statistics for not willing to wait latches. • This query is run by UTLESTAT. SELECT name latch_name, immed_gets nowait_gets, immed_misses nowait_misses, round((immed_gets/immed_gets+immed_misses),3) nowait_hit_ratio, from stats$latches where immed_gets + immed_misses != 0 order by name;

21. Evaluating the result • nowait_gets - Number of times a request for a not-willing-to-wait latch was successful • nowait_misses - Number of times a request for a not-willing-to-wait latch failed • nowait_hit_ratio - The ratio of nowait_misses to nowait_gets: (nowait_gets - nowait_misses) / nowait_gets. • Nowait_hit_ratio should be as close to 1 as possible

22. V$LATCHHOLDER • Contains information about the current latch holders. • Used to find the process (PID) & session (SID) of the process and session holding the latch identified by name (NAME) and address of the latch (LADDR) being held. • In conjunction it with V$SESSION reveals the identity of the user and process holding the latch

23. V$LATCH_CHILDREN • These views contain statistics about child latches and parent latches for multiple latches • Child latches with the same LATCH# have the same parents • The CHILD# column identifies the child latch for the same parent

24. V$LATCH_PARENT • Has the same columns found in V$LATCH • The union of this view and V$LACH_CHILDREN represents all latches

25. V$LATCH_MISSES • Contains statistics about missed attempts to acquire a latch • NWFAIL_COUNT - Number of times that a no-wait (immediate) acquisition of the latch failed • SLEEP_COUNT - Number of times that acquisition attempts caused sleeps

26. Key Latches • Key latches impacting performance: • redo allocation • redo copy • cache buffers LRU • enqueues • row cache objects • library cache • shared pool

27. Latches using wait posting • By default latch-wait posting is enabled for the library cache and shared pool latches • Wait posting can be entirely disabled by setting _LATCH_WAIT_POSTING to 0 (default is 1) • Setting it to 2, enables it for all latches except for cache buffers chains latch • Changing this parameter should be carefully benchmarked • Disabling it can be beneficial where contention on the library cache latch is severe

28. Sleeps Parameters • _MAX_EXPONENTIAL_SLEEP • The maximum duration of sleep (in seconds) under an exponential back-off algorithm • default value is 2 second in Oracle8 • _MAX_SLEEP_HOLDING_LATCH • The value to which maximum sleep time is reduced, if the process is already holding other latches • The default to 4 centiseconds

29. A sample query • To monitor the statistics for the redo allocation latch and the redo copy latches: SELECT name “Latch”, sum(gets) “WTW gets”, sum(misses) “WTW misses”, sum(immediate_gets) “Immediate gets, sum(immediate_misses) “Immediate Misses” FROM v$latch WHERE name IN (’redo allocation’, ’redo copy’) GROUP BY name

30. The redo allocation latch • Controls the allocation of space for redo entries in the redo log buffer. • There is only one redo allocation latch to enforce the sequential nature of the entries in the buffer. • Only after allocation, the user process may copy the entry into the buffer (copying on the redo allocation latch). • A process may only copy on the redo allocation latch if the redo entry is smaller than a threshold size, otherwise a redo copy latch is needed

31. The redo copy latch • Acquired before the allocation latch • Allocation latch is immediately released after acquisition • User process performs the copy under the copy latch, and releases the copy • User process does not try to obtain the copy latch while holding the allocation latch. • Redo copy latch is released after the redo entry copy • System with multiple CPUs may have multiple redo copy latches for the redo log buffer

32. Tuning redo allocation latch Goal: • Minimize the time that a process holds the latch Achieved by: • Reduce the frequency of “copying on the redo allocation latch”. How ? • Decrease LOG_SMALL_ENTRY_MAX_SIZE parameter value which is the threshold for number and size of redo entries to copied to redo allocation latch.

33. Tuning redo copy latch Goal: • Reduce contention on available copy latches Achieved by: • Adding more redo copy latches How ? • Set LOG_SIMULTANEOUS_COPIES up to twice the number of CPUs

34. Cache buffer LRU latch • Controls buffers replacement in the buffer cache • Each LRU latch controls a set of buffers • Each latch should have at least 50 buffers in its set • Contention detected by querying v$latch, v$session_event and v$system_event • Contention also exists if misses are higher than 3% in v$latch

35. Tuning LRU latch Goal: • Reduce cache buffer LRU latch contention Achieved by: • Having enough latches for the entire buffer cache. How ? • Set the maximum number of desired LRU latch sets with DB_BLOCK_LRU_LATCHES up to (number_of CPU’s)*2 • Adjust DB_BLOCK_BUFFERS.

36. Enqueue latch • This latch is used to protect the enqueue data structure To tune: • Set ENQUEUE_RESOURCES to a value greater than 10

37. Monitoring Wait Events Wait events on any latch (latch free wait) are recorded in WAIT and EVENT dynamic views: • V$SESSION_WAIT - Record events for which sessions are waiting or just completed waiting (e.g. latch free wait) • V$SESSION_EVENTS - Record cumulative statistics events have waited for each session (e.g. sessions latch free waits) • V$SYSTEM_EVENTS - Record cumulative wait statistics for all sessions (e.g. latch free wait). • TIMED_STATISTICS must be enabled for the above statistics to be recorded

38. v$session_wait for latch free wait • Wait parameters P1, P2 and P3 contain the following values for latch free when the process is waiting on a latch to be available

39. v$session_event &v$system_event • Symptoms of latch contention can be found in these views • Updated when the process wakes up again indicating the wait is over. • Sleep time is recorded • Consecutive sleeps during attempts to obtain a single latch is recorded as separate waits • Latching statistics in the V$LATCH family are only updated once the latch is acquired

40. Locks • Allow sessions to join a queue for a resource that is not immediately available • To achieve consistency and integrity • Performed automatically by Oracle and manually by users

41. Lock Usage • Transaction & Row-level locks • Transactions imposing implicit locks on rows • In effect for the duration of the transaction • Buffer locks • Short term block-level locks in force while modifying blocks in cache • Data dictionary locks • Locks that protect data dictionary objects

42. Lock Modes • Applied to simple objects: • X - Exclusive • S - Shared • N- Null • Applied to compound objects: • SS - Sub-shared • SX- Sub-exclusive • SSX-Shared-sub exclusive

43. Enqueue Conversion • The operation of changing the mode of an enqueue lock Example: • 1- Transaction T1 holds a lock on table TAB in SS mode • 2- T1 needs to update a row of TAB • 3- Lock is converted to SX mode

44. ENQUEUE Locks • A sophisticated locking mechanism that uses fixed arrays for the lock and the resource data structure • A request for a resource is queued • Permits several concurrent processes to share known resources to varying levels • Can protect any object used concurrently • Many of Oracle locks

45. Enqueue Resources • The fixed array for enqueue resources is sized by ENQUEUE_RESOURCES parameter. • Determines number of resources that can be concurrently locked by the lock manager • Its default value is derived from SESSIONS parameter • If set to a value greater than DML_LOCKS+20, the provided value will be used • Increase if enqueues are exhausted

46. Enqueue Locks • A second fixed array used for enqueue locking • Size set by _ENQUEUE_LOCK • Used by each session waiting for a lock or holding a lock on a resource

47. Corresponding views • Each row in v$resource represents a locked enqueue resource that is currently locked • All locks owned by enqueue state objects are shown in v$enqueue_lock • All locks held by Oracle or locks and outstanding requests for locks and latches are recorded in v$lock

48. Enqueue wait • Occurs when an enqueue request or conversion can not be granted at once • An enqueue wait event is recorded by the blocked process in the v$session_wait view

49. Enqueue statistics • Enqueue statistics recorded in V$SYSSTAT • enqueue waits • enqueue requests • enqueue conversions • enqueue timeouts • enqueue deadlocks

50. Deadlock Detection • Automatically performed by Oracle • Initiated when an enqueue wait times out and if: • The resource type is deadlock sensitive • The lock state for the resource in unchanged • When a session holding a lock on a resource is waiting for a resource that is held by the current session in an incompatible mode