Tuning Database Locks

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
Differences between locks and latches

• 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

6
Locks Latches

• 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

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 VLATCH.
• Latch statistics can be used to find performance
  problems associated latch contentions.

14
VLATCH

• 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 VLATCH Statistics

• VLATCH 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
VLATCH 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
VLATCHNAME

• Holds information about decoded latch names for
  the latches shown in VLATCH
• The rows of this view have one-to-one
  correspondence to the rows of VLATCH

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 statslatches 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 gt 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 vlatch 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_getsimmed_misses),3)
nowait_hit_ratio, from statslatches 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
VLATCHHOLDER

• 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 VSESSION reveals the
  identity of the user and process holding the latch

23
VLATCH_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
VLATCH_PARENT

• Has the same columns found in VLATCH
• The union of this view and VLACH_CHILDREN
  represents all latches

25
VLATCH_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 vlatch WHERE name IN (redo
allocation, redo copy) GROUP BY name
30
Evaluating the result

• Contention for a latch may exist if
• If ratio of Immediate misses to the sum of
  Immediate gets and Immediate_misses gt 1
• OR
• If ratio of misses to gets gt 1

31
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

32
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

33
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.

34
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

35
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 vlatch,
  vsession_event and vsystem_event
• Contention also exists if misses are higher than
  3 in vlatch

36
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
  CPUs)2
• Adjust DB_BLOCK_BUFFERS.

37
Enqueue latch

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

38
Monitoring Wait Events

• Wait events on any latch (latch free wait) are
• recorded in WAIT and EVENT dynamic views
• VSESSION_WAIT - Record events for which sessions
  are waiting or just completed waiting (e.g. latch
  free wait)
• VSESSION_EVENTS - Record cumulative statistics
  events have waited for each session (e.g.
  sessions latch free waits)
• VSYSTEM_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

39
vsession_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

40
vsession_event vsystem_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 VLATCH family are
  only updated once the latch is acquired

41
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

42
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

43
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

44
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

45
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

46
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_LOCKS20, the
  provided value will be used
• Increase if enqueues are exhausted

47
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

48
Corresponding views

• Each row in vresource represents a locked
  enqueue resource that is currently locked
• All locks owned by enqueue state objects are
  shown in venqueue_lock
• All locks held by Oracle or locks and outstanding
  requests for locks and latches are recorded in
  vlock

49
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 vsession_wait view

50
Enqueue statistics

• Enqueue statistics recorded in VSYSSTAT
• enqueue waits
• enqueue requests
• enqueue conversions
• enqueue timeouts
• enqueue deadlocks

51
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

52
DML Locks

• Guarantees integrity of data being access and
  modified concurrently for the entire transaction
• Prevent destructive interference of conflicting
  DML and/or DDL operations occurring at the same
  time
• Adds maintenance of locks conversion history
• Locks are held during the entire transaction
• Sessions with blocking transaction enqueue locks
  always hold a DML lock as well

53
DML_LOCKS

• DML_LOCKS - Max of DML locks-one for each table
  modified in a transaction. Equals the total
  number of locks on tables currently references by
  all users.
• If set to 0, DML locks are entirely disabled
• VLOCKED_OBJECTS reveals active slots
• DISABLE TABLE LOCKS or ALTER TABLE can be used to
  disable DML locks for particular tables
• The free list data structure for DML locks is
  protected by dml lock allocation latch

54
VLOCK view

• Records locks currently held as well as
  outstanding requests for a lock or a latch
• Key columns are
• ADDR Memory address of object in locked state
• SID Id of session holding or requesting the
  lock
• TYPE type of user or system lock
• ID1, ID2 Type dependent lock identifiers
• LMODE, REQUEST Mode the lock is held or
  requested

55
Example Locked Users

• If locking conflict are suspected, locked users
  and the statement they are running can be
  identified by the following query
• select b.username, b.serial, d.id1, a.sql_text
• from vsession b, vlock d , vsqltext a
• where b.lockwait d.kaddr
• and a.address b.sql_address
• and a.hash_value b.sql_hash_value

56
VLOCKED_OBJECTS

• Records information on all locks acquired by all
  transactions including slot numbers being used by
  locks
• Used to obtain session information for sessions
  holding DML locks on crucial database objects

57
Views created by catblock.sql

• DBA_LOCKS Gathers various lock statistics
  translated into an easier to understand format
• DBA_WRITERS Provides information on sessions
  waiting for locks on specific resources and
  sessions that have those resources blocked
• DBA_BLOCKERS Provides information on which
  sessions are holding up others

58
Other lock utilities

• Utllockt.sql provided by Oracle
• The dbms_lock package
• Oracle Enterprise Manager
• Third-party tools

59
VRESOURCE_LIMIT view

• To monitor consumption of resources
• Reveals number of used slots in the fixed array
  of lock structures
• Use it to adjust ENQUEUE_RESOURCE DML_LOCKS
  parameter settings

60
Other lock topics

• Distributed transactions
• The Lock Manager
• LCKs processes
• Global locks
• Parallel cache management (PCM) locks

61
Resources

• Oracle8i Internal Services for waits, latches,
  locks, and memory by Steve Adams
• Oracle Performance Tuning TIPS TECHNIQUES by
  Richard Niemiec
• Oracle8i Tuning Manual
• Oracle8i Reference Manual