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