一次ITL锁导致的系统问题

最新推荐文章于 2021-07-18 21:43:05 发布

hijk139

最新推荐文章于 2021-07-18 21:43:05 发布

阅读量2.5k

点赞数

分类专栏： oracle 问题处理文章标签： insert crm 服务器 conflict sql login

本文链接：https://blog.csdn.net/hijk139/article/details/7970306

版权

oracle 同时被 2 个专栏收录

63 篇文章 0 订阅

订阅专栏

问题处理

25 篇文章 0 订阅

订阅专栏

一次ITL锁导致的系统问题
2011年系统发生的一次问题，现在总结一下。oracle 9.2.0.8 +hpux 11.31
问题描述
第一次：
9:23 CRM系统突然死机，登陆后提示“访问的服务器正忙”，9:35CRM可以登录，但很不稳定，且知识短信无法发送。经测试10:45左右CRM系统恢复正常
影响：呼损增加1364，30S接通率86.68%
第二次：
14:15 CRM系统突然死机，登陆后提示“访问的服务器正忙” ，经测试14:20恢复正常
影响：呼损增加1227，接通率88.5%
第三次：
18:20 CRM系统突然死机，登陆后提示“访问的服务器正忙” ，经测试19:10左右恢复正常
影响：呼损增加2986，接通率77.70%

原因分析：
故障时段主机、数据库均为报错。通过故障时段的statspack报告，基本可以定位由于enqueue等待事件导致数据库响应慢

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                     % Total
Event                                               Waits    Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
enqueue                                           443,596   1,181,401    77.17
db file sequential read                        38,464,588     244,809    15.99
CPU time                                                       57,062     3.73
buffer busy waits                               3,861,116      18,131     1.18
latch free                                        841,297      16,169     1.06

通过下图也可以看出，故障时段基本和enqueue等待事件的时间相吻合

什么原因导致了突然出现enquue锁呢？需要找到具体锁的类型和相关sql。
由于是oracle 9i版本，没有ash来查询历史数据，只能依托statspack和其它一些手段。
通过statspack报告enqueue部分，没有看出异常

Enqueue activity for DB: SIEBDB  Instance: siebdb  Snaps: 28352 -28361
-> Enqueue stats gathered prior to 9i should not be compared with 9i data
-> ordered by Wait Time desc, Waits desc

                                                        Avg Wt         Wait
Eq     Requests    Succ Gets Failed Gets       Waits   Time (ms)     Time (s)
-- ------------ ------------ ----------- ----------- ------------- ------------
US       33,418       33,418           0         830         10.17            8
RO           12           12           0           1      3,414.00            3
SQ       12,172       12,172           0         210          2.56            1
HW       19,830       19,830           0         122          1.15            0
CI       11,214       11,214           0           2          3.50            0

通过监控软件和部署的脚本，发现了一些情况
1，被hang住的都是对同一个SIEBEL.S_EVT_ACT表的 insert操作（INSERT INTO SIEBEL.S_EVT_ACT….）

2，在一个时间点，该insert操作等待的row lock都被同一sid 持有，并且等待的对象都是改表或者表的索引（例如S_EVT_ACT_M51等索引）

LDAPUSER (session 2862) waiting for row lock held by LDAPUSER (4688)
 on SIEBEL.S_ACT_EMP_M4 file 212 block 0 row 0 from last 6.05 minutes
with sql statement =
INSERT INTO SIEBEL.S_EVT_ACT ( CONFLICT_ID, LAST_UPD, CREATED, L AST_UPD_BY, CREATED_BY, MODIFICATION_NUM, ROW_ID, PYMNT_FLG, PCT _COMPLETE, TARGET_PER_ADDR_ID, TODO_PLAN_START_DT, TODO_PLAN_END _DT, OWNER_POSTN_ID, PR_CONTAINER_ID, OWNER_OU_ID, PR_ORDER_ID, OWNER_LOGIN, OWNER_PER_ID, PR_PRDINT_ID, PR_SYMPTOM_CD, EVT_PRIO RITY_CD, PRIV_FLG, APPT_REPT_FLG, ROW_STATUS, SCHED_LOCKED_FLG, DO_NOT_ROUTE_FLG, TODO_ACTL_START_DT, EVT_STAT_CD, STATUS_RPT_FL G, CAL_DISP_FLG, TEMPLATE_FLG, TODO_CD, WC_TYPE_CD, TARGET_OU_ID , SRA_SR_ID, ACTIVITY_UID, ALARM_FLAG, AMT_CURCY_CD, AMT_EXCH_DT , ASSET_ID, ASGN_USR_EXCLD_FLG, ALLOW_BREAK_FLG, EST_COST_CURCY_ CD, EST_COST_EXCH_DT, SUBTYPE_CD, SRA_TYPE_CD, COMMENTS_LONG, CR EATOR_LOGIN, COST_CURCY_CD, NAME, PYMNT_DEV_AMT, CAL_TYPE_CD, TO DO_ACTL_END_DT, DONE_FLG, APPT_START_DT, DURATION_HRS, EST_RMNG_ WRK_TM, COST_EXCH_DT, EXP_RLTD_FLG, ACT_CREATED_BY, ACT_CREATED_ DT, MAX_PYMNT_AMT, LOC_DESC, ASSESS_2)
VALUES (:B1, :B2, :B3, :B 4, :B5, :B6, :B7, :B8, :B9, :B10, :B11, :B12, :B13, :B14, :B15, :B16, :B17, :B18, :B19, :B20, :B21, :B22, :B23, :B24, :B25, :B26 , :B27, :B28, :B29, :B30, :B31, :B32, :B33, :B34, :B35, :B36, :B 37, :B38, :B39, :B40, :B41, :B42, :B43, :B44, :B45, :B46, :B47, :B48, :B49, :B50, :B51, :B52, :B53, :B54, :B55, :B56, :B57, :B58 , :B59, :B60, :B61, :B62, :B63, :B64)

3，通过趋势分析，也可以看到，该语句的执行时间在故障时段明显变长，日常执行0.05秒的sql，故障时段执行时间为几十秒，可以确定改语句执行时间过长导致系统慢

     SNAP_TIME    TOATL_TIME_S    TOTAL_EXEC    AVG_TIME_S
492    2011/2/21 6:00:02    9.84    201    0.049
493    2011/2/21 7:00:04    36.31    708    0.051
494    2011/2/21 8:00:02    204.14    4974    0.041
495    2011/2/21 9:00:01    568.21    11379    0.05
496    2011/2/21 10:00:05    425457.91    5998    70.933
497    2011/2/21 11:00:05    378466.25    6416    58.988
498    2011/2/21 12:00:01    1012.35    19215    0.053
499    2011/2/21 13:00:02    925.61    21508    0.043
500    2011/2/21 14:00:06    1065.74    20180    0.053
501    2011/2/21 15:00:03    106894.8    15857    6.741
502    2011/2/21 16:00:01    1059.73    21133    0.05
503    2011/2/21 17:00:03    1285.53    23760    0.054
504    2011/2/21 18:00:04    1097.4    23492    0.047
505    2011/2/21 19:00:02    478983.09    8471    56.544
506    2011/2/21 20:00:04    454237.92    10323    44.003
507    2011/2/21 21:00:03    714.61    22230    0.032
508    2011/2/21 22:00:01    668.31    20590    0.032
509    2011/2/21 23:00:04    517.94    16200    0.032
510    2011/2/22 0:00:02    245.1    7870    0.031

原因定位：
由于被阻塞的sql为insert语句，插入操作不会导致锁表动作，因此排除常见了row lock，seq lock等，怀疑为ITL锁。
statspack的ITL部分，ITL锁数量明显异常，正常只有10个左右

 

Top 5 ITL Waits per Segment for DB: SIEBDB  Instance: siebdb  Snaps: 28352 -2836
-> End Segment ITL Waits Threshold:       100

                                           Subobject  Obj.           ITL
Owner      Tablespace Object Name          Name       Type         Waits  %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
SIEBEL     SIEB_IDX   S_EVT_ACT_M51                   INDEX        1,215   56.07
SIEBEL     SIEB_IDX   S_EVT_ACT_M52                   INDEX          455   21.00
SIEBEL     SIEB_IDX   S_EVT_ACT_F57                   INDEX          407   18.78
SIEBEL     SIEB_DATA  S_ACT_EMP                       TABLE           40    1.85
SIEBEL     SIEBEL_LAR CX_LOG1_M1                      INDEX           13     .60

锁等待阻塞的语句，主要为对表SIEBEL.S_EVT_ACT的insert语句，该表为记录客服业务活动的表（这也说明了为什么未影响营业厅crm业务）。同时，故障时段，ITL冲突明显增加，而且主要集中在S_EVT_ACT表的相关索引上，与被阻塞的INSERT语句相符。因此造成交易缓慢的直接原因是S_EVT_ACT表的相关索引上的ITL槽位冲突。当insert操作被ITL锁阻塞时，应用表现为系统hang住。这时，客服人员一般不会关闭应用，而是会重新打开web再次登录，由于登录的会话过多，导致OM服务器连接数逐渐增加，直到使用率100%。这个时候，再次登陆便会出现“访问的服务器正忙”的提示。
ITL锁的简单原因分析
1. 一个并发语句会使用一个ITL锁，超过了INITRANS（初始事务数）的保留设定（默认设定为2），并发交易高会出现ITL征用情况，理论上INITRANS可扩展到255（MAXTRANS），但实际应用中数据会被data占用，造成有些交易没有足够的ITL槽位可用。
备注：如图，Siebel DB db_block_size=8k

2. 交易提交变慢，持有的ITL槽位不能及时释放，同样造成后续交易没有足够的ITL槽位可用。

后续措施
原因定位后，解决就很容易了。通过修改主要业务表S_EVT_ACT表的相关索引的ITL槽位数（INITRANS）加大到50，系统再也没有出现ITL锁