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