The system encounters a read by other session wait event.

Today, the user's response system is very slow. An AWR has found a large number of read by other session wait events, accompanied by a large number of DB file sequential read wait events:

650) This. width = 650; "style =" border-right-0px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px "Title =" top5 "border =" 0 "alt =" top5 "src =" http://img1.51cto.com/attachment/201411/4/703960_14150696071lTU.jpg "width =" 509 "Height =" 205 "/>

Read by other session wait Event Description:

When we request a data, Oracle reads the data from the disk into the buffer cache for the first time. If two or more Session requests share the same information, the first session will read this information into the buffer cache, and other sessions will wait. Before 10 Gb, the waiting event is still under bufferbusy waits. After 10 Gb of Oracle, the event is taken out separately and named as read by other session. Generally, this wait event occurs because multiple processes read the same blocks repeatedly. For example, some sessions scan the same index or execute full table scan on the same block. It is best to locate and optimize related SQL statements to solve this wait event. The appearance of readby other session wait also indicates the competition of Data Inventory reading. Therefore, this wait event usually appears together with DB file sequential read or DB file scattered read.

Solution:

1. Use AWR to find the corresponding SQL for optimization.
2. Check whether the execution plan of the corresponding SQL is optimal. If necessary, optimize the execution plan using the dbms_sqltune package and use the SQL _profile file to stabilize the execution plan.
3. Check whether the statistical information of tables and indexes is outdated and collect statistical information when necessary.
4. Adjust the pctfree parameter, re-import the data, and create a heat sink block.

 

The following content is a reference to Xi Feifei Blog content: http://www.xifenfei.com/1200.html

1. If there is such a wait event in the system, we can query v $ session_wait using the following SQL statement to obtain detailed information.

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

2. query the hot block object based on file #, block #. In fact, this part can be seen directly from the AWR segments by buffer busy waits.

650) This. width = 650; "style =" border-right-0px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px "Title =" segments by buffer busy waits "border =" 0 "alt =" segments by buffer busy waits "src =" http://img1.51cto.com/attachment/201411/4/703960_1415069609vpc1.jpg "width =" 767 "Height =" 160" />

SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, A.PARTITION_NAME  FROM DBA_EXTENTS A WHERE FILE_ID = &FILE_IDAND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS – 1;

 

3. You can use the following SQL script to query specific SQL statements.

SELECT /*+rule*/ HASH_VALUE, SQL_TEXT  FROM V$SQLTEXT WHERE (HASH_VALUE, ADDRESS) IN       (SELECT A.HASH_VALUE, A.ADDRESS          FROM V$SQLTEXT A,               (SELECT DISTINCT A.OWNER, A.SEGMENT_NAME, A.SEGMENT_TYPE                  FROM DBA_EXTENTS A,                       (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 HASH_VALUE, ADDRESS, PIECE;

Oracle says most of the reasons for this wait event are the same index or multiple full table scans on the same table.

Eygle's explanation of DB file scattered read is:

DB file scattered read usually shows the wait related to full table scan. When the database performs a full table scan, the data is distributed into the buffer cache based on performance considerations. If this wait event is significant, it may indicate that no index is created or an appropriate index is not created for some tables scanned by the full table.

DB file sequential read usually displays read operations (such as index reading) related to a single data block ). If this wait event is significant, it may indicate that there is a problem with the table connection sequence in the Multi-Table connection, and the driver table may not be properly used; or it may indicate that the table is indexed without selection.

In most cases, we say that records can be obtained more quickly through indexes. Therefore, it is normal to wait for a database with good coding specifications and adjustments. However, in many cases, using indexes is not the best choice. For example, if you read a large amount of data from a large table, full table scanning may be significantly faster than index scanning, therefore, during development, we should note that such queries should be avoided using index scanning.

The system encounters a read by other session wait event.