Oracle Consulting Oracle Training Oracle Support Development
Oracle Books
SQL Server Books
IT Books
Job Interview Books
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy



Read By Other Session

Article by Rampant Author Brian Carr

read by other session occurs when two users need access to the same block of data. The first user reads the data from disk and places it in the buffer cache. The second user has to wait for the first users operation to complete so they are placed in to waiting. This is when the read by other session wait occurs. Unfortunately this is one of those events we need to "catch in the act" to properly resolve.

Use the following queries (during user load) to identify the hot block(s):
SELECT p1 "file#", p2 "block#", p3 "class#" FROM v$session_wait WHERE event = 'read by other session';

Once a common block has been identified use this query to identify the hot object:
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;

The next step is to tune queries that interact with this object. Secondly increasing PCTFREE and allowing for less dense blocks may address the problem, depending on the real root cause however the best solution is SQL tuning. A third option is to move the table to a smaller tablespace, rather than the current 16k tablespace size, to reduce the amount of data in each block.

Note: PCTFREE is still required even with ASSM because it determines how rows will be packed into blocks, while freelists, pctused and pctincrease are ignored with ASSM because they are used for transaction/block management.




 Copyright © 1996 -2017 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks