latch: cache buffers chains故障处理总结

客户说数据库的CPU使用率为100%，应用相应迟缓。 发现是latch: cache buffers chains 作祟 故障分析思路 查看等待事件，判断故障起因 SQL>select * from (select sid,event,p1,p2,p3,p1text,WAIT_TIME,SECONDS_IN_WAIT from v$session_wait where wait_class# <> 6 order by wait_time desc) where rownum <=10; 确认为latch: cache buffers chains引起的故障后，查看latch的命中率 SQL>SELECT name, gets, misses, sleeps,       immediate_gets, immediate_misses      FROM v$latch    WHERE name = 'cache buffers chains'; 各列名称意义如下 NAME：latch名称 IMMEDIATE_GETS：以Immediate模式latch请求数 IMMEDIATE_MISSES：请求失败数 GETS：以Willing to wait请求模式latch的请求数 MISSES：初次尝试请求不成功次数 SPIN_GETS：第一次尝试失败，但在以后的轮次中成功 SLEEP[x]：成功获取前sleeping次数 WAIT_TIME：花费在等待latch的时间 这里需要注意MISSES/GETS如果在达10%左右，则说明有比较严重的latch争用,也可以通过查询v$latch_children视图查看其他latch信息 ，语句如下 SQL> SELECT *     FROM (SELECT   addr, child#, gets, misses, sleeps, immediate_gets igets,                   immediate_misses imiss, spin_gets sgets               FROM v$latch_children              WHERE NAME = 'cache buffers chains'           ORDER BY sleeps DESC)    WHERE ROWNUM < 11; 关于latch的统计信息，主要关注以下几部分 misses/gets的比率是多少 获自spinning的misses的百分比是多少 latch请求了多少次 latch休眠了多少次 查看热点对象和访问信息，TCH列表示对象被访问的次数 SQL> SELECT *     FROM (  SELECT addr,                    ts#,                    file#,                    dbarfil,                    dbablk,                    tch               FROM x$bh           ORDER BY tch DESC)    WHERE ROWNUM < 11; 通过对象的文件号和块号查看具体对象信息 SQL>select owner, segment_name, partition_name, tablespace_name from dba_extents where relative_fno = &v_dba_rfile and &v_dba_block between block_id and block_id + blocks - 1; 也可以通过如下sql查找热点块，主要 SELECT *   FROM (SELECT O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE, SUM(TCH) TOUCHTIME           FROM X$BH B, DBA_OBJECTS O          WHERE B.OBJ = O.DATA_OBJECT_ID            AND B.TS# > 0          GROUP BY O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE          ORDER BY SUM(TCH) DESC)  WHERE ROWNUM <= 10; 查看引起latch: cache buffers chains的sql SQL> select * from (select     count(*),     sql_id,     nvl(o.object_name,ash.current_obj#) objn,     substr(o.object_type,0,10) otype,        CURRENT_FILE# fn,          CURRENT_BLOCK# blockn    from  v$active_session_history ash        , all_objects o    where event like 'latch: cache buffers chains'      and o.object_id (+)= ash.CURRENT_OBJ#    group by sql_id, current_obj#, current_file#,                   current_block#, o.object_name,o.object_type    order by  count(*) desc )where rownum <=10; 根据上面得到的sql_id信息查看sql全文 SQL>select sql_fulltext from v$sqlarea where sql_id='&sqlid'; 查看SQL的执行计划 SQL>SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(('&sql_id',0)); 在认为sql执行计划不准确的情况也可以通过sql_id查看sql的address和hash_value查看sql的实际执行计划 SQL>SELECT  address, hash_value FROM v$sql      WHERE sql_id='&sql_id'; SQL>SELECT operation, options, object_name, cost FROM v$sql_plan      WHERE address = '&addr' AND hash_value = 'hash_v'; 当某个会话长时间持有latch时，可以通过联合v$latchholder和v$session视图查看sql信息 SQL>SELECT s.sql_hash_value,s.sql_id,s.address, l.name   FROM V$SESSION s, V$LATCHHOLDER l WHERE s.sid = l.sid; 故障处理思路 1、根据sql执行计划判断该执行计划是否正确，sql执行过长往往意味着过长时间的持有latch。 2、优化nested loop join，如果有可能使用hash join代替nested loop join。也可以利用对热块索引进行hash分区，或者使用hash簇的方式减缓热块现象。 3、调整表的pctfree值，将数据尽可能的分布到多个块中 4、调整应用