latch free检查

步骤如下：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 SWHERE SQL.HASH_VALUE = S.SQL_HASH_VALUEAND SQL.ADDRESS = S.SQL_ADDRESSAND 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_WAITWHERE 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, sleepsFROM v$latchWHERE sleeps>0ORDER BY sleeps;6.如果较多的LATCH FREE种类,和4中查询的符合,则根据P2,查询这个LATCH存在的子LATCH及其对应的类型SELECT addr, latch#, gets, misses, sleepsFROM 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 < 5 ) ) bwhere a.RELATIVE_FNO = b.dbarfiland a.BLOCK_ID <= b.dbablk 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, BLOCKFROM V$LOCKWHERE SID > 8 ;SELECT * FROM V$LATCHHOLDER ;