1 / 60

Tuning Database Locks & Latches

Tuning Database Locks & Latches. Hamid R. Minoui Fritz Companies Inc. NoCOUG May 16, 2001. 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.

faunus
Download Presentation

Tuning Database Locks & Latches

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related