【性能调整】等待事件(十) 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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16179598/viewspace-677108/