两个或者多个会话同时需要把硬盘中的对象装载到data buffer中,当其中一个会话把对象装入,其他会话就处于read by other session等待状态。这个等待也就是说明数据库存在读的竞争,所以该等待事件通常和db
file sequential read或db file scattered read
同时出现。
--查找热点块对象语句
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
GROUPBY O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE
ORDERBYSUM(TCH)
DESC)
WHEREROWNUM <=
10;
--查找热点块操作语句
SELECT/*+rule*/ HASH_VALUE, SQL_TEXT
FROM V$SQLTEXT
WHERE (HASH_VALUE, ADDRESS)
IN
(SELECT A.HASH_VALUE, A.ADDRESS
FROM V$SQLTEXT A,
(SELECTDISTINCT A.OWNER, A.SEGMENT_NAME, A.SEGMENT_TYPE
FROM DBA_EXTENTS A,
(SELECT DBARFIL, DBABLK
FROM (SELECT DBARFIL, DBABLK
FROM X$BH
ORDERBY TCH
DESC)
WHEREROWNUM <
11) B
WHERE A.RELATIVE_FNO = B.DBARFIL
AND A.BLOCK_ID <= B.DBABLK
AND A.BLOCK_ID + A.BLOCKS > B.DBABLK) B
WHERE A.SQL_TEXT
LIKE'%' || B.SEGMENT_NAME ||
'%'
AND B.SEGMENT_TYPE =
'TABLE')
ORDERBY HASH_VALUE, ADDRESS, PIECE;