1。SQL> select addr,gets,misses,sleeps from v$latch_children where name='cache buffers chains' and misses>1000000;
ADDR GETS MISSES SLEEPS
-------- ---------- ---------- ----------
5C291AF4 156946978 1160342 3875
5C26105C 547810584 3880652 12362
SQL> select file#,dbablk,class,state from x$bh where hladdr='5C26105C';
FILE# DBABLK CLASS STATE
---------- ---------- ---------- ----------
27 196809 1 1
2、SELECT SID, EVENT, P1TEXT, P1RAW, P2TEXT, P2, SECONDS_IN_WAIT FROM V$SESSION_WAIT
2 WHERE SID = 70;
SID EVENT P1TEXT P1RAW P2TEXT P2 SECONDS_IN_WAIT
------- -------------- -------- ---------------- ------- ----- ---------------
70 latch free address 00000004125AB718 number 98 330
SQL> SELECT LATCH#, NAME FROM V$LATCH WHERE LATCH# = 98;
LATCH# NAME
---------- ----------------------------------------------------------------
98 cache buffers chains
通过下面的脚本可以看到，目前正在等待的这个子LATCH的信息：
SELECT ADDR, LATCH#, CHILD#, NAME FROM V$LATCH_CHILDREN
2 WHERE ADDR IN (SELECT P1RAW FROM V$SESSION_WAIT WHERE SID = 70);
观察LATCH_MISSES的信息：
SELECT *
FROM
(
SELECT PARENT_NAME, "WHERE", SLEEP_COUNT, WTR_SLP_COUNT, LONGHOLD_COUNT
FROM V$LATCH_MISSES
WHERE PARENT_NAME = 'cache buffers chains'
ORDER BY SLEEP_COUNT + WTR_SLP_COUNT + LONGHOLD_COUNT DESC)
WHERE ROWNUM < 20;
与热点块有关的：
SELECT OBJ, OBJECT_NAME, TCH, TIM
FROM X$BH A, DBA_OBJECTS B
WHERE HLADDR IN (SELECT P1RAW FROM V$SESSION_WAIT WHERE SID = 70)
AND A.OBJ = B.DATA_OBJECT_ID;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/543979/viewspace-412029/ ，如需转载，请注明出处，否则将追究法律责任。
