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');
