Read by other session wait event.

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:

 

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.

Switching Number of Waits:

Reference: -------------------------------------------------------------------------------------------------- Rhys --------------