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之后该等待事件被独立出来。
查看该等待事件的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#
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 ......(后面还有很多，处于保密，省略掉了)
上面不是说到还伴随着连续读吗，看一下两者的关系： 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');
