DBA常用SQL语句 (5) - Latch 相关

DBA常用SQL语句:

DBA常用SQL语句（1）— 概况信息

DBA常用SQL语句（2）— SGA和PGA

DBA常用SQL语句（3）- cache、undo、索引和等待事件

DBA常用SQL语句（4）- Top SQL

Latch 相关SQL

检查 Latch 的相关 SQL查询当前数据库最繁忙的 Buffer，TCH(Touch)越大表示访问次数越高

SELECT *
FROM ( SELECT addr,
ts#,
file#,
dbarfil,
dbablk,
tch
FROM x$bh
ORDER BY tch DESC)
WHERE ROWNUM < 11;

查看 latch 的命中率

SELECT name, gets, misses, sleeps,
immediate_gets, immediate_misses
FROM v$latch
WHERE name = 'cache buffers chains';

查找数据块中的热点块

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;

根据文件号和块号查找数据库对象

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;

如果在 Top 5 中发现 latch free 热点块事件时，可以从 V$latch_children 中查询具体的子Latch 信息

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: cache buffers chains 的 sql

select * from (select
count(*),
sql_id,
nvl(o.object_name,ash.current_obj#) objn,
substr(o.object_type,0,10) otype,
3 4 5 6 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;