read by other session原理和實(shí)戰(zhàn)解決思路

發(fā)布時(shí)間：2020-08-10 14:04:43 來源：ITPUB博客閱讀：114 作者：水逸冰欄目：關(guān)系型數(shù)據(jù)庫

3月17日七點(diǎn)左右，客戶的一套11gRAC突然出現(xiàn)活動(dòng)會(huì)話增加，等待事件過高，cpu使用率幾乎接近0的情況。下面盡可能還原解決這個(gè)問題的過程：
查詢等待事件：
select inst_id, event#, event,count(*) from gv$session
where wait_class# <> 6
group by inst_id, event#,event
order by 1,4 desc;

發(fā)現(xiàn)出現(xiàn)了大量的read by other session等待事件。同時(shí)伴隨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.
當(dāng)b會(huì)話要訪問buffer cache并且pin住相應(yīng)的block的時(shí)候，它發(fā)現(xiàn)a會(huì)話也需要這些數(shù)據(jù)塊，并且正在將相關(guān)的數(shù)據(jù)塊從硬盤讀取到buffer cache中，這個(gè)時(shí)候b會(huì)話必須等待a會(huì)話讀取完成，b會(huì)話此時(shí)出現(xiàn)read by other session等待事件。在10.1之前的版本中，該等待事件被歸為buffer busy waits等待事件。10.1之后該等待事件被獨(dú)立出來。

從上面的定義中不難發(fā)現(xiàn)，這個(gè)等待事件出現(xiàn)的原因其實(shí)就是：
1）熱塊競(jìng)爭(zhēng)：大量會(huì)話幾乎同時(shí)訪問相同的數(shù)據(jù)塊造成爭(zhēng)用。
2）磁盤IO性能有問題：磁盤IO還是要去看一下的，雖然這個(gè)原因的可能性相比較上面的來說要小一點(diǎn)。萬一大象沖進(jìn)了數(shù)據(jù)中心造成了硬件故障呢。當(dāng)然了，并行如果被使用了，還要看一下是否是開了太多了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';

找到三個(gè)值以后，可以確定熱點(diǎn)對(duì)象到底是什么：
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;

查看熱點(diǎn)塊導(dǎo)致的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的會(huì)話和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')；

這個(gè)時(shí)候我發(fā)現(xiàn)里面的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')

上面不是說到還伴隨著連續(xù)讀嗎，看一下兩者的關(guān)系：
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');

相對(duì)來說比較吻合，應(yīng)該是上面執(zhí)行計(jì)劃中用到的索引導(dǎo)致的db file sequential read

五張表的嵌套循環(huán)的花銷還是比較大的，每一條sql執(zhí)行時(shí)間都比較久，由于sql太過于復(fù)雜，當(dāng)前優(yōu)化sql對(duì)我來說不現(xiàn)實(shí)，跟業(yè)務(wù)溝通以后先殺掉，后面再考慮能否優(yōu)化。

這次情況的處理比較簡單，主要通過該情況了解一下read by other session的出現(xiàn)原理。熱塊爭(zhēng)用問題解決本質(zhì)還是抓到sql進(jìn)行優(yōu)化。

向AI問一下細(xì)節(jié)

read by other session原理和實(shí)戰(zhàn)解決思路

猜你喜歡

最新資訊

相關(guān)推薦

相關(guān)標(biāo)簽