latch free 等待事件的診斷語句

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;