Readbyothersession wait event.

Today is. Starting from today, I plan to learn a waiting event every day before the Spring Festival. Today I will record the readbyothersession waiting event note. What is readbyothersession? Thiswaiteventoccurswhenwearetryingtoaccessabufferinthebuffercachebutwefin

Today is. Starting from today, I plan to learn a wait event every day before the Spring Festival. Today I will record the wait event note read by other session. What is read by other session? This wait event occurs when we are trying to access a buffer in the buffer cache but we fin

Today is. Starting from today, I plan to learn a wait event every day before the Spring Festival. Today I will record the wait event note read by other session.

What is read by other session?

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" event. however, in Oracle 10.1 and higher, the wait time is now broken out into the "read by other session" wait event.

Excessive waits for this event are typically due to several processes repeatedly reading the same blocks, e.g. specified sessions scanning the same index or parameter Ming full table scans on the same table. tuning this issue is a matter of finding and eliminating this contention.

Reference:Document ID 732891.1

Official introduction:

Read by other session

This event occurs when a session requests a buffer that is currently being read into the buffer cache by another session. prior to release 10.1, waits for this event were grouped with the other reasons for waiting for buffers under the 'buffer busy wait' event

Wait Time: Time waited for the buffer to be read by the other session (in microseconds)

Parameter Description
File # See "file #"
Block # See "block #"
Class # See "class"

Note that p1, p2, p3, and ,.

How can this problem be solved?

Generally, this problem is caused by SQL or disk devices.

When this problem occurs, you must first locate the SQL statement.

Method 1: use ash to obtain fine-grained reports and view top SQL statement to obtain SQL statements.

Method 2: use SQL statements to directly obtain:

1. Current problems:

Select SQL _fulltext from v $ SQL a, v $ session B where a. SQL _id = B. SQL _id and B. event = 'read by other session ';

2. What happened in history

Select a. SQL _id, SQL _fulltext from v $ SQL a, dba_hist_active_sess_history B where a. SQL _id = B. SQL _id and B. event = 'read by other session ';

Read by other session is often accompanied by db file sequential read events.

In addition, you can view the information about the objects involved, which is p1, p2, and p3.

SELECT p1 "file #", p2 "block #", p3 "class #"
FROM v $ session_wait WHERE event = 'read by other session ';

Obtain the hotspot object through p1, p2, and p3:

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;

In addition, you can also directly view the information of the hotspot block, such as the SQL statement caused by viewing the hotspot block:

Select SQL _text
From v $ sqltext,
(Select distinct a. owner, a. segment_name, a. segment_type
From dba_extents,
(Select dbarfil, dbablk
From (select dbarfil, dbablk from x $ bh order by tch desc)
Where rownum <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'
Order by a. hash_value, a. address, a. piece;

View hotspot block objects:

Select e. OWNER, E. SEGMENT_NAME, E. SEGMENT_TYPE
FROM DBA_EXTENTS E,
(SELECT *
FROM (select addr, TS #, FILE #, DBARFIL, DBABLK, TCH
From x $ BH
Order by tch desc)
Where rownum <11) B
Where e. RELATIVE_FNO = B. DBARFIL
And e. BLOCK_ID <= B. DBABLK
And e. BLOCK_ID + E. BLOCKS> B. DBABLK;After finding the SQL statement, you need to view the execution plan, identify the problem, and optimize it.1. You can run the following command to view the execution plan for the cursor existing in the shared pool:Select * from table (dbms_xplan.display_cursor ('SQL _ id', null, 'allstats '));2. You can view awr information for the history:Select * from table (dbms_xplan.display_awr ('SQL _ id '));In addition, you can use vmstat 2 200 to check disk read/write information for devices.

Reducing Number of Waits:

If you are seeing long delays taken to service this wait event then check the amount of I/O being completed MED on the device identified by the P1 argument of this wait event.
The device and/or the controller may be overloaded. if this is the case then take the standard steps of spreading the file into SS further devices etc. check that the real problem isn't the amount of time that the operating system is taking to service the system call. find out which file numbers are causing the highest average waits and then determine which filesystem contains the file Determine why the filesystems are using Ming poorly. some common causes are:
"Hot filesystems"-too implements active files on the same filesystem exhausting the I/O bandwidth hardware problem In Parallel Execution (PX) is being used, determine if the I/O subsystem is saturated by having too route slaves in use. reference:Document ID 1477229.1-------------------------------------------------------------------------------------------------- Rhys --------------