天萃荷净
数据库的CPU使用率为100%,应用相应迟缓。查看AWR中数据库的latch:cache buffers chains等待较多
当一个数据块读入sga区,相应的buffer header会被放置到hash列表上,我们称其这hash chains,chain在中文的意为链条或串的意思,表达就是关连性.如果一个进程想访问或修改hash chain上的block,它首先要获得”cache buffers chains” latch。
原因一:低效率的SQL语句(主要体现在逻辑读过高)
cache buffers chains latch很大程度与逻辑读有关,所以要观注v$sql中BUFFER_GETS/EXECUTIONS大的语句。
同时每一个逻辑读需要一个latch get 操作及一个cpu操作,这样的sql也会很耗cpu资源。
原因二:热块(访问过于频繁)
找出热点块方法一:
--找出p1raw
select p1,p1raw from v$session_wait where event='latch: cache buffers chains';
--找到对象
SELECT /*+ RULE */
E.OWNER || '.' || E.SEGMENT_NAME SEGMENT_NAME,
E.PARTITION_NAME,
E.EXTENT_ID EXTENT#,
X.DBABLK - E.BLOCK_ID + 1 BLOCK#,
X.TCH,
L.CHILD#
FROM SYS.V$LATCH_CHILDREN L, SYS.X$BH X, SYS.DBA_EXTENTS E
WHERE X.HLADDR = '00000002576EE018'--p1raw
AND E.FILE_ID = X.FILE#
AND X.HLADDR = L.ADDR
AND X.DBABLK BETWEEN E.BLOCK_ID AND E.BLOCK_ID + E.BLOCKS - 1
ORDER BY X.TCH DESC;
找出热点块方法二:
--直接找出热点块
SELECT OBJECT_NAME, SUBOBJECT_NAME
FROM DBA_OBJECTS
WHERE DATA_OBJECT_ID IN
(SELECT DATA_OBJECT_ID
FROM (SELECT OBJ DATA_OBJECT_ID, FILE#, DBABLK, CLASS, STATE, TCH
FROM X$BH
WHERE HLADDR IN (SELECT ADDR
FROM (SELECT ADDR
FROM V$LATCH_CHILDREN
ORDER BY (GETS + MISSES + SLEEPS) DESC)
WHERE ROWNUM < 10)
ORDER BY TCH DESC)
WHERE ROWNUM < 10);
--------------------------------------ORACLE-DBA----------------------------------------
最权威、专业的Oracle案例资源汇总之案例:Oracle等待事件latch: cache buffers chains故障优化处理总结