mutex,latch,lock,enqueue hash chains latch基础概念

latch A low-level serialization control mechanism used to protect shared data structures in the SGA from simultaneous access. lock A database mechanism that prevents destructive interaction between transactions accessing a shared resource such as a table, row, or system object not visible to users. The main categories of locks are DML locks, DDL locks, and latches and internal locks. Mutexes A mutual exclusion object (mutex) is a low-level mechanism that prevents an object in memory from aging out or from being corrupted when accessed by concurrent processes. A mutex is similar to a latch, but whereas a latch typically protects a group of objects, a mutex protects a single object. Mutexes provide several benefits: ■ A mutex can reduce the possibility of contention. Because a latch protects multiple objects, it can become a bottleneck when processes attempt to access any of these objects concurrently. By serializing access to an individual object rather than a group, a mutex increases availability. ■ A mutex consumes less memory than a latch. ■ When in shared mode, a mutex permits concurrent reference by multiple sessions. Internal Locks Internal locks are higher-level, more complex mechanisms than latches and mutexes and serve various purposes. The database uses the following types of internal locks: ■ Dictionary cache locks These locks are of very short duration and are held on entries in dictionary caches while the entries are being modified or used. They guarantee that statements being parsed do not see inconsistent object definitions. Dictionary cache locks can be shared or exclusive. Shared locks are released when the parse is complete, whereas exclusive locks are released when the DDL operation is complete. ■ File and log management locks These locks protect various files. For example, an internal lock protects the control file so that only one process at a time can change it. Another lock coordinates the use and archiving of the online redo log files. Data files are locked to ensure that multiple instances mount a database in shared mode or that one instance mounts it in exclusive mode. Because file and log locks indicate the status of files, these locks are necessarily held for a long time. ■ Tablespace and undo segment locks These locks protect tablespaces and undo segments. For example, all instances accessing a database must agree on whether a tablespace is online or offline. Undo segments are locked so that only one database instance can write to a segment. Latches Latches are simple, low-level serialization mechanisms that coordinate multiuser access to shared data structures, objects, and files. Latches protect shared memory resources from corruption when accessed by multiple processes. Specifically, latches protect data structures from the following situations: ■ Concurrent modification by multiple sessions ■ Being read by one session while being modified by another session ■ Deallocation (aging out) of memory while being accessed Typically, a single latch protects multiple objects in the SGA. For example, background processes such as DBWn and LGWR allocate memory from the shared pool to create data structures. To allocate this memory, these processes use a shared pool latch that serializes access to prevent two processes from trying to inspect or modify the shared pool simultaneously. After the memory is allocated, other processes may need to access shared pool areas such as the library cache, which is required for parsing. In this case, processes latch only the library cache, not the entire shared pool. Unlike enqueue latches such as row locks, latches do not permit sessions to queue. When a latch becomes available, the first session to request the latch obtains exclusive access to it.  （latch不允许排队，当latch变为可用状态，则第一个来请求的将获得独占访问，并不是原来第一个排队的，是第一个来访问时正好latch可用，是竞争机制） Latch spinning occurs when a process repeatedly requests a latch in a loop, whereas latch sleeping occurs when a process releases the CPU before renewing the latch request. （latch请求如果没有被满足，将进入sleep状态并释放cpu，随后循环请求，直到获取之后再次请求cpu） Typically, an Oracle process acquires a latch for an extremely short time while manipulating or looking at a data structure. For example, while processing a salary update of a single employee, the database may obtain and release thousands of latches. The implementation of latches is operating system-dependent, especially in respect to whether and how long a process waits for a latch. An increase in latching means a decrease in concurrency. For example, excessive hard parse operations create contention for the library cache latch. The V$LATCH view contains detailed latch usage statistics for each latch, including the number of times each latch was requested and waited for. cursor A handle or name for a private SQL area in the PGA. Because cursors are closely associated with private SQL areas, the terms are sometimes used interchangeably. child cursor The cursor containing the plan, compilation environment, and other information for a statement whose text is stored in a parent cursor. The parent cursor is number 0, the first child is number 1, and so on. Child cursors reference exactly the same SQL text as the parent cursor, but are different. For example, two statements with the text SELECT * FROM mytable use different cursors when they reference tables named mytable in different schemas. Multiple private SQL areas in the same or different sessions can point to a single execution plan in the SGA 一个会话或多个会话的多个私有SQL区（PGA中）可以执行同一个SGA中的执行计划，这就是为什么当软解析过多时 可以跳过那么多bucket的library cache mutex S（11g以前为library cache latch） latch enqueue hash chain、parent cursor的cursor mutex S 直接进行cursor pin S Latch: Enqueue Hash Chains(Doc ID 445076.1) (Doc ID 445076.1), Solution Acquiring a lock is a series of steps from getting an index number to  identify the hash bucket in the hash table to releasing free resource structures and lock data structures. 获取hash bucket的hash table的索引号的锁需要通过一些列的步骤，以去释放资源结构或锁定资源结构。 Here are the steps: 1. Identifying the Hash Chain and Allocating the Resource Structure 确定hash chain以及分配资源结构 Oracle finds the resource structure associated with the named resource using a hashing algorithm. Oracle通过hash算法寻找已经被命名的相关资源结构 In the hashing algorithm Oracle uses a hash table (array of hash buckets), which is controlled by the parameter _ENQUEUE_HASH. The size of the hash table depends upon the value of this parameter. 在hash算法中，Oracle使用一个hash表（以hash bucket排列），这个被一个隐含参数_ENQUEUE_HASH控制。Hash table的大小依赖于这个参数。 The Hash chain contains the resource structures for that hash value. hash bucket组成的hash chain包含了资源结构的hash值。   When a session tries to acquire an enqueue, Oracle applies a hash function to convert the resource name to an index number in the array of hash buckets.  当一个会话尝试获取一个队列锁，Oracle使用hash函数转换资源名称为hash bucket列表上的索引号。 Each hash bucket has one linked list attached to it, which is called a hash chain. Before accessing the hash bucket, the session acquires an enqueue hash chain latch. 每一个hash bucket都有一个链接列表， 这个被称为hash chain 在访问hash bucket之前，会话会先获取一个hash chain的排队latch（enqueue latch）                After the session acquires an enqueue hash chain latch it moves down the hash chain attached to the bucket to locate the required resource structure. 会话获取到这个hash bucket的enqueue hash chain latch之后，它将开始从从hash chain向下搜寻hash bucket请求的资源结构。 At this point because the session acquires an enqueue hash chain latch  it will record a miss or spin get in the V$LATCH view depending upon the result of the latch operation. 在会话获取到hash chain的latch之后，将会根据获取结果在V$LATCH视图中记录miss或者spin get. There can be situations where the resource structure is not available on the hash chain. In this case where a resource structure is not present, the session will acquire an enqueue latch and will record statistics about the latch operation in V$LATCH. After acquiring the enqueue latch, the session will unlink the head of the resource free list and link it into a hash chain associated with the hash bucket. The enqueue latch will be held while the resource is allocated to the resource table. 也会有hash chain上的资源不可用的情形。在资源不存在的这种情况下，会话将会获得一个排队latch以及在V$LATCH记录latch操作统计信息。 获得一个队列Latch之后，会话会将资源头部从空闲列表中取消链接，将资源链接到一个hash bucket的hash chain中。 当资源被分配到资源表中时队列Latch将会被持有。 2. Populating the Lock Data Structure with the requested resource 锁数据结构由请求的资源组成 Now the session will acquire the enqueue latch again and will unlink the head of the lock free list.  现在会话将会再次获取队列latch以及将其在free list中取消链接。 It will populate the information related to the resource being requested, like mode of lock etc.  它将会填充相关请求的资源信息，比如锁模式等等 Now after populating this information, the session will link this lock structure to one of the linked lists (owner, waiter or converter) associated with the resource structure, depending upon the other sessions owning that resource structure or waiting to own the lock, or waiting to convert the existing held lock for that resource.  在填充了信息之后，会话将把锁结构链接到一个已链接列表（所有者、等待者、转换者）相关的资源结构，根据其他会话正在 拥有或者正在等待持有锁、或正在等待转换正在持有的资源锁。 Oracle will record the statistics in V$LATCH according to the result of the enqueue latch operation. By this time both the enqueue latch and enqueue hash chain latch are held by this session. The session will release the enqueue latch first then the enqueue hash chain latch after linking the lock data structure with the resource structure. Oracle将会记录这些统计信息在V$LATCH视图，根据队列latch操作的结果，此时enqueue latch以及enqueue hash chain 都将被这个会话持有。这个会话在链接到资源结构的锁数据结构之后将会首先释放enqueue latch，然后释放enqueue hash chain latch。 Now if the session is waiting in any queue (owner, waiter or converter) for another session to complete, the enqueue wait event will be recorded in V$SESSION_EVENT. 此时，如果有会话正在等待其他会话任何队列（所有者、等待者、转换者）完成工作，enqueue wait将会记录在V$SESSION_EVENT 3. Releasing a Lock 释放锁定 The method to release a lock is mostly the same as acquiring a lock. First of all Oracle will use a hash function to determine the hash bucket where the resource structure is allocated.  释放锁的方法与获取锁的方法基本相同，首先Oracle将会使用hash算法判断资源结构在哪个hash bucket中 （这也印证了SQL完成语义解析之后，Oracle将SQL文本转换ASCII码计算HASH值之后，直接就可以定位到parent cursor应该处于哪个hash bucket） It will acquire the enqueue hash chain latch and record statistics of the latch operation in V$LATCH.  Then it will locate the resource in the hash chain (linked list associated with the hash bucket identified by hash function).  将会获得enqueue hash chain latch并在V$LATCH中记录latch操作的统计信息。然后在hash chain中搜索资源（使用hash算法已链接资源所在的hash bucket） （这也就是说在SQL执行过程中，如果需要搜索其他相关资源，会利用HASH算法直接到资源所在的hash bucket中搜寻） The session will acquire the enqueue latch and will unlink the lock data structure from the resource structure, and link the lock data structure to the lock free list and release the enqueue latch.  会话将会获得enqueue latch并从资源结构中取消链接锁数据结构，然后将锁资源结构链接到锁空闲列表并释放enqueue latch。 After releasing the enqueue latch, the session will post the next process (waiter or converter) to proceed if appropriate.  Depending upon the LRU algorithm, Oracle will decide whether to unlink the resource structure from the hash chain and link it to the resource free list or not.  根据LRU（Latest Recently Used）算法，Oracle将决定是否将资源结构从hash chain取消链接，并将它链接到资源空闲列表。 After all of this, the session will release the enqueue hash chain latch and the lock will be released. 所有这些操作之后，会话将释放enqueue hash chain latch，然后锁将会被释放。 DML locks protect objects from concurrent modification. Frequently, DML lock allocation latch contention is seen with enqueue hash chain latches, as dml_locks are implemented through TM enqueue locks. These resource structures are hanging from enqueue hash chains serialized by enqueue hash chain latches. So, reducing DML lock allocation latch contention should resolve enqueue hash chain latch contention. About the two bugs that were identified in your previous SR  #(high session with this latch and resmgr:resource group CPU method 、） PROBLEM DESCRIPTION:  There is a high contention on resource manager runnable list latch on big system.  FIX DESCRIPTION:  Have multiple runnable lists per consumer group with multiple child latches. The fix is to have multiple runnable lists for a consumer group with multiple child latches.  .  1) Number of runnable lists.  I allocate 1 runnable list per 16 CPUs. If there are 128 CPUs, then there  will be 8 runnable lists per consumer group. The number of runnable lists is  capped to 10. The number of runnable lists is tunable using an underscore  parameter.  .  2) Adding a vt to runnable list  The group of runnable lists for a consumer group is maintained by kgkp. kgsk  calls the add vt function and kgkp is the one that decides which runnable  list the vt should go to. To spread the vts evenly across the group of  runnable lists, an add counter is introduced for each consumer group. The  add counter decides the runnable list that a vt goes to. The add counter is  increased(not atomically) every time the counter is used.  .  3) Picking a vt from a runnable list  kgsk calls pick vt function and kgkp is the one that decides which runnable  list to pick from. To pick the vts evenly from all runnable lists, a pick  counter is introduced for each consumer group. The pick counter serves as the  target index. If the corresponding target runnable list is empty, the current  process will traverse the runnable lists to find the nearest non-empty  runnable list. The pick counter is increased(not atomically) every time the  counter is used.  .  As to P1, P2 and P3, they are P1 = Latch address P2 = Latch number P3 = Tries