Shulou(Shulou.com)05/31 Report--

This article mainly explains "read by other session waiting event analysis in Oracle". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "read by other session waiting event analysis in Oracle".

The scenario in which read by other session occurs:

When querying data, Oracle reads block from the hard drive to buffer cache. If there are two or more session reading the same block data at the same time, the first session reads the block to the buffer cache, and because the block has not fully read the buffer cache, the other session is waiting for "read by other session". This is the separation of oracle 10g from the "buffer busy waits" of oracle 9i, which is also a hot lump phenomenon.

The read by other session wait event takes three parameters, which belong to the wait class of User Imax O:

SQL > select name,parameter1,parameter2,parameter3,wait_class from v$event_name where name = 'read by other session'

NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS

Read by other session file# block# class# User I/O

The data block to be read by file#: is locked in the file number of the data file.

Starting block number to be read by block#:

Class#: Block Typ

You can obtain the segment of the hotspot block based on the waiting file# and block# combined with the following sql:

SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, A.PARTITION_NAME

FROM DBA_EXTENTS A

WHERE FILE_ID = & FILE_ID

AND & BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS-1

Query hotspot block objects:

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.