This wait event occurs when we are trying to access a buffer in the buffer cache but we find that the buffer is currently being read from disk by another user so we need to wait for that to complete before we can access it. In previous versions, this wait was classified under the "buffer busy waits" wait-event. However, in Oracle 10.1 and higher, the wait time is now broken out into the "read by other session" wait event.
This is the class of block being waited on. In particular:
class 1 indicates a "data block", which could be table or index
class 4 indicates a "segment header"
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.
eg:
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.
Systemwide Waits:
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 / Wait times:
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.
Document:1477229.1 Resolving Issues Where 'read by other session' Waits When I/O is Slow
Document:1476081.1 Resolving Issues Where 'read by other session' Waits Occur When I/O is NOT Slow and Cause is Unknown
Document:223117.1 Troubleshooting I/O Related Waits
Known Issues / Bugs:
You can restrict the list below to issues likely to affect one of the following versions by clicking the relevant button:
Document:1628089.1 AWR Report Interpretation Checklist for Diagnosing Database Performance Issues
Document:1359094.1 How to Use AWR Reports to Diagnose Database Performance Issues
Document:1274511.1 General SQL_TRACE / 10046 trace Gathering Examples
This wait event occurs when we are trying to access a buffer in the buffer cache but we find that the buffer is currently being read from disk by another user so we need to wait for that to complete before we can access it. In previous versions, this wait was classified under the "buffer busy waits" wait-event. However, in Oracle 10.1 and higher, the wait time is now broken out into the "read by other session" wait event.
This is the class of block being waited on. In particular:
class 1 indicates a "data block", which could be table or index
class 4 indicates a "segment header"
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.
eg:
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.
Systemwide Waits:
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 / Wait times:
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.
Document:1477229.1 Resolving Issues Where 'read by other session' Waits When I/O is Slow
Document:1476081.1 Resolving Issues Where 'read by other session' Waits Occur When I/O is NOT Slow and Cause is Unknown
Document:223117.1 Troubleshooting I/O Related Waits
Known Issues / Bugs:
You can restrict the list below to issues likely to affect one of the following versions by clicking the relevant button:
Document:1628089.1 AWR Report Interpretation Checklist for Diagnosing Database Performance Issues
Document:1359094.1 How to Use AWR Reports to Diagnose Database Performance Issues
Document:1274511.1 General SQL_TRACE / 10046 trace Gathering Examples
7337
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
