|
"read by other session" Reference Note This is a reference note for the wait event "read by other session" which includes the following subsections:
See Note:61998.1 for an introduction to Wait Events. Definition:
Parameters:
This is the file number of the data file that contains the block that the waiting session wants. This is the block number in the above file# that the waiting session wants access to. See Note:181306.1 to determine the tablespace, filename and object for this file#,block# pair.
This is the class of block being waited on. In particular: o class 1 indicates a "data block", which could be table or index o class 4 indicates a "segment header" o class >=15 indicate undo blocks Wait Time: The session will keep timing-out/waiting until it acquires the buffer. Finding Blockers: The blocking session for an individual wait can usually be found using the "BLOCKING_SESSION" column of V$SESSION for the waiting (blocked) session. SELECT SID mySID, blocking_session, p1 "FILE#", p2 "BLOCK#", p3 "class#", row_wait_obj# OBJECT_ID FROM v$session WHERE event = 'read by other session' AND STATE='WAITING' ; Usually the file#/block# waited on will be changing over time. The "ROW_WAIT_OBJ#" (OBJECT_ID) may not always be populated. You can check the segment involved by running the following query using the FILE (P1) and BLOCK (P2) from the wait: SELECT relative_fno, owner, segment_name, segment_type FROM dba_extents WHERE file_id = &FILE AND &BLOCK BETWEEN block_id AND block_id + blocks - 1 ; If sessions stay stuck waiting for a specific block then investigate the blocking session - eg: it may be stuck in a read operation at OS level. To find more information: · Use ADDM and ASH reports for advice and information about sessions and SQL involved. · Check the "Buffer Wait Statistics" section of AWR reports for details of block classes causing waits. · The "Buffer Waits" columns of Tablespace and File information in AWR reports helps show where the waits are occurring, but be aware that these figures also include "buffer busy waits". · Check if these same tablespace/file/s show excessive IO or poor IO performance. Reducing waits typically involves application tuning and/or IO tuning. Contention does not mean that there is necessarily a problem, but it is more likely that selects against the objects involved are reading more blocks than they have to. These unnecessary reads can then contend. To find such selects, look for the queries that are waiting frequently for 'read by other session'. Active Session History (ASH) reports during the period where contention is seen are a useful source of this sort of information. Alternatively look for those queries that read a lot of buffers when querying these tables; it is possible that these queries are poorly optimized and perhaps a different access path may read fewer buffers and cause less contention. eg: if lots of sessions scan the same unselective index this can show as "read by other session" waits for "data blocks": · the first session processes the blocks that are in the buffer cache quickly but then a block has to be read from disk · the other sessions (scanning the same index) quickly 'catch up' and want the block which is currently being read from disk - they wait for the buffer as someone is already reading the block in. Since the 'read by other session' wait event is an indicator that the buffers being waited for are popular (and are being "read by another session"), if queries are properly optimized, then an undersized buffer cache may mean that there is insufficient space to retain all the buffers required by queries. Make sure that the buffer cache is adequately sized to keep the buffers required by the current SQL statements from being aged out. See the following documents for additional help troubleshooting issues relating to "read by other session" related waits. Document:1477229.1 Resolving Issues Where 'read by other session' Waits When I/O is Slow You can restrict the list below to issues likely to affect one of the following versions by clicking the relevant button:
|