read by other session 等待事件分析

一、問題 

實際生產程式，中午業務高峰的時候，購物車下單，訂單查詢，新增購物車超時，有些客戶能成功，有些不能成功。其實業務請求不多。後臺打出awr報告出來定位到了問題，是read by other session等待事件。

找到對應的sql語句

二、分析

read by other session原因是一個session會話正在從磁碟把相同的資料塊讀到記憶體SGA時候，另外一些會話也同時請求相同資料塊，資料塊爭用，這個瞬間的等待就是 read by other session。

解決辦法：1.如果你把資料都快取到了SGA，不需要從磁碟讀這個資料，沒有這個等待事件。

                 2.假如你一定要從磁碟讀，那你加快這個sql的執行時間。看上面sql平均執行時間是0.44秒。而且每個sql都是針對表A（這裡隱藏了真正表的名字，是實際生產庫的）的相同行，查詢語句都是針對表A的。

                 現在我來做一個數學題目:每條語句執行0.44秒，假設購物車下單一次需要執行如上sql語句2次，目前我有40個會話。那我1秒鐘可以執行2.2次sql。針對同一行，不同會話，1秒鐘1個會話只能有2.2次查詢，超過這個數，如果每次都要從磁碟讀資料，那就會發生 read by other session等待。發生如上情況有同時滿足如下條件：

 (1)查詢 針對同一表的同一行，才會有同一個資料塊爭用。

 (2)要從磁碟讀資料,不能直接從快取拿，如果從快取拿，不會發生 read by other session等待事件

 (3)要爭用，併發請求會話要多

如果這個語句執行時間是0.01秒，那麼1秒鐘執行100次這個語句。分析如下關鍵語句：

select ID, PAY_TYPE, ORDER_TIME, TOTAL_PAYABLE_AMOUNT, ACTUAL_PAY_AMOUNT, CURRENCY, AGGREGATE_ORDER_STATE, COMMENT_ORDER_STATE, REFUND_ORDER_STATE, CREATE_TIME, UPDATE_TIME, DEL_STATE, VERSION, USER_ID, 
STORE_ID, BUSINESS_ORDER_ID, A_NO, REMARK, SHOW_URL, BUSINESS_LINE, MERCHANT_NO, BUSINESS_ORDER_STATE, STORE_NAME, BASIC_AMOUNT, CHANGE_NO_TIMES, PAY_ORDER_NO, BUSINESS_CONTENT, DEVICE_ID,
 NOT_CHECK_AMOUNT from A where A_NO = :1

非常簡單的一個單表查詢，查詢有30個列。加索引最佳化這個語句就可以。

SELECT count(0) FROM A a LEFT JOIN p p ON (a.pay_order_no = p.pay_order_no) WHERE a.DEL_STATE = 10 AND a.USER_ID = :userid AND a.BUSINESS_LINE = :buline;

pnh_primary_。。。@LIFEKH>SELECT count(0) FROM A a LEFT JOIN p p ON (a.pay_order_no = p.pay_order_no) WHERE a.DEL_STATE = 10 AND a.USER_ID = :userid AND a.BUSINESS_LINE = :buline; 
  COUNT(0)
----------
	 0
Execution Plan
----------------------------------------------------------
Plan hash value: 3179731160
--------------------------------------------------------------------------------------
| Id  | Operation	   | Name	     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |		     |	   1 |	  31 | 16333   (1)| 00:03:16 |
|   1 |  SORT AGGREGATE    |		     |	   1 |	  31 |		  |	     |
|*  2 |   TABLE ACCESS FULL| A  |	   2 |	  62 | 16333   (1)| 00:03:16 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("A"."USER_ID"=:USERID AND "A"."BUSINESS_LINE"=:BULINE AND
	      "A"."DEL_STATE"=10)
Statistics
----------------------------------------------------------
	  1  recursive calls
	  0  db block gets
      60091  consistent gets
	  2  physical reads
	  0  redo size
	525  bytes sent via SQL*Net to client
	520  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

你看執行計劃中有2 physical reads, 去從磁碟讀，加上索引不需要去磁碟讀，就不會有等待事件

三、另外一個案例，也是非常典型：

負載是3344/29=100，資料庫負載很高，執行sql語句效率非常低了，全部都在等待 read by other session，還同時出現 direct path read ，遇到非常典型的例子了

Solutions

Excessive waits for this event are typically due to several processes repeatedly reading the same blocks, e.g. many sessions scanning the same index or performing full table scans on the same table.

 

1.  Tune the SQL statement so that it reads fewer blocks. If the top objects listed in SolarWinds DPA are indexes, determine if there is a more efficient index that can be used more efficiently. If the top objects are tables, a full table scan or index randge scan is being performed. Look for efficient indexing opporunties.

 

1.  Increase the buffer cache so that more blocks are already in memory rather having to be read from disk. The query will still need to read the same number of blocks so tuning is the first recommendation, but if you cannot tune the statement, a query reading blocks from memory is much faster than from disk.

 

1.  Increase the PCTUSED / PCTFREE for the table storage parameters via ALTER TABLE or rebuild. This will result in less rows per block and possibly reduce contention.