read by other session原理和实战解决思路

3月17日七点左右，客户的一套11gRAC突然出现活动会话增加，等待事件过高，cpu使用率几乎接近0的情况。下面尽可能还原解决这个问题的过程：
查询等待事件：
    select inst_id, event#, event,count(*) from gv$session
     where wait_class# <> 6
    group by inst_id, event#,event
    order by 1,4 desc;


发现出现了大量的read by other session等待事件。同时伴随db file sequential read.


##########################################简介##############################
什么是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" wait-event. However, in oracle 10.1 and higher, the wait time is now broken out into the "read by other session" wait event.
当b会话要访问buffer cache并且pin住相应的block的时候，它发现a会话也需要这些数据块，并且正在将相关的数据块从硬盘读取到buffer cache中，这个时候b会话必须等待a会话读取完成，b会话此时出现read by other session等待事件。在10.1之前的版本中，该等待事件被归为buffer busy waits等待事件。10.1之后该等待事件被独立出来。


从上面的定义中不难发现，这个等待事件出现的原因其实就是：
1）热块竞争：大量会话几乎同时访问相同的数据块造成争用。
2）磁盘IO性能有问题：磁盘IO还是要去看一下的，虽然这个原因的可能性相比较上面的来说要小一点。万一大象冲进了数据中心造成了硬件故障呢。当然了，并行如果被使用了，还要看一下是否是开了太多了dbwr slave process：sho parameter dbwr_io_slaves


查看该等待事件的p1 p2 p3值的含义：
sql>  SELECT NAME, PARAMETER1 P1, PARAMETER2 P2, PARAMETER3 P3
  2        FROM V$EVENT_NAME
  3       WHERE NAME = '&event_name';
Enter value for event_name: read by other session
old   3:      WHERE NAME = '&event_name'
new   3:      WHERE NAME = 'read by other session'


NAME                           P1                   P2                   P3
------------------------------ -------------------- -------------------- --------------------
read by other session          file#                block#               class#


Parameters:
P1 = file# Absolute File# (AFN)
P2 = block#
P3 = class# Block class


class# Block class#
This is the class of block being waited on. In particular:
class 1 indicates a "data block", which could be table or index
class 4 indicates a "segment header"
class >=15 indicate undo blocks


查询等待事件的p1 p2 p3:
SELECT p1 "file#", p2 "block#", p3 "class#"
FROM v$session_wait WHERE event = '&event_name';


找到三个值以后，可以确定热点对象到底是什么：
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;


查看热点块导致的sql：
select sql_id,sql_text
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 a.hash_value, a.address, a.piece;
###########################################################


确定read by other session的会话和sql信息：
select sid,
       s.username,
       s.program,
       s.action,
       loGon_time,
       q.sql_text,
       q.SQL_FULLTEXT,
       q.sql_id
  from v$session s
  left join v$sql q on s.sql_hash_value = q.hash_value
 where s.sid in (select sid
                   from v$session_wait
                  where event='read by other session')；


这个时候我发现里面的sql基本都是一些类似的sql语句：
select语句的sql id变化比较频繁，但是sql比较类似。
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  44v32y42t480h, child number 0
-------------------------------------
select * from (select B.*,rownum as row_index from (  select PRO
D_ID,SO_DATE,INSTALL_ADDR,SO_STAFF_NAME,CHANNEL_NAME,SO_ORDER_ID
,SO_CO_SERIAL,ORDER_ID,SPELINENUMBER,TML_CODE,ACCESS_NUMB,EXT_NU
MB,CUST_NAME,OWNER_ORG_ID,GROUP_CODE,ACT_ID,DEAL_PRIORITY,STATE,
PROD_OFFER_NAME,ONU_PORT_NAME,COMMUnity_NAME,PROD_BANDWIDTH,OPER
_TYPE,ALARM_DELAY_FLAG,PRE_INSTALL_START_TIME,PRE_INSTALL_END_TI
ME,CHANNEL_CODE,SLA_CODE,IS_KZY,REGION_ID from (SELECT a.order_i
d, a.so_order_id, a.so_co_serial, a.order_grp_id, a.channel_type
, a.channel_code, a.channel_name, a.pre_install_start_time, a.pr
e_install_end_time, a.limit_time, a.fee_flag, a.so_staff_id, a.s
o_staff_code, a.so_staff_name, a.so_date, a.so_org_id, a.owner_o
rg_id, a.owner_district, a.order_kind, a.priority, a.prod_ins_id
, a.rel_prod_ins_id, a.prod_id, a.act_id, a.pay_way, a.pay_name,
a.access_numb, a.ext_numb, a.bind_access_numb, a.tml_code, a.sl
a_id, a.is_main_prod, a.install_addr, a.standard_addr_id, a.stan
dard_addr_name, a.create_date, a.execute_date, a.delay_date, a.a
larm_date, a.alarm_delay_flag, a.deal_priority, a.sla_code, a.wf
_template_code, a.src_system, a.template_id, a.state, a.remarks,
a.region_id, (SELECT oa.attr_val FROM SF_O_ATTR_A oa WHERE a.or
der_id = oa.order_id AND oa.attr_code = 'community_name' AND ROW
NUM = 1 ) AS community_name, a.pay_name AS SPELINENUMBER, NVL((S
ELECT ra.attr_val FROM SF_O_RESSRV_REL_A orr, SF_RESSRV_RES_REL_
A rrr, SF_RES_ATTR_A ra WHERE ra.attr_code = 'port_name' AND ra.
res_ins_id = rrr.res_ins_id AND rrr.ressrv_ins_id = orr.ressrv_i
ns_id AND orr.order_id = a.order_id AND orr.state = 'N' AND ROWN
UM = 1), (SELECT ra.attr_val FROM SF_O_RESSRV_REL_A orr, SF_RESS
RV_RES_REL_A rrr, SF_RES_ATTR_A ra WHERE ra.attr_code = 'port_na
me' AND ra.res_ins_id = rrr.res_ins_id AND rrr.ressrv_ins_id = o
rr.ressrv_ins_id AND orr.order_id = a.order_id AND orr.state = '
O' AND ROWNUM = 1)) AS onu_port_name, (SELECT oa.attr_val FROM S
F_O_ATTR_A oa WHERE a.order_id = oa.order_id AND oa.attr_code =
'prod_offer_name' AND ROWNUM = 1 ) AS prod_offer_name, (SELECT o
a.attr_val FROM SF_O_ATTR_A oa WHERE a.order_id = oa.order_id AN
D oa.attr_code = 'prod_bandwidth' AND ROWNUM = 1 ) AS prod_bandw
idth, op.oper_id, decode ......(后面还有很多，处于保密，省略掉了)




Plan hash value: 3873006668


------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                    |       |       |    18 (100)|          |
|*  1 |  VIEW                                 |                    |     1 |  2963 |    18   (6)| 00:00:01 |
|   2 |   COUNT                               |                    |       |       |            |          |
|   3 |    VIEW                               |                    |     1 |  2950 |    18   (6)| 00:00:01 |
|   4 |     SORT ORDER BY                     |                    |     1 |   344 |    18   (6)| 00:00:01 |
|*  5 |      FILTER                           |                    |       |       |            |          |
|   6 |       NESTED LOOPS                    |                    |       |       |            |          |
|   7 |        NESTED LOOPS                   |                    |     1 |   344 |    17   (0)| 00:00:01 |
|   8 |         NESTED LOOPS                  |                    |     1 |   314 |    11   (0)| 00:00:01 |
|   9 |          NESTED LOOPS                 |                    |     1 |   244 |     8   (0)| 00:00:01 |
|  10 |           NESTED LOOPS                |                    |     1 |   227 |     6   (0)| 00:00:01 |
|* 11 |            TABLE ACCESS BY INDEX ROWID| SF_TASK_A          |     1 |    80 |     4   (0)| 00:00:01 |
|* 12 |             INDEX RANGE SCAN          | I_SF_TASK_2_A      |     2 |       |     3   (0)| 00:00:01 |
|* 13 |            TABLE ACCESS BY INDEX ROWID| SF_ORDER_A         |     1 |   147 |     2   (0)| 00:00:01 |
|* 14 |             INDEX UNIQUE SCAN         | PK_SF_ORDER_A      |     1 |       |     1   (0)| 00:00:01 |
|  15 |           TABLE ACCESS BY INDEX ROWID | SF_O_CUST_A        |     1 |    17 |     2   (0)| 00:00:01 |
|* 16 |            INDEX UNIQUE SCAN          | PK_SF_O_CUST_A     |     1 |       |     1   (0)| 00:00:01 |
|* 17 |          TABLE ACCESS BY INDEX ROWID  | SF_TASK_AUTH_A     |     1 |    70 |     3   (0)| 00:00:01 |
|* 18 |           INDEX RANGE SCAN            | I_SF_TASK_AUTH_1_A |     1 |       |     2   (0)| 00:00:01 |
|* 19 |         INDEX RANGE SCAN              | I_SF_TASK_OPER_1_A |     3 |       |     3   (0)| 00:00:01 |
|* 20 |        TABLE ACCESS BY INDEX ROWID    | SF_TASK_OPER_A     |     2 |    60 |     6   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------


Predicate InfORMation (identified by operation id):
---------------------------------------------------


   1 - filter(("ROW_INDEX"<=5000 AND "ROW_INDEX">=1))
   5 - filter(TO_DATE(:4,'YYYY-MM-dd HH24:MI:SS')<=TO_DATE(:5,'YYYY-MM-dd HH24:MI:SS'))
  11 - filter(("T"."TASK_TEMPLATE_ID"=TO_NUMBER(:1) AND INTERNAL_FUNCTION("T"."OWNER_ORG_ID")))
  12 - access("T"."CREATE_DATE">=TO_DATE(:4,'YYYY-MM-dd HH24:MI:SS') AND
              "T"."CREATE_DATE"<=TO_DATE(:5,'YYYY-MM-dd HH24:MI:SS'))
  13 - filter(("O"."PROD_ID"=TO_NUMBER(:2) AND "O"."ACT_ID"=TO_NUMBER(:3)))
  14 - access("T"."ORDER_ID"="O"."ORDER_ID")
  16 - access("O"."ORDER_ID"="C"."ORDER_ID")
  17 - filter("STA"."IS_CUR"='Y')
  18 - access("T"."TASK_ID"="STA"."TASK_ID")
  19 - access("T"."TASK_ID"="STO"."TASK_ID")
  20 - filter("STO"."IS_CUR"='Y')


上面不是说到还伴随着连续读吗，看一下两者的关系：
select distinct sql_id  
from v$session  
where event in ('read by other session', 'db file sequential read'); 
select distinct sql_id  
from v$session  
where event in ('read by other session');   


相对来说比较吻合，应该是上面执行计划中用到的索引导致的db file sequential read


五张表的嵌套循环的花销还是比较大的，每一条sql执行时间都比较久，由于sql太过于复杂，当前优化sql对我来说不现实，跟业务沟通以后先杀掉，后面再考虑能否优化。


这次情况的处理比较简单，主要通过该情况了解一下read by other session的出现原理。热块争用问题解决本质还是抓到sql进行优化。