latch free检查

转自

http://hi.baidu.com/szkking/blog/item/65c5070786d5f77c03088173.html

 

1.找到等待latch free的session的session id(sid)
select sid,event,p1,p1raw from v$session_wait;

2.根据该session id,查看该session正在执行的语句,sql为:
SELECT SQL_TEXT
FROM   V$SQL SQL, V$SESSION S
WHERE SQL.HASH_VALUE = S.SQL_HASH_VALUE
AND    SQL.ADDRESS = S.SQL_ADDRESS
AND    S.SID = &sid ;

3.再根据该session id,查看该session的等待状况.
在会话级，查询视图V$SESSION_WAIT时如果有该事件存在，那么该视图中的
P1－表示Latch地址，也就是进程正在等待的latch地址。
P2－表示Latch编号，对应于视图V$LATCHNAME中的latch#。
P3－表示为了获得该latch而尝试的次数。
sql为:
SELECT SID, EVENT, P1TEXT, P1RAW, P2TEXT, P2, SECONDS_IN_WAIT
FROM V$SESSION_WAIT
WHERE SID = &sid ;

4.如果event为latch free,则根据P2的值,查询具体的latch free类型,sql为:
SELECT LATCH#, NAME
FROM V$LATCH
WHERE LATCH# = :P2 ;

5.查看系统当前最多的LATCH FREE是哪种
SELECT latch#, name, gets, misses, sleeps
FROM   v$latch
WHERE sleeps>0
ORDER BY sleeps;

6.如果较多的LATCH FREE种类,和4中查询的符合,则根据P2,查询这个LATCH存在的子LATCH及其对应的类型
SELECT addr, latch#, gets, misses, sleeps
FROM   v$latch_children
WHERE sleeps>0       
AND    latch# = 98
ORDER BY sleeps desc ;

7.接着查询sleep较多的子LATCH都对应有哪些对象
select distinct a.owner,a.segment_name,a.segment_type
from   dba_extents a,
         ( select dbarfil,dbablk
           from   x$bh
           where hladdr in ( select addr
                              from   ( select addr
                                       from   v$latch_children
                                       order by sleeps desc
                                     )
                              where rownum                             )
         ) b
where a.RELATIVE_FNO = b.dbarfil
and   a.BLOCK_ID and   a.block_id + a.blocks > b.dbablk ;

8.找到对应的对象后,检查运行的SQL本身,是否产生错误的执行计划,或者表/索引的分析资料有误,特别注意表或索引的
并发度不能开太高,尤其是对OLTP系统,否则容易引起大量的(SESSION ALLOCATION/PROCESS ALLOCATION)的
LATCH FREE等待.

9.附:查看v$latchholder和v$lock视图,看是否有其他对象影响该进程.
SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, CTIME, BLOCK
FROM V$LOCK
WHERE SID > 8 ;

SELECT *
FROM   V$LATCHHOLDER ;