【效能調整】等待事件(十) 10g中的latch等待

10g中的latch等待

從10g release 1開始latch free中一些等待已經分離出來成為單獨的event：

SQL> select name from v$event_name where name like 'latch%' order by 1;

NAME

----------------------------------------------------------------

latch activity

latch free

latch: Change Notification Hash table latch

latch: In memory undo latch

latch: KCL gc element parent latch

latch: MQL Tracking Latch

latch: Undo Hint Latch

latch: cache buffer handles

latch: cache buffers chains

latch: cache buffers lru chain

latch: checkpoint queue latch

NAME

----------------------------------------------------------------

latch: enqueue hash chains

latch: gcs resource hash

latch: ges resource hash list

latch: library cache

latch: library cache lock

latch: library cache pin

latch: messages

latch: object queue header heap

latch: object queue header operation

latch: parallel query alloc buffer

latch: redo allocation

NAME

----------------------------------------------------------------

latch: redo copy

latch: redo writing

latch: row cache objects

latch: session allocation

latch: shared pool

latch: undo global data

latch: virtual circuit queues

已選擇29行。

SQL>

可以從v$system_event看看系統的latch相關的等待資訊：

SQL> col event on format a30

SQL> select a.EVENT,

2 a.TOTAL_WAITS,

3 a.TOTAL_TIMEOUTS

4 from v$system_event a

5 where a.event like 'latch%';

EVENT TOTAL_WAITS TOTAL_TIMEOUTS

------------------------------ ----------- --------------

latch: cache buffers chains 2 0

latch: redo writing 3 0

latch: shared pool 4 0

latch: library cache pin 1 0

latch free 78 0

latch: cache buffers lru chain 1 0

latch: redo allocation 2 0

已選擇7行。

SQL>

從前面latch的原理可以知道，出現latch等待意味著程式在請求willing-to-wait模式時再_spin_count內失敗，並sleep，如果程式有大量的latch競爭，由於spin的緣故也要消耗大量的cpu資源，同時往往也會帶來高響應時間的後果。

V$system_event中latch等待的total_waits資訊就是程式在willing-to-wait模式下獲得latch的失敗次數。

SQL> select a.total_waits, b.sum_of_sleeps

2 from (select sum(total_waits) total_waits from v$system_event where event like 'latch%') a,

3 (select sum(sleeps) sum_of_sleeps from v$latch) b;

TOTAL_WAITS SUM_OF_SLEEPS

----------- -------------

91 91

這個就是總計的等待和sleep數了。

Latch miss定位

檢視v$latch_misses儲存了oracle核心程式碼中的latch丟失資訊。這個資訊對診斷latch等待非常有幫助們看看這個檢視：

SQL> select location, parent_name, wtr_slp_count, sleep_count, longhold_count

2 from v$latch_misses

3 where sleep_count > 0

4 order by wtr_slp_count, location;

LOCATION PARENT_NAME WTR_SLP_COUNT SLEEP_COUNT LONGHOLD_COUNT

----------------------------------- ------------------------------ ------------- ----------- --------------

kcbgtcr: kslbegin shared cache buffers chains 0 1 1

kcbzwb cache buffers chains 0 1 0

kcrfsr: rba scn pair redo writing 0 2 0

kcrfw_redo_gen: redo allocation 1 redo allocation 0 2 0

kcrrasgn archive process latch 0 2 0

kghfre shared pool 0 1 0

kglpnc: child library cache pin 0 1 0

ksqgtl2 enqueues 0 2 0

ksvcreate slave class create 0 3 0

kcbzgws_1 cache buffers lru chain 1 1 0

kcrfw_cal_target_rba redo writing 1 1 0

kcrrgpll archive process latch 1 1 0

kghalo shared pool 1 3 0

kcrrcrlc archive process latch 2 1 0

kcrrsarc archive process latch 2 1 0

No latch event range base latch 70 70 0

已選擇16行。

給出了latch的等待位置，休眠數，長等待數等資訊。

從v$latch檢視中可以看到當前等待的一些資訊：

SQL> set lines 200

SQL> set pages 1000

SQL> select * from (

2 select name, gets, misses, immediate_gets, immediate_misses, sleeps

3 from v$latch

4 order by sleeps desc) where sleeps >0;

NAME GETS MISSES IMMEDIATE_GETS IMMEDIATE_MISSES SLEEPS

-------------------------------------------------- ---------- ---------- -------------- ---------------- ----------

qmn task queue latch 1482 84 0 0 70

archive process latch 3685 4 0 0 5

shared pool 584051 196 0 0 4

slave class create 42 3 0 0 3

redo writing 21879 7 0 0 3

redo allocation 21174 14 105878 4 2

cache buffers chains 6213706 4 77766 1 2

enqueues 156842 33 0 0 2

cache buffers lru chain 37084 36 4313 2 1

library cache pin 363819 6 3 0 1

已選擇10行。

SQL>

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16179598/viewspace-677108/，如需轉載，請註明出處，否則將追究法律責任。

【效能調整】等待事件(十) 10g中的latch等待

相關文章