通过案例学调优之--Oracle Latch基础知识
一、什么是Latch1.1、Latch是一种低级排队(串行)机制,用于保护SGA中共享内存结构。
1.2、Latch是一种快速的被获取和释放的内存锁,用于防止共享内存结构被多个用户同时
访问。
1.3、Latch请求的类型:willing-to-wait和immediate。
1.3.1对于willing-to-wait类型的latch:
如果一个进程在第一次尝试中没有获得latch,那么它会等待并且再尝试一次,如果经过_spin_count次争夺不能获得latch,然后该进程转入睡眠状态,睡眠醒来后,按顺序重复以
前的步骤。睡眠的时间会越来越长。
1.3.2对于immediate类型的latch:
如果该闩不能立即得到的话,那么该进程就不会为获得该latch而等待。它将继续执行
另一个操作。
1.3.3可以通过v$latch视图查看latch的具体信息,在v$latch中:(以下字段反映了Willing-to-wait类型请求)GETS:成功地以Willing-to-wait请求类型请求一个latch的次数。MISSES:初始以Willing-to-wait请求类型请求一个latch不成功的次数。
SLEEPS:初始以Willing-to-wait请求类型请求一个latch不成功后,进程等待获取latch的次数。
(以下字段反映了Immediate类型请求)IMMEDIATE_GETS:以Immediate请求类型成功地获得一个latch的次数。
IMMEDIATE_MISSES:以Immediate请求类型请求一个latch不成功的次数。12:27:09 SYS@ prod >desc v$latch
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
ADDR RAW(4)
LATCH# NUMBER
LEVEL# NUMBER
NAME VARCHAR2(64)
HASH NUMBER
GETS NUMBER
MISSES NUMBER
SLEEPS NUMBER
IMMEDIATE_GETS NUMBER
IMMEDIATE_MISSES NUMBER
WAITERS_WOKEN NUMBER
WAITS_HOLDING_LATCH NUMBER
SPIN_GETS NUMBER
SLEEP1 NUMBER
SLEEP2 NUMBER
SLEEP3 NUMBER
SLEEP4 NUMBER
SLEEP5 NUMBER
SLEEP6 NUMBER
SLEEP7 NUMBER
SLEEP8 NUMBER
SLEEP9 NUMBER
SLEEP10 NUMBER
SLEEP11 NUMBER
WAIT_TIME NUMBER
1.4、 与队列锁(enqueue)不同的是:
对于Latch的请求,不用排队等待资源,由于Latch的分配相当随机,所以当Latch释放后,不管哪个会话请求都可能得到它,当不能获得Latch的会话,就会在后续不断的重试(spin->sleep->spin...->get latch)。1.4.1为什么自旋(spin)?
在一台多CPU的机器上,如果Latch不能立即获得,那么会话就会自旋,即在循环中反复地
尝试来或得Latch。如果不这样的话,那么就会导致上下文切换(context switching),这样对
资源开销更大。
1.4.2得到Latch的伪代码如下:If Latch gotten
Then
Return SUCCESS
Else
Misses on that Latch = Misses + 1
Loop
For I in 1 .. 2000(_spin_count)
Loop
Attempt to get Latch
If Latch gottenThen
Return SUCCESS
End if
End loopGo to sleep for short period
Sleep on Latch = Sleeps + 1
End loop
End if
图示如下:
所以,可以知道,当尝试获得Latch的时候,可能会消耗大量的CPU时间。系统看上去很忙,
但并没有做多少实际工作。
1.5、相关视图1.5.1 v$latch :
V$LATCH shows aggregate latch statistics for both parent and child latches, grouped by latch name.
13:20:55 SYS@ prod >desc v$latch
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
ADDR RAW(4)
LATCH# NUMBER
LEVEL# NUMBER
NAME VARCHAR2(64)
HASH NUMBER
GETS NUMBER
MISSES NUMBER
SLEEPS NUMBER
IMMEDIATE_GETS NUMBER
IMMEDIATE_MISSES NUMBER
WAITERS_WOKEN NUMBER
WAITS_HOLDING_LATCH NUMBER
SPIN_GETS NUMBER
SLEEP1 NUMBER
SLEEP2 NUMBER
SLEEP3 NUMBER
SLEEP4 NUMBER
SLEEP5 NUMBER
SLEEP6 NUMBER
SLEEP7 NUMBER
SLEEP8 NUMBER
SLEEP9 NUMBER
SLEEP10 NUMBER
SLEEP11 NUMBER
WAIT_TIME
1.5.2 v$latch_children:V$LATCH_CHILDREN contains statistics about child latches.
13:20:15 SYS@ prod >desc v$latch_children
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
ADDR RAW(4)
LATCH# NUMBER
CHILD# NUMBER
LEVEL# NUMBER
NAME VARCHAR2(64)
HASH NUMBER
GETS NUMBER
MISSES NUMBER
SLEEPS NUMBER
IMMEDIATE_GETS NUMBER
IMMEDIATE_MISSES NUMBER
WAITERS_WOKEN NUMBER
WAITS_HOLDING_LATCH NUMBER
SPIN_GETS NUMBER
SLEEP1 NUMBER
SLEEP2 NUMBER
SLEEP3 NUMBER
SLEEP4 NUMBER
SLEEP5 NUMBER
SLEEP6 NUMBER
SLEEP7 NUMBER
SLEEP8 NUMBER
SLEEP9 NUMBER
SLEEP10 NUMBER
SLEEP11 NUMBER
WAIT_TIME NUMBER
1.5.3 v$latch_misses :This view contains statistics about missed attempts to acquire a latch.
13:20:10 SYS@ prod >desc v$latch_misses
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
PARENT_NAME VARCHAR2(64)
WHERE VARCHAR2(80)
NWFAIL_COUNT NUMBER
SLEEP_COUNT NUMBER
WTR_SLP_COUNT NUMBER
LONGHOLD_COUNT NUMBER
LOCATION VARCHAR2(80)
1.5.4 v$latchname :This view contains information about decoded latch names for the latches shown in
13:19:11 SYS@ prod >desc v$latchname
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
LATCH# NUMBER
NAME VARCHAR2(64)
HASH NUMBER
---原作者:zhuxu@taobao.com(感谢作者)