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 ;