Latch Free事件处理

最新推荐文章于 2021-12-14 09:55:03 发布

congshi5914

最新推荐文章于 2021-12-14 09:55:03 发布

阅读量279

点赞数

文章标签：数据库数据结构与算法

1. Monitor the Global System event for Latch free, up to now the wait time is hung about 655 sec

SQL> select event,time_waited from v$system_event where event='latch free';

EVENT TIME_WAITED
---------------------------------------------------------------- -----------
latch free 65549

2. Find the most of the sessions are waiting for Latch free event.

SQL> select event,seconds_in_wait,state from v$session_wait where event='latch free';

EVENT                          SECONDS_IN_WAIT STATE
------------------------------ --------------- -------------------
latch free                                   5 WAITED KNOWN TIME
latch free                                   3 WAITED KNOWN TIME
latch free                                   0 WAITING
latch free                                   5 WAITED KNOWN TIME
latch free                                   5 WAITED KNOWN TIME
latch free                                   0 WAITED KNOWN TIME
latch free                                   3 WAITED KNOWN TIME
latch free                                   0 WAITED KNOWN TIME
latch free                                   0 WAITING
latch free                                   3 WAITED KNOWN TIME
latch free                                   3 WAITED KNOWN TIME
latch free&n

bsp;                                  3 WAITED KNOWN TIME
latch free                                   5 WAITED KNOWN TIME
latch free                                   3 WAITED KNOWN TIME
latch free                                   3 WAITED KNOWN TIME
latch free                                   3 WAITED KNOWN TIME
latch free                                   0 WAITING
latch free                                   0 WAITING
latch free                                   5 WAITED KNOWN TIME

3. The user who are waiting for the latch free event.

SQL> select username from v$session where sid in (select sid from v$session_wait where event='latch free');

USERNAME
------------------------------
OSUSER
OSUSER
F_SW
F_SW
OSUSER
F_SW
F_SW
F_SW
F_SW
F_SW
F_SW
F_SW
F_SW

4. The Key latch contention is for "cache buffers chains " with 70279873 gets.

SQL> select addr,latch#,name,gets,spin_gets from v$latch order by spin_gets;
..........................................................................

ADDR         LATCH# NAME                                 GETS SPIN_GETS
-------- ---------- ------------------------------ ---------- ----------
2000B794         88 mostly latch-free SCN                1132          3
20015FBC        158 library cache pin                   90104          5
2000BDE0         93 cache buffers lru chain             32434         11
200061D4         15 messages                             7853         22
2000469C          7 session idle bit                   634637         35
20015E34

        156 shared pool                         53226         36
20015EF8        157 library cache                      271167         45
2000D204         98 cache buffers chains             70279873        578

5.这里我在补充一下，对于buffer cache中的每个hash chain链表来说，都会有一个名为cache buffers chains latch的latch来保护对hash chain的并发操作，这种latch通常也叫作hash latch或CBC latch。数据库中会有很多的cache buffers chains latch，每个latch都叫做child cache buffers chains latch。一个child cache buffers chains latch会管理多个hash chain。前面我们知道，hash chain的数量由一个隐藏参数：_db_block_hash_buckets决定。同样也有一个隐藏参数：_db_block_hash_latches来决定有多少个cache buffers chains latch来管理这些hash chain。该参数的缺省值由buffer cache中所含有的内存数据块的多少决定，当内存数据块的数量

    •少于2052个时，_db_block_hash_latches = power(2,trunc(log(2, 内存块数量 - 4) - 1))
    •多于131075个时，_db_block_hash_latches = power(2,trunc(log(2, db_block_buffers - 4) - 6))
    •位于2052与131075 buffers之间，_db_block_hash_latches = 1024

SQL> show parameter db_block_size;

NAME TYPE VALUE
------------------------------------ ----------- -----------------------------
db_block_size integer 8192
SQL> show parameter db_cache_size;

NAME TYPE VALUE
------------------------------------ ----------- -----------------------------
db_cache_size big integer 637534208

这个时候内存数据块的总容量就是：637534208/8192=77824个

SQL> select count(distinct(hladdr)) from x$bh;

COUNT(DISTINCT(HLADDR))
-----------------------
8192

SQL> select count(*) from v$latch_children where name='cache buffers chains';

COUNT(*)
----------
8192

我们可以知道cache buffer chains latch的数量是8192个

SQL> select x.ksppinm, y.ksppstvl, x.ksppdesc from x$ksppi x , x$ksppcv y where
x.indx = y.indx and x.ksppinm like '\_%' escape '\' and ksppinm like '%_db_block
_hash_buckets%';

KSPPINM
----------------------------------------------------------------
KSPPSTVL
--------------------------------------------------------------------------------
KSPPDESC
----------------------------------------------------------------
_db_block_hash_buckets
152183
Number of database block hash buckets

这里获得了_db_block_hash_buckets的数量是152183

因此一个cache buffer chains latch管理了将近18个hash bucket

5. Follow objects are involved in Latch free wait events.

SQL> select a.hladdr, a.file#, a.dbablk, a.tch, a.obj, b.object_name from x$bh a, dba_objects b where (a.obj = b.object_id or a.obj = b.data_object_id) and a.hladdr in (select p1raw from v$session_wait where event = 'latch free');

HLADDR FILE# DBABLK TCH OBJ
-------- ---------- ---------- ---------- ----------
OBJECT_NAME
------------------------------
677FC544 11 5361 0 32634
GENERIC

677FC544 11 13553 0 32634
GENERIC

677FC544 12 3180 0 33094
VWQUEUE1_124

677FC544 12 11372 0 33094
VWQUEUE1_124

67AB2FB8 11 4890 0 32634
GENERIC

67AB2FB8 11 13082 0 32634
GENERIC

67AB2FB8 12 2709 0 33094
VWQUEUE1_124

67AB2FB8 12 10901 0 33094
VWQUEUE1_124

67AB2FB8 12 19093 0 33094
VWQUEUE1_124

I am sure it is a slightly hot block issue:-) with partial same HLADDR

ANOTHER method:
select sid, p1raw, p2, p3, seconds_in_wait, wait_time, state from v$session_wait where event = 'latch free' order by p2, p1raw;

select a.hladdr, a.file#, a.dbablk, a.tch, a.obj, b.object_name from x$bh a, dba_objects b where (a.obj = b.object_id or a.obj = b.data_object_id) and a.hladdr = &p1raw;

6. Here may have some low effecient SQL/PL SQL routin that have a high buffer gets.
select /*+ rule */ s.sql_text
from x$bh a,dba_extents b,
(select * from (select addr from v$latch_children
where name = 'cache buffers chains' order by sleeps desc)
where rownum<11) c,
v$sqltext s
where a.hladdr = c.addr
and a.dbarfil = b.relative_fno
and a.dbablk between b.block_id and b.block_id + b.blocks
and s.sql_text like '%'||b.segment_name||'%' and b.segment_type='TABLE'
order by s.hash_value,s.address,s.piece

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12361284/viewspace-620167/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12361284/viewspace-620167/