Investigating "read by other session" Wait Event

This article explains about the wait event “read by other session”. In this article I will explain how to further investigate about SQLs and segments involved in this wait event. This should be noted that unless this wait event is the result of slow IO performance, this wait event has to be tuned at the application side or by tuning the segment. To find out which SQLs and which table/index are causing this wait, we can query v$active_session_history (or DBA_HIST_ACTIVE_SESS_HISTORY); SAMPLE_TIME column can be used in WHERE clause to restrict rows for the event details during a specific period. Alternatively, we can also query v$session_wait to find out sessions currently waiting on this wait event.

SQL> select p1, p1text, p2, p2text, p3, p3text,sql_id from v$active_session_history where event='read by other session' order by sample_id;           P1 P1TEXT       P2          P2TEXT      P3      P3TEXT     SQL_ID ---------- ---------- ---------- --------------- ---------- ---------------------------------         10 file#              254794   block#         1         class#         gf9690ayfu58k         10 file#              254794   block#         1         class#         gf9690ayfu58k         10 file#              254794   block#         1         class#         9k14zbm7zssbx         10 file#              254794   block#         1         class#         9k14zbm7zssbx SQL> select p1,p2,p3 from v$session_wait where event='read by other session';           P1         P2               P3 ---------- ---------- -------------         10    1244164          1         10    1244164          1         10    1244164          1         10    1244164          1         10    1244164          1

As you can see above, we have found SQLs as well as blocks and datafile that are involved in wait. You can further execute following query to find out the segments with the hot blocks causing this wait event. I executed the query once for the block returned by v$active_session_history, and once for the block returned by v$session_wait. In both cases, I found out the same segment/table.

SQL> select owner,segment_name,segment_type from dba_extents where file_id=10 and 1244164  between block_id and block_id + Blocks -1;   OWNER       SEGMENT_NAME      SEGMENT_TYPE -------------------------------------------------------------------- DBUSER      MYTABLE                   TABLE     SQL> select owner,segment_name,segment_type from dba_extents where file_id=10 and 254794 between block_id and block_id + Blocks -1; OWNER       SEGMENT_NAME      SEGMENT_TYPE --------------------------------------------------------------------- DBUSER      MYTABLE                   TABLE

Tuning read by other session

Since we have found out the SQLs and segment involved in this wait event, we can fix this either by tuning the SQL such that it accesses fewer number of rows/blocks, thus reducing the chance of contributing to the hot blocks. In case this wait event is caused by hot index blocks, recreating the index as reverse key index to spread the load on hot blocks to several different blocks could also help eliminating this wait event. Creating index on a table (with hot blocks) to avoid full table scan should also be helpful.

You may also try creating SQL Profile for the SQL causing the issue